Get Day from date or Timestamp in PostgreSQL

In order to Get day from date or  Timestamp in PostgreSQL we use Extract() function. Within the Extract keyword we have to mention DAY as we are getting day from date. To extract the day (day part) from a date in PostgreSQL, you can also use the DATE_PART() function with the ‘day’ unit.

Let’s see how to

• Extract day from Timestamp column in PostgreSQL using Extract() function
• Get Day from date column in PostgreSQL using DATE_PART() function

With an example for both

Syntax for DATE_PART() Function in Postresql:

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 “day”.

Extract Day from Date in postgresql Simple Example:

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

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

SELECT DATE_PART('day', '2024-02-24'::timestamp) AS day_part;

Output:

Extract Day from Current date or timestamp in PostgreSQL :

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

SELECT CURRENT_DATE AS current_date, DATE_PART('day', CURRENT_DATE) AS day_part;

Output:

Extract Day part from Date column in PostgreSQL table:

We will be using  Below Student_detail table for our example to depict on how to extract day part from date column in postgresql

Student_detail:

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

select *, DATE_PART('day', birthdaytime::timestamp) AS day_part from Student_detail

so the resultant table will have day_part column

Extract Day part from Date column in PostgreSQL table using Extract():

We will be using Below Student_detail table for our example to depict on how to extract day part from date column in postgresql

Student_detail:

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

Select *, extract(DAY FROM birthdaytime) as day_part from student_detail

So the resultant table will have day_part column