In PostgreSQL you can use the extract() function to get the week number from a date.

You can also use the date_part() function to do the same thing.

Example 1: The extract() Function

Here’s an example of using the extract() function to extract the week from a date.

SELECT extract(week from date '2020-12-27') AS "Week Number";

Result:

 Week Number 
-------------
          52

Example 2: The date_part() Function

Here’s how to do the same thing using the date_part() function.

SELECT date_part('week', date '2020-12-27') AS "Week Number";

Result:

 Week Number 
-------------
          52

Example 3: About the ISO Week-Numbering System

If you get results you weren’t expecting, it could be because of the way ISO week-numbering is defined.

ISO weeks start on Mondays and the first week of a year contains January 4 of that year. Therefore, it’s possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year.

Example:

SELECT 
  extract(week from date '2021-01-03') AS "2021-01-03",
  extract(week from date '2021-01-04') AS "2021-01-04";

Result:

 2021-01-03 | 2021-01-04 
------------+------------
         53 |          1

In this case, the 3rd of January 2021 is actually still part of the last week of 2020. The first week of 2021 doesn’t start until the 4th of January.

Below is another example that demonstrates a date at the end of the year being part of the first week of the following year.

SELECT 
  extract(week from date '2024-12-29') AS "2024-12-29",
  extract(week from date '2024-12-30') AS "2024-12-30";

Result:

 2024-12-29 | 2024-12-30 
------------+------------
         52 |          1

In this case, 29th of December 2024 is part of the last week of 2024, but as soon as we get to the 30th December, we’re in the first week of 2025.