How date_part() Works in PostgreSQL

In PostgreSQL, the date_part() function retrieves subfields such as the year, month, hour, or minute, part from a date/time value.

It’s equivalent to the extract() function, although with a slightly different syntax.

Syntax

The function has the following syntax:

date_part('field', source)

Where:

'field' is for the date part that you want to retrieve. This parameter must be a string value, not a name. See below for a list of valid field names.
source is a timestamp or an interval that you want the date part retrieved from.

Here’s a basic example to demonstrate how to retrieve a field from a timestamp.

SELECT date_part('hour', timestamp '2020-09-16 22:33:15');

Result:

This example retrieves the hour field from a timestamp value.

Here it is again, but this time I retrieve the year field.

SELECT date_part('year', timestamp '2020-09-16 22:33:15');

Result:

In this example, I retrieve a date part from an interval value.

SELECT date_part('hour', interval '5 hours 30 minutes');

Result:

In the next example, the function correctly returns the number of hours, even though I only provide the number of minutes.

SELECT date_part('hour', interval '120 minutes');

Result:

However, don’t count on this technique. You might find that you don’t always get the result you expect.

For example:

SELECT date_part('hour', interval '100 minutes');

Result:

And another:

SELECT date_part('minute', interval '2 hours');

Result:

The first argument can be any of the following: