PostgreSQL – DATE_PART Function
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');
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:
Share your thoughts in the comments
Please Login to comment...