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.