Extract Day From Timestamp in PostgreSQL

PostgreSQL provides a timestamp datatype to store date and time information in a database. For some requirements, we need to extract only the day portion from a timestamp in PostgreSQL.

There are two functions available in PostgreSQL to extract days from a timestamp.

1. EXTRACT
2. DATE_PART

Both functions provide the same output.

1. EXTRACT function

Below is the syntax for the extract function.

EXTRACT(<field> from <timestamp>) -> numeric

Replace the <field> with the required field, for example: day, month, year, hour, and replace the <timestamp> with the timestamp column.

The Extract function allows you to extract various pieces of information from a timestamp. To get the day from a timestamp, use day as a parameter in the Extract function.

SELECT EXTRACT(day from '1990-05-15 00:00:00'::timestamp)

To get a day from a timestamp column in table, replace the custom date with the target column name, as shown below:

2. DATE_PART function

Below is the syntax for the DATE_PART function.

DATE_PART('<field>', <timestamp>)

Replace <field> with epoch, decade, day, month, year, hour, minute, and seconds, and replace <timestamp> with the target column.

SELECT DATE_PART('day','1982-03-12 00:00:00'::timestamp)