PostgreSQL – EXTRACT Function

The PostgreSQL EXTRACT() function is used to query for field associated with date and time such as a year, month, and day from a date/time value.

Syntax: EXTRACT(field FROM source)

Let’s analyze the above syntax:

• In the above syntax the field argument is used to specify fields that is to be extracted from the date/time value.
• The source is generally either a TIMESTAMP type or an INTERVAL type. Depending upon the values passed the type is set. For instance, if we pass a DATE value, the function adapt to a TIMESTAMP type value.

Example 1:

The below statement extracts year from a timestamp:

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

Output:

Example 2:

The below statement extracts the quarter from a timestamp:

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

Output:

Example 3:

The below statement extracts month from a timestamp:

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

Output: