Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-extract-function/ below:

PostgreSQL - EXTRACT Function - GeeksforGeeks

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.

EXTRACT(field FROM source)

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.

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

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'.

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.

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.

• 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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4