Articles Cookbook
Back to list
PostgreSQL

How to Extract the Week Number from a Date in PostgreSQL

Database:

Operators:

EXTRACT(), DATE_PART(), WEEK

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.

idfirst_namelast_namebirth_date
1AngelaMichelin2018-01-01
2MartinJackson2002-07-26
3JustinClark2010-12-26
4FrankBarker2008-06-08

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

Solution 1:

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:

first_namelast_namebirth_week_number
AngelaMichelin1
MartinJackson30
JustinClark51
FrankBarker23

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.

DATE_PART() is not only one way to get the week number. Another option is to use the EXTRACT() function.

Solution 2:

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.

Recommended courses:

Recommended articles:

See also:

go to top