Extract day of year from date in PostgreSQL

To extract the day of the year from a date in PostgreSQL, you can use the DATE_PART() function with the ‘doy’ (day of the year) unit.

Syntax for DATE_PART() Function in PostgreSQL:

unit: Specifies the part of the date or timestamp to extract (e.g., ‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.).

source: The date or timestamp from which to extract the specified part.

In Our case. The values of the unit must be “doy” (day of the year)

Extract Day of Year from Date in postgresql Simple Example:

In order to Extract day of year from date in postgresql we will be using  DATE_PART() function.

In the below Example we will be passing doy as “unit” argument and date as “source” argument in order to extract day of year from Date or datetime

 

SELECT DATE_PART('doy', '2024-02-24'::timestamp) AS day_of_year_part;

Output:

Extract Day of Year from Current date in PostgreSQL :

This query uses the CURRENT_DATE function to get the current date and then extracts the day of the year using DATE_PART()

 

SELECT CURRENT_DATE AS current_date, DATE_PART('doy', CURRENT_DATE) AS day_of_year;

Output:

Extract Day of Year from Datetime in PostgreSQL table:

We will be using  Below Student_detail table for our example to depict on how to extract day of year from datetime in PostgreSQL

Student_detail:

In the above table we will be using  DATE_PART() Function, which will take “doy” (day of year)  and column named “birthdaytime” as argument. Which will extract day of the year from “birthdaytime” column and store in the new column of postgresql table as shown below

 

select *, DATE_PART('doy', birthdaytime::timestamp) AS day_of_year from Student_detail

so the resultant table will have day_of_year column