How to Use EXTRACT in PostgreSQL
The EXTRACT function is PostgreSQL's primary tool for pulling specific date and time components from timestamp values. Whether you need to filter orders from a particular month, group sales by hour...
Key Insights
- EXTRACT pulls specific date/time components from timestamps and returns them as numeric values, making it essential for time-based filtering, grouping, and reporting in PostgreSQL
- Unlike DATE_TRUNC which rounds timestamps, EXTRACT isolates individual fields like year, month, or hour without modifying the underlying data structure
- Creating functional indexes on EXTRACT expressions can dramatically improve query performance when frequently filtering or grouping by specific time components
Introduction to EXTRACT Function
The EXTRACT function is PostgreSQL’s primary tool for pulling specific date and time components from timestamp values. Whether you need to filter orders from a particular month, group sales by hour of day, or calculate someone’s age, EXTRACT gives you direct access to the individual pieces of temporal data.
The syntax is straightforward:
EXTRACT(field FROM source)
Here’s a simple example extracting the year from the current timestamp:
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS current_year;
This returns a numeric value representing the year, like 2024. The function works with DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and INTERVAL types, making it versatile for various temporal operations.
Common use cases include generating monthly reports, filtering data by business hours, analyzing seasonal patterns, and creating time-based aggregations. The key advantage is that EXTRACT returns a simple numeric value that’s easy to use in WHERE clauses, GROUP BY statements, and calculations.
Extractable Date/Time Fields
PostgreSQL offers a comprehensive set of fields you can extract from temporal data types. The most commonly used fields are:
SELECT
EXTRACT(YEAR FROM TIMESTAMP '2024-03-15 14:30:45') AS year,
EXTRACT(MONTH FROM TIMESTAMP '2024-03-15 14:30:45') AS month,
EXTRACT(DAY FROM TIMESTAMP '2024-03-15 14:30:45') AS day,
EXTRACT(HOUR FROM TIMESTAMP '2024-03-15 14:30:45') AS hour,
EXTRACT(MINUTE FROM TIMESTAMP '2024-03-15 14:30:45') AS minute,
EXTRACT(SECOND FROM TIMESTAMP '2024-03-15 14:30:45') AS second;
This returns:
year | month | day | hour | minute | second
-----|-------|-----|------|--------|-------
2024 | 3 | 15 | 14 | 30 | 45
Beyond the basics, PostgreSQL provides several specialized fields:
SELECT
EXTRACT(DOW FROM TIMESTAMP '2024-03-15 14:30:45') AS day_of_week,
EXTRACT(DOY FROM TIMESTAMP '2024-03-15 14:30:45') AS day_of_year,
EXTRACT(WEEK FROM TIMESTAMP '2024-03-15 14:30:45') AS week_number,
EXTRACT(QUARTER FROM TIMESTAMP '2024-03-15 14:30:45') AS quarter,
EXTRACT(EPOCH FROM TIMESTAMP '2024-03-15 14:30:45') AS unix_timestamp;
Note that DOW (day of week) returns 0 for Sunday through 6 for Saturday. DOY (day of year) returns 1-366. EPOCH converts the entire timestamp to seconds since January 1, 1970, which is useful for calculating durations or interfacing with systems that use Unix timestamps.
Practical Use Cases
EXTRACT shines in real-world scenarios where you need to slice temporal data. Here’s how to generate a monthly sales report:
SELECT
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY
EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
ORDER BY year, month;
This aggregates all orders by month, giving you a clean time series of your business metrics.
For analyzing traffic patterns or system load, you might want to group by hour:
SELECT
EXTRACT(HOUR FROM created_at) AS hour_of_day,
COUNT(*) AS request_count,
AVG(response_time_ms) AS avg_response_time
FROM api_requests
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY EXTRACT(HOUR FROM created_at)
ORDER BY hour_of_day;
Filtering by specific time components is equally straightforward:
-- Find all orders placed on weekends
SELECT *
FROM orders
WHERE EXTRACT(DOW FROM order_date) IN (0, 6);
-- Get transactions during business hours (9 AM - 5 PM)
SELECT *
FROM transactions
WHERE EXTRACT(HOUR FROM transaction_time) BETWEEN 9 AND 17;
-- Calculate age from birthdate
SELECT
name,
birthdate,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) AS age
FROM users;
EXTRACT vs DATE_PART vs DATE_TRUNC
PostgreSQL provides three functions that seem similar but serve different purposes. Understanding when to use each is crucial for writing efficient queries.
-- EXTRACT: Returns numeric value
SELECT EXTRACT(MONTH FROM TIMESTAMP '2024-03-15 14:30:45');
-- Returns: 3
-- DATE_PART: Identical to EXTRACT, different syntax
SELECT DATE_PART('month', TIMESTAMP '2024-03-15 14:30:45');
-- Returns: 3
-- DATE_TRUNC: Truncates to specified precision
SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-15 14:30:45');
-- Returns: 2024-03-01 00:00:00
EXTRACT and DATE_PART are functionally identical—they both return numeric values. EXTRACT uses SQL standard syntax, while DATE_PART is PostgreSQL-specific. Use EXTRACT for portability.
DATE_TRUNC is fundamentally different. It returns a timestamp rounded down to the specified precision. This is useful when you want to keep working with timestamps rather than converting to numbers:
-- Group by month using DATE_TRUNC
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
Performance-wise, all three functions are comparable. Choose based on what you need: a number (EXTRACT/DATE_PART) or a truncated timestamp (DATE_TRUNC).
Working with Timezones
EXTRACT handles timezone-aware timestamps (TIMESTAMPTZ) by converting them to your session’s timezone before extraction:
-- Set session timezone
SET timezone = 'America/New_York';
SELECT
EXTRACT(HOUR FROM TIMESTAMPTZ '2024-03-15 14:30:45 UTC') AS hour_ny,
EXTRACT(HOUR FROM TIMESTAMPTZ '2024-03-15 14:30:45 America/Los_Angeles') AS hour_la;
This returns different hours based on timezone conversion. The UTC timestamp shows 9 AM (14:00 - 5 hours), while the LA timestamp shows 6 AM.
You can also extract timezone information itself:
SELECT
EXTRACT(TIMEZONE FROM TIMESTAMPTZ '2024-03-15 14:30:45-04') AS tz_offset_seconds,
EXTRACT(TIMEZONE_HOUR FROM TIMESTAMPTZ '2024-03-15 14:30:45-04') AS tz_hours,
EXTRACT(TIMEZONE_MINUTE FROM TIMESTAMPTZ '2024-03-15 14:30:45-04') AS tz_minutes;
A common pitfall is assuming EXTRACT on TIMESTAMPTZ gives you UTC values. It doesn’t—it uses your session timezone. If you need UTC values explicitly:
SELECT EXTRACT(HOUR FROM (created_at AT TIME ZONE 'UTC')) AS hour_utc
FROM events;
Advanced Patterns and Tips
Creating functional indexes on EXTRACT expressions can dramatically improve query performance when you frequently filter or group by specific time components:
-- Create an index for queries filtering by month
CREATE INDEX idx_orders_month ON orders (EXTRACT(MONTH FROM order_date));
-- Create an index for hour-of-day analysis
CREATE INDEX idx_requests_hour ON api_requests (EXTRACT(HOUR FROM created_at));
Now queries filtering by these extracted values can use the index instead of scanning the entire table.
For business logic that doesn’t align with calendar periods, combine EXTRACT with CASE statements:
-- Calculate fiscal quarter (assuming fiscal year starts in July)
SELECT
order_date,
CASE
WHEN EXTRACT(MONTH FROM order_date) BETWEEN 7 AND 9 THEN 1
WHEN EXTRACT(MONTH FROM order_date) BETWEEN 10 AND 12 THEN 2
WHEN EXTRACT(MONTH FROM order_date) BETWEEN 1 AND 3 THEN 3
ELSE 4
END AS fiscal_quarter,
total_amount
FROM orders;
-- Categorize by time of day
SELECT
CASE
WHEN EXTRACT(HOUR FROM created_at) BETWEEN 6 AND 11 THEN 'Morning'
WHEN EXTRACT(HOUR FROM created_at) BETWEEN 12 AND 17 THEN 'Afternoon'
WHEN EXTRACT(HOUR FROM created_at) BETWEEN 18 AND 21 THEN 'Evening'
ELSE 'Night'
END AS time_period,
COUNT(*) AS event_count
FROM events
GROUP BY 1;
Common Errors and Troubleshooting
Invalid field names produce clear errors:
SELECT EXTRACT(MONTHS FROM CURRENT_TIMESTAMP);
-- ERROR: MONTHS is not a recognized field
-- Use MONTH instead
Type compatibility matters. EXTRACT works with temporal types but not strings:
-- This fails
SELECT EXTRACT(YEAR FROM '2024-03-15');
-- ERROR: function extract(unknown, unknown) does not exist
-- Cast the string first
SELECT EXTRACT(YEAR FROM '2024-03-15'::DATE);
-- Returns: 2024
NULL handling is straightforward—EXTRACT returns NULL when given NULL input:
SELECT EXTRACT(YEAR FROM NULL::TIMESTAMP);
-- Returns: NULL
This means you should handle NULLs appropriately in your queries:
SELECT
COALESCE(EXTRACT(YEAR FROM order_date), 0) AS year
FROM orders;
When working with intervals, remember that EXTRACT returns the specific field value, not a normalized total:
SELECT EXTRACT(HOUR FROM INTERVAL '25 hours');
-- Returns: 1 (not 25)
-- For total hours, use EPOCH and divide
SELECT EXTRACT(EPOCH FROM INTERVAL '25 hours') / 3600;
-- Returns: 25
Master EXTRACT and you’ll have precise control over temporal data in PostgreSQL, enabling sophisticated time-based analysis and reporting with clean, performant SQL.