In this lesson, you will learn how to summarize time-series data into meaningful chronological blocks—like days, weeks, or months—to uncover trends.
Why Group by Intervals?
When working with raw timestamps, your data is often too granular to be useful. For example, a sales table might record every individual purchase with an exact time (2023-10-01 09:12:05). If you want to know how much revenue you made each day, you cannot group by the full timestamp because every record has a unique time. Instead, you need to "truncate" the time, stripping away the hours and minutes so that all events on the same day share the same value.
Truncating for Analysis
In SQL, we use transformation functions to convert specific timestamps into a standardized unit (like a day or a month). By applying this transformation in your GROUP BY clause, you force the database to bundle records together based on that common time interval.
Consider this query that calculates daily revenue:
SELECT
DATE_TRUNC('day', transaction_time) AS sale_day,
SUM(amount) AS daily_total
FROM sales
GROUP BY 1
ORDER BY sale_day;
In the example above, DATE_TRUNC('day', transaction_time) takes a precise timestamp and rounds it down to the beginning of that day (e.g., 2023-10-01 00:00:00). Because we use this in the GROUP BY clause, all transactions that occurred on October 1st are lumped into the same bucket, allowing the SUM function to calculate the total for that specific day.
Flexibility in Granularity
You are not limited to days. Depending on your needs, you can adjust your interval to:
'week': To analyze performance across standard work or calendar weeks.'month': To compare seasonal performance over the course of a year.'quarter': To generate high-level financial reports for stakeholders.
By transforming timestamps into discrete buckets, you turn overwhelming lists of individual events into clear, actionable trends.
Key Takeaway: To aggregate time-series data, transform your timestamps into broader intervals, then group by that result to consolidate individual events into meaningful blocks of time.