PostgreSQL - EXTRACT Function

PostgreSQL EXTRACT() function retrieves a specific part of a date, time or timestamp value, such as the year, month, day, hour or minute. It is commonly used for date and time analysis in SQL queries. Supports filtering, grouping and analyzing date and time data in SQL queries.

Syntax

SELECT EXTRACT(field FROM source);

Where:

• field: The date or time part to extract (such as YEAR, MONTH, DAY, HOUR, MINUTE or SECOND).
• source: The date, time or timestamp value from which the field is extracted.

Examples

Consider the following events table for the examples below:

Example 1: Extract the Year from a Timestamp

Query:

SELECT event_name,
       EXTRACT(YEAR FROM event_date) AS event_year
FROM events;

Output:

• The EXTRACT() function retrieves the year from the event_data column.

Example 2: Extract the Month from a Timestamp

Query:

SELECT event_name,
       EXTRACT(MONTH FROM event_date) AS event_month
FROM events;

Output:

• The EXTRACT() function retrieves the month from the event_date column.