While working with a Postgres database you may come across a situation where you need to get or extract a specific field from a date or timestamp. This may be because of sorting tables based on a specific date field, calculating experience/age in terms of specific fields, or any other reason. Whatever the reason is, when it comes to extracting a date field, the best suitable options are the EXTRACT() function and the DATE_PART() function.
This Postgres blog will guide you on getting the week number from a given date or timestamp.
How Do I Get the Week Number From a Specific Date or Timestamp in Postgres?
Use the EXTRACT() or DATE_PART() function along with the “week” argument to get a week number from a particular date or timestamp:
Alternatively, the EXTRACT() function will be used as follows:
EXTRACT('week' FROM date|timestamp);
Let’s learn it using the following examples.
Example 1: Getting Week From Date
In the following example, we will execute both the DATE_PART() and EXTRACT() functions to fetch the week from the given date:
SELECT DATE_PART('week', DATE '2018-06-08'), EXTRACT('week' FROM DATE '2018-06-08');
The output shows that it's the 23rd week of the year.
Example 2: Getting Week From Table’s Data
We will use the EXTRACT() and DATE_PART() functions on the “emp_data” table, whose content is listed in the following snippet:
SELECT * FROM emp_data;
Let’s execute the DATE_PART() and EXTRACT() functions on the “joining_date” column to find the joining week number of each employee:
SELECT emp_id, emp_name, joining_date, EXTRACT('WEEK' FROM joining_date) AS joining_week_number, DATE_PART('WEEK', joining_date) AS emp_joining_week_number FROM emp_data;
The result set demonstrates the joining week number of each employee.
That’s all about getting the week number from a particular date in PostgreSQL.
Use the EXTRACT() or DATE_PART() function along with the “week” argument to get a week number from a particular date or timestamp. The stated functions can extract the week number of the year from the date value or timestamp. This post has discussed a couple of methods to fetch the week number from the given date or timestamp.