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

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

Syntax

The syntax goes like this:

EXTRACT(field FROM source)

Where:

• field is is an identifier or string that selects what field to extract from the source value.
• source is a timestamp or an interval.

Example – Timestamp

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

SELECT extract(hour FROM timestamp '2022-10-30 10:11:35');

Result:

10

This example retrieves the hour field from a timestamp value.

As mentioned, the first argument can be an identifier or string. This is one of the differences between extract() and date_part(). When using date_part(), you must supply a string for this argument.

Here’s the same example again, except as a string.

SELECT extract('hour' FROM timestamp '2022-10-30 10:11:35');

Result:

10

Here’s an example with the same timestamp, but this time I retrieve the year field.

SELECT extract(year FROM timestamp '2022-10-30 10:11:35');

Result:

2022

Example – Interval

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

SELECT extract(hour FROM interval '7 hours 45 minutes');

Result:

7

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

SELECT extract(hour FROM interval '120 minutes');

Result:

2

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

For example:

SELECT extract(hour FROM interval '100 minutes');

Result:

1

And another:

SELECT extract(minute FROM interval '2 hours');

Result:

0

Field Names

The first argument can be any of the following:

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

These are the same as the valid values for date_part() function.