Basic Syntax Math Functions Date Functions JSON Functions Databases Tables & Schema Management String Functions Triggers Indexes

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)Offerto da

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;Offerto da

This example extracts the year 2023 from the specified date.

2. Extracting Month from Current Date

SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS month;Offerto da

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;Offerto da

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;Offerto da

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;Offerto da

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;Offerto da

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.

PostqreSQL