Course contentsShow
← Data Science
Lesson 1043 of 2,14526. Advanced SQLFree lesson

Date Truncation and Rounding

Using DATE_TRUNC or equivalent to round dates to the nearest month, week, day, or hour for aggregation.

Date Truncation and Rounding

You'll learn how to "round down" dates to standard intervals like the start of a month, week, day, or hour—essential for grouping time-series data.

The Core Idea

When analyzing time-based data, you often don't care about the exact timestamp—you want to group events by day, week, or month. Date truncation cuts off the precision beyond a certain level, effectively "rounding down" a timestamp to the beginning of that time period.

Think of it like rounding numbers, but for dates: 2024-03-15 14:37:22 truncated to the month becomes 2024-03-01 00:00:00, and truncated to the day becomes 2024-03-15 00:00:00.

The DATE_TRUNC Function

Most SQL dialects provide DATE_TRUNC (PostgreSQL, Redshift) or similar functions (DATE_BIN, DATEADD + DATEDIFF combos in SQL Server):

-- PostgreSQL / Redshift syntax
DATE_TRUNC('month', order_date)   -- First day of the month
DATE_TRUNC('week', created_at)    -- Monday of that week
DATE_TRUNC('day', timestamp_col)  -- Midnight of that day
DATE_TRUNC('hour', event_time)    -- Top of the hour

Why It Matters for Aggregation

Truncation is your best friend for time-series analysis:

SELECT 
    DATE_TRUNC('month', order_date) AS order_month,
    COUNT(*) AS monthly_orders,
    SUM(amount) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

This groups all orders from the same month together, regardless of the exact day or time they occurred.

Key Takeaway: DATE_TRUNC standardizes timestamps to the start of a specified time interval (year, month, week, day, hour), making it invaluable for grouping and aggregating time-series data in consistent buckets.