How to Extract the Week Number from a Date in PostgreSQL

Problem:

You want to get the week number of a date or timestamp value in a PostgreSQL database.

Example:

Our database has a table named children with data in the columns id, first_name, last_name, and birth_date.

For each child, let’s get their first name, last name, and the week number of their birth date.

Solution 1: Use DATE_PART function

We’ll use the DATE_PART() function. Here’s the query you would write:

SELECT 
  first_name,
  last_name,
  DATE_PART('week', birth_date) AS birth_week_number
FROM children;

Here’s the result of the query:

Discussion:

Use the DATE_PART() function to retrieve the week number from a date in a PostgreSQL database. This function takes two arguments. The first argument is the date part to retrieve; we use 'week', which returns the week number (e.g. 1 for the first week in January, the first week of the year). You can use another date part, like 'day', 'year', 'month', 'hour', 'minute', 'doy' (the day’s number in the year), etc. You can learn more about date parts in the PostgreSQL documentation.

The second argument is a column name of the date/timestamp/timestamptz data type or an expression returning one of these types. (In our example, it is a column of the date data type.)

When used with the 'week' date part, the function DATE_PART() returns the week number as an integer. In our example, the week number for the given child’s birthdate is displayed; we named this column birth_week_number. Martin Jackson was born on 2002-07-26, so the function returns 30 as the week number of his birthdate.

Solution 2: Use EXTRACT function

We’ll use the EXTRACT() function. Here’s the query you would write:

SELECT 
  first_name,
   last_name,
   EXTRACT('week' FROM birth_date) AS birth_week_number
FROM children;

This function is similar to DATE_PART(). The difference is in the syntax: instead of a comma between the date part name and the column/expression, there is the keyword FROM. Also, the EXTRACT() function is SQL standard. The result is the same for both functions.