Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL EXTRACT

PostgreSQL's EXTRACT function is used to retrieve specific subfields such as year, month, day, hour, etc., from date/time values. It is essential for date-time manipulation and analysis in SQL queries.

Usage

The EXTRACT function is particularly useful when you need to isolate a specific part of a date/time value for filtering, grouping, or display purposes. Its syntax is straightforward:

EXTRACT(field FROM source)

In this syntax, field represents the part of the date/time you want to extract (e.g., YEAR, MONTH, DOW, DOY, EPOCH), and source is any expression returning a timestamp, date, interval, or time value, not just literals.

Examples

1. Extracting Year

SELECT EXTRACT(YEAR FROM '2023-10-14'::date) AS year;

This example extracts the year 2023 from the specified date.

2. Extracting Month from Current Date

SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS month;

Here, the EXTRACT function retrieves the current month from the system date.

3. Extracting Hour from Timestamp

SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-10-14 15:23:45') AS hour;

This example extracts the hour 15 from the given timestamp.

4. Extracting Day of the Week

SELECT EXTRACT(DOW FROM CURRENT_DATE) AS day_of_week;

This example retrieves the day of the week, where Sunday is 0 and Saturday is 6.

5. Using EXTRACT with GROUP BY

SELECT EXTRACT(YEAR FROM order_date) AS order_year, COUNT(*)
FROM orders
GROUP BY order_year;

This example groups orders by year, demonstrating the use of EXTRACT with GROUP BY.

Tips and Best Practices

  • Precision matters. Ensure the source is in the correct date/time format to avoid errors or unexpected results.
  • Combine with other functions. Use EXTRACT with GROUP BY or ORDER BY to organize results by time intervals.
  • Be mindful of time zones. Consider the time zone context of your data to ensure accurate extraction.
  • Check for null values. Handle potential nulls in date/time fields using COALESCE or similar functions to prevent query failures. For example:
SELECT EXTRACT(YEAR FROM COALESCE(order_date, CURRENT_DATE)) AS year;
  • Return types. EXTRACT returns a double precision value for all fields except when the source is an interval, where it returns an integer for fields such as YEAR, MONTH, etc.
  • EXTRACT vs DATE_PART. Both functions serve similar purposes, but DATE_PART is a more traditional function syntax in PostgreSQL. Choose based on personal or project convention.