Back to list PostgreSQL How to Get the Day of the Year from a Date in PostgreSQL Database: PostgreSQL Operators: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. Solution 1: 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. 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).) This is not the only way to get the day of the year. We can also use the EXTRACT() function. Solution 2: Here’s the query you would write with 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() is SQL standard. Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Group by Month in PostgreSQL How to Find the Interval Between Two Dates in PostgreSQL How to Get the Day of the Year from a Date in PostgreSQL How to Extract the Week Number from a Date in PostgreSQL Tags: PostgreSQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.