PostgreSQL – DATE_PART Function

In PostgreSQL, the DATE_PART() function is used to query for subfields from a date or time value. 

Syntax: DATE_PART(field, source)

In the above syntax the field is an identifier that is used to set the field to extract the data from the source. The permitted field values mentioned below:

• century
• decade
• year
• month
• day
• hour
• minute
• second
• microseconds
• milliseconds
• dow
• doy
• epoch
• isodow
• isoyear
• timezone
• timezone_hour
• timezone_minute

The DATE_PART() function returns a double precision type value.

Example 1:

The below query extracts the data regarding the century from a timestamp:

SELECT date_part('century', TIMESTAMP '2020-01-01');

Output:

Example 2:

In this example we will extract the hour, minute, second from a time stamp type value, by passing the corresponding value hour, minute and second to the DATE_PART() function:

SELECT date_part('hour', TIMESTAMP '2020-03-18 10:20:30') h,
       date_part('minute', TIMESTAMP '2020-03-18 10:20:30') m,
       date_part('second', TIMESTAMP '2020-03-18 10:20:30') s;

Output:

Example 3:

Here we will query for the day of week and or day of the year from a timestamp, through the use of the following statement:

SELECT date_part('dow', TIMESTAMP '2020-03-18 10:20:30') dow,
       date_part('doy', TIMESTAMP '2020-03-18 10:20:30') doy;

Output: