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:

DATE_PART('week', date|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.

Conclusion

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.