Back to cookbooks list Articles Cookbook

How to Get the Day of the Year from a Date in PostgreSQL

  • EXTRACT()
  • DATE_PART()
  • doy

Problem:

You want to get the day of the year from a date or a timestamp column in PostgreSQL.

Example:

Our database has a table named software_sale with data in the columns id, software, and sale_date.

idsoftwaresale_date
1Super Game X2019-09-15
2Browser X2019-10-15
3DB News2019-11-26
4MyPaintSoft2018-10-15
5New OS2019-10-15

Let’s count the number of software items sold by days of the year. We’ll use this to find out which days of the year (independent of the actual year) sales were high or low.

Download SQL for Data Analysis Cheat Sheet

Solution 1: Using the DATE_PART function

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

SELECT 
  DATE_PART('doy',sale_date) AS day_of_year,
  COUNT(id) as count
FROM software_sale
GROUP BY day_of_year;

Here’s the result of the query:

day_of_yearcount
2581
2883
3301

Discussion:

Use the PostgreSQL DATE_PART() function to retrieve the number of the day of a year from a date/time/datetime/timestamp column. This function takes two arguments. The first argument is the date part (or time part) you want the function to return. In this example, we’re using 'doy', which returns the day of the year. You can use other parts, like 'day', 'year', 'month', 'hour', 'minute', 'week', etc. You can learn more in the PostgreSQL documentation.

Discover the best interactive PostgreSQL courses

The second argument is the date/time/datetime/timestamp you want to extract the date/time part from. This can be an expression returning a date/time/datetime/timestamp value or the name of a date/time/datetime/timestamp column. (In our example, it is a column of the date data type.)

The function DATE_PART() with the 'doy' identifier returns the number of the day of the year (from 1 to 365/366) as an integer. In our example, the day of the year of the sale date (from the sale_date column) is now displayed in a new column, day_of_year. The 15th October was the 288th day in 2019 and in 2018; on that day over two years, a total of three sales were made (2 in 2019, 1 in 2018).

We use the COUNT() aggregate function to count the number of sales. This function takes one argument; in this case, it’s the software ID number. We also need the GROUP BY clause in this query to group records according to the day of the year. (In our example, the actual argument is day_of_year, the alias for DATE_PART('doy',sale_date).)

Solution 2: Using the EXTRACT() function

This is not the only way to get the day of the year. We can also use the EXTRACT() function.

SELECT EXTRACT('doy' FROM sale_date) 
         AS  day_of_year,
       COUNT(id) as count
FROM software_sale
GROUP BY day_of_year;

This function is similar to DATE_PART(). The difference is that we use FROM instead of a comma between the date part identifier and the date and time argument. The result is the same. Note: EXTRACT() functions is the standard SQL function, it's not specific to PostgreSQL.

Recommended courses:

Recommended articles:

See also: