SQL DATE_TRUNC: How to Truncate Dates for Time-Based Analysis

DATE_TRUNC rounds a timestamp down to a specified precision (year, quarter, month, week, day, hour). It is the most important function for time-based analytics because it lets you group events by time period without extracting and recombining date parts.

Basic Syntax

DATE_TRUNC('month', timestamp_column)

This truncates any timestamp to the first moment of its month:

• '2026-05-15 14:30:00' becomes '2026-05-01 00:00:00'
• '2026-05-01 09:15:22' becomes '2026-05-01 00:00:00'
• '2026-05-31 23:59:59' becomes '2026-05-01 00:00:00'

Truncation Levels

The Primary Use Case: GROUP BY Time Period

-- Monthly revenue
SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue,
    COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

-- Weekly signups
SELECT
    DATE_TRUNC('week', created_at) AS week,
    COUNT(*) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

-- Hourly traffic (for capacity planning)
SELECT
    DATE_TRUNC('hour', event_time) AS hour,
    COUNT(*) AS requests
FROM api_logs
WHERE event_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1;

Cross-Database Syntax

DATE_TRUNC is not universal. Here is how to achieve the same result across databases:

PostgreSQL, Snowflake, Redshift

DATE_TRUNC('month', order_date)

BigQuery

DATE_TRUNC(order_date, MONTH)
-- Note: precision comes second, no quotes

MySQL

MySQL lacks DATE_TRUNC. Use alternatives:

-- Month truncation
DATE_FORMAT(order_date, '%Y-%m-01')

-- Or:
LAST_DAY(order_date) - INTERVAL (DAY(LAST_DAY(order_date)) - 1) DAY

-- Week truncation
DATE(order_date - INTERVAL WEEKDAY(order_date) DAY)

SQL Server

-- Month truncation
DATETRUNC(month, order_date)  -- SQL Server 2022+

-- Older versions:
DATEADD(month, DATEDIFF(month, 0, order_date), 0)

Analytical Patterns with DATE_TRUNC

Month-over-Month Comparison

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
        / LAG(revenue) OVER (ORDER BY month), 1) AS mom_growth
FROM monthly
ORDER BY month DESC;

Year-over-Year Comparison

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    m.month,
    m.revenue AS current_year,
    prev.revenue AS prior_year,
    ROUND((m.revenue - prev.revenue) * 100.0 / prev.revenue, 1) AS yoy_growth
FROM monthly m
LEFT JOIN monthly prev ON m.month = prev.month + INTERVAL '1 year'
WHERE m.month >= DATE_TRUNC('year', CURRENT_DATE)
ORDER BY m.month;

Quarter-to-Date

SELECT
    SUM(amount) AS qtd_revenue
FROM orders
WHERE order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
  AND order_date < CURRENT_DATE;

Daily Trend Within Current Month

SELECT
    DATE_TRUNC('day', order_date) AS day,
    SUM(amount) AS daily_revenue,
    SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('day', order_date)) AS mtd_cumulative
FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 1
ORDER BY 1;

Cohort Retention (Months Since Signup)

SELECT
    DATE_TRUNC('month', u.created_at) AS cohort,
    DATEDIFF('month', DATE_TRUNC('month', u.created_at), DATE_TRUNC('month', e.event_date)) AS months_since_signup,
    COUNT(DISTINCT e.user_id) AS active_users
FROM users u
JOIN events e ON u.id = e.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

Common Mistakes

1. Forgetting Timezone Handling

-- If timestamps are in UTC but you want to truncate in local time:
DATE_TRUNC('day', event_time AT TIME ZONE 'America/New_York')

2. Using DATE_TRUNC in WHERE (Prevents Index Usage)

-- SLOW: Function on column prevents index usage
WHERE DATE_TRUNC('month', order_date) = '2026-05-01'

-- FAST: Range condition uses index
WHERE order_date >= '2026-05-01' AND order_date < '2026-06-01'

3. Week Start Day Confusion

Different databases start weeks on different days (Monday vs Sunday). Check your database's default and adjust if needed:

-- PostgreSQL: weeks start Monday by default
-- To get Sunday-starting weeks:
DATE_TRUNC('week', order_date + INTERVAL '1 day') - INTERVAL '1 day'

Summary

DATE_TRUNC is the essential function for time-based analytics. Use it to group data by any time period (year, quarter, month, week, day, hour) with a single function call. Remember that syntax varies across databases, timezone handling matters, and applying DATE_TRUNC in WHERE clauses can prevent index usage (use range conditions instead).