How to Use EXTRACT Function in PostgreSQL: Syntax and Examples

In PostgreSQL, use the EXTRACT function to get specific parts like year, month, or day from date/time values. The syntax is EXTRACT(field FROM source), where field is the part you want and source is a date or timestamp.

Syntax

The EXTRACT function extracts a specified part from a date or time value.

• field: The part you want to get, such as year, month, day, hour, minute, or second.
• source: The date, time, or timestamp value you want to extract from.

sql

EXTRACT(field FROM source)

Example

This example shows how to extract the year, month, and day from a timestamp value.

sql

SELECT
  EXTRACT(YEAR FROM TIMESTAMP '2024-06-15 14:30:00') AS year,
  EXTRACT(MONTH FROM TIMESTAMP '2024-06-15 14:30:00') AS month,
  EXTRACT(DAY FROM TIMESTAMP '2024-06-15 14:30:00') AS day;

Output

 year | month | day 
------+-------+-----
 2024 |     6 |  15
(1 row)

Common Pitfalls

Common mistakes include:

• Using invalid field names like YEARS instead of YEAR.
• Trying to extract from a string without casting it to a date or timestamp.
• Confusing EXTRACT with DATE_PART (they behave similarly but syntax differs).

sql

/* Wrong: invalid field name */
-- SELECT EXTRACT(YEARS FROM CURRENT_DATE);

/* Right: correct field name */
SELECT EXTRACT(YEAR FROM CURRENT_DATE);

Output

 year 
------
 2024
(1 row)

Quick Reference

Field	Description
YEAR	Year part of the date/time
MONTH	Month part (1-12)
DAY	Day of the month (1-31)
HOUR	Hour of the day (0-23)
MINUTE	Minute of the hour (0-59)
SECOND	Second of the minute (0-59)

Key Takeaways

Use EXTRACT(field FROM source) to get parts of date/time in PostgreSQL.

Field names must be valid keywords like YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.

Source must be a date, time, or timestamp type, not a plain string.

EXTRACT returns a numeric value representing the requested part.

Common errors come from misspelling fields or wrong data types.