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

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.

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.

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:

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.

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 15^th 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.