Skip to content
Related Articles

Related Articles

PostgreSQL – DATE_PART Function

View Discussion
Improve Article
Save Article
Like Article
  • Last Updated : 01 Feb, 2021

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');


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;


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;


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!