Extracting Date Components

Often you do not need an entire date -- you need just the year, the month, the day of the week, or the hour. PostgreSQL provides two primary ways to pull individual components from dates and timestamps: the EXTRACT function and the date_part function. They are functionally equivalent, but have different syntax styles.

EXTRACT(field FROM source)

EXTRACT pulls a specific component from a date, time, timestamp, or interval.

EXTRACT(field FROM source)

Year, Month, Day

SELECT EXTRACT(YEAR FROM DATE '2025-08-15');
-- Returns: 2025

SELECT EXTRACT(MONTH FROM DATE '2025-08-15');
-- Returns: 8

SELECT EXTRACT(DAY FROM DATE '2025-08-15');
-- Returns: 15

Use these to filter or group data by calendar components:

-- Find all orders placed in March (any year)
SELECT *
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 3;

-- Count orders per year
SELECT
  EXTRACT(YEAR FROM order_date) AS order_year,
  COUNT(*) AS total_orders
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY order_year;

Hour, Minute, Second

For timestamps that include time information:

SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-08-15 14:30:45');
-- Returns: 14

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2025-08-15 14:30:45');
-- Returns: 30

SELECT EXTRACT(SECOND FROM TIMESTAMP '2025-08-15 14:30:45');
-- Returns: 45

Analyzing log events by hour of the day:

SELECT
  EXTRACT(HOUR FROM created_at) AS event_hour,
  COUNT(*) AS event_count
FROM logs
GROUP BY EXTRACT(HOUR FROM created_at)
ORDER BY event_hour;

Day of Week (DOW)

DOW returns the day of the week as an integer: Sunday = 0, Monday = 1, ..., Saturday = 6.

SELECT EXTRACT(DOW FROM DATE '2025-04-28');
-- Returns: 1  (Monday)

Analyzing order patterns by day of week:

SELECT
  EXTRACT(DOW FROM order_date) AS day_number,
  CASE EXTRACT(DOW FROM order_date)
    WHEN 0 THEN 'Sunday'
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednesday'
    WHEN 4 THEN 'Thursday'
    WHEN 5 THEN 'Friday'
    WHEN 6 THEN 'Saturday'
  END AS day_name,
  COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(DOW FROM order_date)
ORDER BY day_number;

ISODOW

ISODOW is similar to DOW but follows the ISO 8601 standard where Monday = 1 and Sunday = 7:

SELECT EXTRACT(ISODOW FROM DATE '2025-04-27');
-- Returns: 7  (Sunday, which is 0 with DOW but 7 with ISODOW)

Day of Year (DOY)

DOY returns the day number within the year (1 through 365 or 366):

SELECT EXTRACT(DOY FROM DATE '2025-03-01');
-- Returns: 60

Quarter

QUARTER returns which quarter of the year the date falls in (1-4):

SELECT EXTRACT(QUARTER FROM DATE '2025-08-15');
-- Returns: 3

Quarterly revenue analysis:

SELECT
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(QUARTER FROM order_date) AS order_quarter,
  COUNT(*) AS order_count,
  SUM(total_price) AS quarterly_revenue
FROM orders
GROUP BY
  EXTRACT(YEAR FROM order_date),
  EXTRACT(QUARTER FROM order_date)
ORDER BY order_year, order_quarter;

Week

WEEK returns the ISO 8601 week number of the year:

SELECT EXTRACT(WEEK FROM DATE '2025-01-06');
-- Returns: 2

Epoch

EPOCH converts a timestamp or interval to the total number of seconds. For timestamps, it returns the number of seconds since January 1, 1970 (Unix epoch).

-- Seconds since Unix epoch
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2025-01-01 00:00:00');
-- Returns: 1735689600

-- Interval to seconds
SELECT EXTRACT(EPOCH FROM INTERVAL '1 day 2 hours');
-- Returns: 93600  (86400 + 7200)

This is useful for calculating precise time differences in seconds:

SELECT
  EXTRACT(EPOCH FROM (TIMESTAMP '2025-03-15 14:00:00' - TIMESTAMP '2025-03-15 10:30:00')) AS diff_seconds;
-- Returns: 12600  (3.5 hours in seconds)

The date_part Function

date_part is functionally identical to EXTRACT, but uses a different syntax. The field name is passed as a string:

date_part('field', source)

SELECT date_part('year', DATE '2025-08-15');
-- Returns: 2025

SELECT date_part('month', DATE '2025-08-15');
-- Returns: 8

SELECT date_part('dow', DATE '2025-04-28');
-- Returns: 1

The choice between EXTRACT and date_part is largely a matter of style. EXTRACT follows the SQL standard, while date_part is a PostgreSQL-specific function. Both return double precision (a floating-point number), though in practice the results for date components are always whole numbers.

-- These two queries produce identical results
SELECT EXTRACT(MONTH FROM order_date) AS month_num FROM orders;
SELECT date_part('month', order_date) AS month_num FROM orders;

Practical Patterns

Filtering by Multiple Components

Find orders placed on weekdays in Q1:

SELECT *
FROM orders
WHERE EXTRACT(QUARTER FROM order_date) = 1
  AND EXTRACT(DOW FROM order_date) BETWEEN 1 AND 5;

Grouping by Multiple Time Dimensions

SELECT
  EXTRACT(YEAR FROM order_date) AS yr,
  EXTRACT(MONTH FROM order_date) AS mo,
  COUNT(*) AS orders,
  SUM(total_price) AS revenue
FROM orders
GROUP BY
  EXTRACT(YEAR FROM order_date),
  EXTRACT(MONTH FROM order_date)
ORDER BY yr, mo;

Analyzing Log Severity by Time

SELECT
  EXTRACT(HOUR FROM created_at) AS log_hour,
  severity,
  COUNT(*) AS event_count
FROM logs
GROUP BY
  EXTRACT(HOUR FROM created_at),
  severity
ORDER BY log_hour, severity;

Finding Peak Activity Periods

SELECT
  EXTRACT(DOW FROM created_at) AS day_of_week,
  EXTRACT(HOUR FROM created_at) AS hour_of_day,
  COUNT(*) AS events
FROM logs
GROUP BY
  EXTRACT(DOW FROM created_at),
  EXTRACT(HOUR FROM created_at)
ORDER BY events DESC
LIMIT 5;

Try It Yourself

1. Write a query that shows each order's order_date, the year, the month number, and the day of the week.

2. Count how many log events occurred on each day of the week. Include the day name using a CASE expression.

3. Find all orders placed in the fourth quarter (October, November, December) using EXTRACT(QUARTER ...).

4. Calculate the total number of seconds between the earliest and latest created_at values in the logs table using EXTRACT(EPOCH ...).

5. Group log events by hour and severity, and count the events in each group. Order by hour.