Dates and time: SQL patterns and examples
Grouping by period, comparing with earlier periods, filling gaps and measuring durations. Most date bugs come from time zones, partial periods and months with no data.
- Group by MonthGrouping by month rolls individual records up into one row per calendar month, the basis of almost every trend report.
- Group by WeekGrouping by week rolls records up into one row per week, which smooths daily noise without hiding short-term movement.
- Month-over-Month GrowthMonth-over-month growth compares each month's figure with the month before it, as a percentage change.
- Year-over-Year GrowthYear-over-year growth compares each month with the same month a year earlier, which removes seasonality from the comparison.
- Fill Missing DatesFilling missing dates builds a complete calendar and joins your data to it, so days or months with no records show as zero instead of disappearing.
- Date DifferenceA date difference measures the time between two dates or timestamps, in days, hours or whole months, such as time to ship or time to resolve.
- Last N DaysA last-N-days filter keeps only rows from the most recent window, such as the previous 30 complete days.
- Quarter to DateQuarter to date totals everything from the first day of the current quarter up to today, and can be compared with the same stretch last year.
- Convert Time ZonesConverting time zones changes stored UTC timestamps into your local time, so that a day's activity is counted on the right day.