PostgreSQL - EXTRACT Function

In PostgreSQL, the EXTRACT() function is a powerful tool used to retrieve specific components of a date or time value. Whether you need to query for a particular year, month, day, or even more detailed time attributes, EXTRACT() can help you extract these fields from date and time values efficiently.

From this article, we can better understand the EXTRACT Function in PostgreSQL.

Syntax

EXTRACT(field FROM source)

Parameters

Let's analyze the above syntax:

• field: Specifies the date or time component you want to extract. This can be a year, month, day, quarter, or other temporal field.
• source: The date or time value from which the field is extracted. This can be of type TIMESTAMP, DATE, or INTERVAL.

PostgreSQL EXTRACT Function Examples

Now let us take a look at some of the examples of EXTRACT() Function in PostgreSQL to better understand the concept.

Example 1: Extracting the Year

The below statement extracts year from a timestamp.

Query:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2020-12-31 13:30:15');

Output:

Explanation: The output will display the year extracted from the given timestamp, which is '2020'.

Example 2: Extracting the Quarter

The below statement extracts the quarter from a timestamp.

Query:

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2020-12-31 13:30:15');

Output:

Explanation: The output will show the quarter of the year, which is '4' for the given timestamp.

Example 3: Extracting the Month

The below statement extracts month from a timestamp.

Query:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2020-12-31 13:30:15');

Output:

Explanation: The output will be '12', indicating December.

Important Points About PostgreSQL EXTRACT Function

• The field argument in EXTRACT() should be specified in uppercase (e.g., YEAR, MONTH).
• When using DATE values, PostgreSQL implicitly converts them to TIMESTAMP for EXTRACT().
• When using EXTRACT() with INTERVAL, the function can retrieve components like days, hours, minutes, and seconds.
• The EXTRACT() function does not directly support extraction of fractional seconds (milliseconds or microseconds). For sub-second precision, you might need to use functions like DATE_PART() or directly extract from a TIMESTAMP with formatting.