Date Handling For Time Series

Why this matters

As a BI Analyst, most product, marketing, and finance questions are time-based. Getting dates and times right means your charts match reality and decisions are trustworthy.

• Daily/weekly/monthly KPI dashboards (DAU, revenue, churn).
• Rolling averages, MoM/YoY growth for trends.
• Handling missing dates so charts don’t have gaps.
• Time zone alignment for stakeholder’s local time.
• Inclusive/exclusive date filters for correct ranges.

Concept explained simply

Dates are continuous. Your data often isn’t. Good time-series SQL aligns raw timestamps into consistent buckets (day/week/month), fills gaps, and applies window math safely.

Mental model

• Truncate timestamps to a bucket (day/week/month).
• Group facts by bucket.
• Fill gaps by joining to a calendar (or generate a series).
• Window for rolling metrics and comparisons.
• Bound time with safe filters: start inclusive, end exclusive.
• Localize timestamps to the reporting time zone first, then bucket.

Core patterns you’ll use

Worked examples

1) Daily events by local day

Goal: Count events per local day for the last 14 days, in America/New_York time. Table: events(user_id, occurred_at TIMESTAMP UTC).

WITH params AS (
  SELECT 
    (DATE_TRUNC('day', (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')) - INTERVAL '13 day')::date AS start_d,
    DATE_TRUNC('day', (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York'))::date AS end_d
), calendar AS (
  SELECT generate_series(p.start_d, p.end_d, INTERVAL '1 day')::date AS d
  FROM params p
), facts AS (
  SELECT DATE_TRUNC('day', (occurred_at AT TIME ZONE 'America/New_York'))::date AS d,
         COUNT(*) AS cnt
  FROM events, params
  WHERE occurred_at >= (p.start_d)::timestamp AT TIME ZONE 'America/New_York' - INTERVAL '1 day'
    AND occurred_at < (p.end_d + 1)::timestamp AT TIME ZONE 'America/New_York'
  GROUP BY 1
)
SELECT c.d, COALESCE(f.cnt, 0) AS events
FROM calendar c
LEFT JOIN facts f USING (d)
ORDER BY c.d;

WITH params AS (
  SELECT DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'America/New_York')) AS end_d,
         DATE_SUB(DATE(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'America/New_York')), INTERVAL 13 DAY) AS start_d
), calendar AS (
  SELECT d FROM UNNEST(GENERATE_DATE_ARRAY((SELECT start_d FROM params),(SELECT end_d FROM params))) AS d
), facts AS (
  SELECT DATE(TIMESTAMP(occurred_at), 'America/New_York') AS d, COUNT(*) AS cnt
  FROM events
  GROUP BY 1
)
SELECT c.d, COALESCE(f.cnt, 0) AS events
FROM calendar c
LEFT JOIN facts f USING (d)
ORDER BY c.d;

2) Fill gaps and 7-day moving average

Goal: Daily signups and 7d moving average. Table: users(user_id, created_at TIMESTAMP UTC).

WITH bounds AS (
  SELECT DATE_TRUNC('day', CURRENT_DATE) - INTERVAL '59 day' AS start_d,
         DATE_TRUNC('day', CURRENT_DATE) AS end_d
), calendar AS (
  SELECT generate_series((SELECT start_d FROM bounds)::date, (SELECT end_d FROM bounds)::date, INTERVAL '1 day')::date AS d
), daily AS (
  SELECT DATE_TRUNC('day', created_at)::date AS d, COUNT(*) AS signups
  FROM users
  WHERE created_at >= (SELECT start_d FROM bounds)
    AND created_at < (SELECT end_d FROM bounds) + INTERVAL '1 day'
  GROUP BY 1
)
SELECT c.d,
       COALESCE(d.signups, 0) AS signups,
       ROUND(AVG(COALESCE(d.signups,0)) OVER (ORDER BY c.d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7
FROM calendar c
LEFT JOIN daily d USING (d)
ORDER BY c.d;

3) Month-over-month growth (exclude partial month)

Goal: Monthly revenue, MoM % change, excluding the current partial month. Table: orders(order_id, paid_at TIMESTAMP, amount NUMERIC).

WITH monthly AS (
  SELECT DATE_TRUNC('month', paid_at)::date AS mth,
         SUM(amount) AS revenue
  FROM orders
  WHERE paid_at < DATE_TRUNC('month', CURRENT_DATE) -- exclude current partial month
  GROUP BY 1
)
SELECT mth,
       revenue,
       LAG(revenue) OVER (ORDER BY mth) AS prev_revenue,
       CASE WHEN LAG(revenue) OVER (ORDER BY mth) IS NULL OR LAG(revenue) OVER (ORDER BY mth) = 0
            THEN NULL
            ELSE ROUND( (revenue - LAG(revenue) OVER (ORDER BY mth)) / LAG(revenue) OVER (ORDER BY mth) * 100.0, 2)
       END AS mom_pct
FROM monthly
ORDER BY mth;

Exercises

Solve these in your SQL environment, then compare with the solutions. Each exercise mirrors the tasks below.

• EX1: Daily orders last 14 local days with zero-filled gaps (America/New_York).
• EX2: 7-day moving average of daily signups with gaps filled.
• EX3: Monthly revenue with MoM %, excluding the current partial month.

Common mistakes and how to self-check

• Grouping before time zone conversion. Fix: convert first, then date_trunc.
• Using BETWEEN with end-of-day timestamps. Fix: use < next_day or < next_month.
• Forgetting to fill gaps. Fix: calendar/generate_series LEFT JOIN.
• Using RANGE in windows with dates. Prefer ROWS for precise counts by row.
• Including partial months in MoM/YoY. Exclude current partial periods unless explicitly needed.
• DST surprises. Ensure conversion precedes truncation and validate counts near DST switches.

Practical projects

1. Build a 60-day daily KPI board: revenue, orders, new users. Include 7d MA lines. Export or materialize as a view.
2. Create a calendar table with columns: d, week_start, month_start, is_weekend, is_month_end, quarter. Use it to standardize all reports.
3. Implement a product cohort report: weekly cohorts by signup_date, with week N retention using date differences.

Who this is for

• BI Analysts building dashboards and weekly/monthly business reviews.
• Data Analysts preparing clean, gap-free time-series for stakeholders.
• Anyone turning raw event timestamps into trustworthy trends.

Prerequisites

• Comfort with SELECT, GROUP BY, and basic joins.
• Intro knowledge of window functions (LAG, AVG OVER).
• Awareness of your warehouse’s time zone functions.

Learning path

1. Bucket data: date_trunc by day/week/month.
2. Apply safe date filters (end exclusive).
3. Generate a calendar and left join to fill gaps.
4. Add rolling windows (MA7, MA28) and period-over-period (MoM/YoY).
5. Harden with time zone conversion and DST checks.

Next steps

• Automate your calendar table and reuse it across dashboards.
• Parameterize reports for flexible date ranges and time zones.
• Add quality checks that validate counts across raw vs. aggregated data.

Mini challenge

Write a query that returns the last 90 local days of revenue with columns: day, revenue, MA7, MA28, and WoW difference (day vs. same weekday last week). Ensure zero-filled days.

Quick Test

Take the quick test to check your understanding. Everyone can try it for free; only logged-in users will have their progress saved.