PostgreSQL DATE_TRUNC Function

The DATE_TRUNC() function in PostgreSQL truncates a timestamp or interval to a specified precision level (year, month, day, hour, etc.). It's essential for grouping time-series data and performing date-based aggregations.

Syntax

DATE_TRUNC(field, source [, time_zone])

-- Common fields: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium

Official Documentation

Example usage

-- Truncate to different precision levels
SELECT
    DATE_TRUNC('year', '2025-12-26 14:30:45'::TIMESTAMP) AS year,
    DATE_TRUNC('month', '2025-12-26 14:30:45'::TIMESTAMP) AS month,
    DATE_TRUNC('day', '2025-12-26 14:30:45'::TIMESTAMP) AS day,
    DATE_TRUNC('hour', '2025-12-26 14:30:45'::TIMESTAMP) AS hour;
-- Results:
-- year:  2025-01-01 00:00:00
-- month: 2025-12-01 00:00:00
-- day:   2025-12-26 00:00:00
-- hour:  2025-12-26 14:00:00

-- Group orders by month
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

-- Group by week
SELECT
    DATE_TRUNC('week', created_at) AS week_start,
    COUNT(*) AS signups
FROM users
GROUP BY week_start
ORDER BY week_start;

-- Hourly aggregation
SELECT
    DATE_TRUNC('hour', timestamp) AS hour,
    COUNT(*) AS events,
    AVG(response_time) AS avg_response_ms
FROM api_logs
WHERE timestamp > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- Find records at the start of the month
SELECT * FROM reports
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE);

-- Quarter-based reporting
SELECT
    DATE_TRUNC('quarter', order_date) AS quarter,
    COUNT(*) AS orders,
    SUM(amount) AS total_revenue
FROM sales
GROUP BY quarter
ORDER BY quarter;

-- Time-series bucketing for charts
SELECT
    DATE_TRUNC('minute', created_at) AS minute_bucket,
    COUNT(*) AS request_count
FROM requests
WHERE created_at > NOW() - INTERVAL '1 hour'
GROUP BY minute_bucket
ORDER BY minute_bucket;

Common issues

1. Week start day: DATE_TRUNC('week', ...) starts on Monday by default (ISO 8601).
2. Time zone handling: For timestamp with time zone, truncation considers the time zone.
3. Return type: Returns the same type as input (timestamp or interval).
4. Date only: To get date only, cast result to DATE or use ::DATE.

Best practices

1. Use DATE_TRUNC for time-series grouping and aggregations rather than EXTRACT.
2. Create indexes on truncated dates for better query performance on large tables.
3. For date-only truncation, DATE_TRUNC('day', timestamp)::DATE is cleaner than multiple EXTRACT calls.
4. Use appropriate precision levels to balance granularity and query performance.

Frequently Asked Questions

Q: What day does DATE_TRUNC('week', ...) start on?
A: DATE_TRUNC truncates to the Monday of the week (ISO 8601 standard). If you need Sunday as the week start, use date arithmetic: DATE_TRUNC('week', date)::DATE - EXTRACT(DOW FROM date)::INT.

Q: How is DATE_TRUNC different from EXTRACT?
A: EXTRACT pulls out a specific component (returns a number like 2025 for year), while DATE_TRUNC returns a timestamp rounded down to that precision (like 2025-01-01 00:00:00 for year).

Q: Can I use DATE_TRUNC with intervals?
A: Yes, DATE_TRUNC can truncate intervals: DATE_TRUNC('hour', INTERVAL '2 days 3 hours 45 minutes') returns '2 days 3 hours'.

Q: How do I truncate to a custom interval like 15 minutes?
A: Use epoch-based bucketing: TO_TIMESTAMP(FLOOR(EXTRACT(EPOCH FROM timestamp) / 900) * 900) where 900 = 15*60 seconds.

Q: Does DATE_TRUNC work with dates or only timestamps?
A: It works with both DATE and TIMESTAMP types. When used with DATE, it returns a timestamp at midnight for that precision level.