PostgreSQL DATE_PART and EXTRACT Functions

The DATE_PART() and EXTRACT() functions in PostgreSQL extract specific components (year, month, day, hour, etc.) from date and timestamp values. EXTRACT is SQL standard while DATE_PART is PostgreSQL-specific but functionally equivalent.

Syntax

-- SQL standard syntax
EXTRACT(field FROM source)

-- PostgreSQL specific syntax
DATE_PART('field', source)

-- Common fields: year, month, day, hour, minute, second, dow (day of week), doy (day of year), week, quarter, epoch

Official Documentation

Example usage

-- Extract year, month, day
SELECT
    EXTRACT(YEAR FROM NOW()) AS year,
    EXTRACT(MONTH FROM NOW()) AS month,
    EXTRACT(DAY FROM NOW()) AS day;
-- Result: year=2025, month=12, day=26

-- Using DATE_PART (equivalent)
SELECT
    DATE_PART('year', NOW()) AS year,
    DATE_PART('month', NOW()) AS month,
    DATE_PART('day', NOW()) AS day;

-- Extract time components
SELECT
    EXTRACT(HOUR FROM NOW()) AS hour,
    EXTRACT(MINUTE FROM NOW()) AS minute,
    EXTRACT(SECOND FROM NOW()) AS second;

-- Day of week (0=Sunday, 6=Saturday)
SELECT EXTRACT(DOW FROM CURRENT_DATE) AS day_of_week;

-- Day of year (1-365/366)
SELECT EXTRACT(DOY FROM CURRENT_DATE) AS day_of_year;

-- Week number
SELECT EXTRACT(WEEK FROM CURRENT_DATE) AS week_number;

-- Quarter (1-4)
SELECT EXTRACT(QUARTER FROM CURRENT_DATE) AS quarter;

-- Group by month
SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    COUNT(*) AS order_count
FROM orders
GROUP BY 1, 2
ORDER BY year, month;

-- Unix timestamp (seconds since 1970-01-01)
SELECT EXTRACT(EPOCH FROM NOW()) AS unix_timestamp;

-- Filter by day of week (find all Mondays)
SELECT * FROM events
WHERE EXTRACT(DOW FROM event_date) = 1;

-- Get timezone offset in hours
SELECT EXTRACT(TIMEZONE_HOUR FROM NOW()) AS tz_offset;

Common issues

1. Return type: Both functions return DOUBLE PRECISION, not integers. Cast to INT if needed.
2. Day of week: PostgreSQL uses 0=Sunday, unlike some systems that use 1=Monday (use ISODOW for ISO standard).
3. Week numbering: WEEK follows US convention. Use ISO WEEK for ISO 8601 standard.
4. Time zones: EXTRACT respects the time zone of timestamp with time zone values.

Best practices

1. Use EXTRACT for SQL standard compliance and better portability.
2. Cast results to appropriate types: EXTRACT(YEAR FROM date)::INT.
3. For ISO day of week (1=Monday), use EXTRACT(ISODOW FROM date).
4. Use EPOCH extraction for Unix timestamp conversions.

Frequently Asked Questions

Q: What's the difference between EXTRACT and DATE_PART?
A: They're functionally identical. EXTRACT uses SQL standard syntax EXTRACT(field FROM source), while DATE_PART uses function syntax DATE_PART('field', source). EXTRACT is preferred for portability.

Q: Why does EXTRACT return floating-point numbers instead of integers?
A: EXTRACT returns DOUBLE PRECISION to accommodate fractional seconds. Cast to INTEGER when needed: EXTRACT(YEAR FROM date)::INT.

Q: How do I get the day of week where Monday is 1?
A: Use ISODOW instead of DOW: EXTRACT(ISODOW FROM date) returns 1=Monday through 7=Sunday (ISO 8601 standard).

Q: Can I extract multiple fields at once?
A: No, but you can call EXTRACT multiple times in the same SELECT: SELECT EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date).

Q: How do I convert a timestamp to Unix epoch time?
A: Use EXTRACT(EPOCH FROM timestamp)::BIGINT to get seconds since 1970-01-01 00:00:00 UTC.