PostgreSQL EXTRACT Function (original) (raw)

Last Updated : 15 Jul, 2025

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.