Date Handling For Time Series

Why this matters

As a Data Visualization Engineer, almost every dashboard you build has time on the x-axis. Getting dates right is the difference between trustworthy insights and confusing charts. You will routinely:

• Aggregate metrics by day/week/month (e.g., Daily Active Users, weekly conversions).
• Handle time zones correctly so numbers match what business users see.
• Produce rolling averages and period-over-period comparisons.
• Fill in missing dates to prevent broken lines in charts.

Who this is for

• Beginners to intermediate analysts and visualization engineers who need reliable time-based metrics in SQL-driven dashboards.
• Anyone preparing to hand off clean time series to BI tools (e.g., Looker, Power BI, Tableau, Metabase).

Prerequisites

• Basic SELECT, WHERE, GROUP BY, ORDER BY.
• Familiarity with numeric aggregates (COUNT, SUM) and simple JOINs.
• Optional: basic window functions (ROW_NUMBER, moving averages).

Concept explained simply

Time series is just your metric measured repeatedly over time (per day, week, month, etc.). The essentials are:

• Choose the right time grain (day/week/month).
• Normalize timestamps (UTC vs local time).
• Truncate or extract parts of dates for grouping.
• Fill gaps so charts don’t break.
• Use windows for rolling stats (e.g., 7-day average).

Mental model

Think of your timeline as evenly spaced boxes (days). You:

1. Clip each event into the correct box (time zone first, then truncate).
2. Sum everything inside each box.
3. If a box is empty, still keep the box with zero so charts are continuous.

Worked examples

Example 1 — Daily revenue (correct time grain)

-- Bucket orders by calendar day (UTC example)
SELECT
  CAST(order_ts AS DATE) AS order_date,
  SUM(amount) AS revenue
FROM orders
GROUP BY CAST(order_ts AS DATE)
ORDER BY order_date;

Example 2 — Convert UTC to local (beware DST)

-- Preferred if your DB supports time zones (handles DST):
SELECT
  CAST((order_ts_utc AT TIME ZONE 'America/New_York') AS DATE) AS local_date,
  SUM(amount) AS revenue
FROM orders
GROUP BY CAST((order_ts_utc AT TIME ZONE 'America/New_York') AS DATE)
ORDER BY local_date;

-- If no time zone support, and you must approximate with a fixed offset:
-- Note: This does NOT handle daylight saving transitions.
SELECT
  CAST(order_ts_utc - INTERVAL '5 hours' AS DATE) AS local_date,
  SUM(amount) AS revenue
FROM orders
GROUP BY CAST(order_ts_utc - INTERVAL '5 hours' AS DATE)
ORDER BY local_date;

Example 3 — Fill gaps with a date spine

-- Generate a continuous series of dates and left join metrics
WITH RECURSIVE spine AS (
  SELECT DATE '2024-04-01' AS d
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM spine WHERE d < DATE '2024-04-07'
)
SELECT
  s.d AS dt,
  COALESCE(v.visits, 0) AS visits
FROM spine s
LEFT JOIN daily_visits v ON v.dt = s.d
ORDER BY dt;

Example 4 — 7-day rolling average

SELECT
  dt,
  visits,
  AVG(visits) OVER (
    ORDER BY dt
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS visits_ma7
FROM daily_visits
ORDER BY dt;

Step-by-step: getting robust time series

1. Normalize time. Decide on UTC or a specific business timezone and convert first.
2. Choose a grain. day/week/month; align with how stakeholders read results.
3. Bucket/truncate. Use DATE_TRUNC or CAST to derive the period key.
4. Aggregate. SUM, COUNT, AVG; keep one row per time bucket.
5. Fill gaps. Join to a date spine and COALESCE to zero.
6. Compute rolling metrics. Windows for MAs and PoP comparisons.
7. Validate. Spot-check totals and key dates; compare UTC vs local if needed.

Exercises

These mirror the graded exercises below. Do them here first, then check the solutions.

Exercise 1 — Daily revenue in local time (UTC-5)

Table: orders(order_id INT, user_id TEXT, order_ts_utc TIMESTAMP, amount NUMERIC)

order_id | user_id | order_ts_utc          | amount
---------+---------+------------------------+--------
1        | u1      | 2024-03-01 01:30:00+00 | 30.00
2        | u2      | 2024-03-01 23:20:00+00 | 20.00
3        | u1      | 2024-03-02 02:10:00+00 | 10.00
4        | u3      | 2024-03-02 12:00:00+00 | 40.00
5        | u2      | 2024-03-03 03:05:00+00 | 50.00

Task: Assume local timezone is UTC-5 (fixed offset). Compute daily revenue by local calendar date.

SELECT
  CAST(order_ts_utc - INTERVAL '5 hours' AS DATE) AS local_date,
  SUM(amount) AS revenue
FROM orders
GROUP BY CAST(order_ts_utc - INTERVAL '5 hours' AS DATE)
ORDER BY local_date;
-- Expected:
-- 2024-02-29 | 30.00
-- 2024-03-01 | 30.00
-- 2024-03-02 | 90.00

Exercise 2 — Fill gaps and 3-day moving average

Table: daily_visits(dt DATE, visits INT)

dt         | visits
-----------+-------
2024-04-01 | 100
2024-04-02 | 120
2024-04-04 | 80
2024-04-05 | 0
2024-04-07 | 140

Task: Generate a date spine from 2024-04-01 to 2024-04-07. Left join to daily_visits, fill missing with 0, and compute a 3-day moving average including the current day.

WITH RECURSIVE spine AS (
  SELECT DATE '2024-04-01' AS dt
  UNION ALL
  SELECT dt + INTERVAL '1 day' FROM spine WHERE dt < DATE '2024-04-07'
), filled AS (
  SELECT s.dt, COALESCE(v.visits, 0) AS visits
  FROM spine s
  LEFT JOIN daily_visits v ON v.dt = s.dt
)
SELECT
  dt,
  visits,
  ROUND(AVG(visits) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma3
FROM filled
ORDER BY dt;
-- Expected:
-- 2024-04-01 | 100 | 100.00
-- 2024-04-02 | 120 | 110.00
-- 2024-04-03 |   0 | 73.33
-- 2024-04-04 |  80 | 66.67
-- 2024-04-05 |   0 | 26.67
-- 2024-04-06 |   0 | 26.67
-- 2024-04-07 | 140 | 46.67

Common mistakes and self-check

• Using raw timestamps as join keys for daily data. Fix: derive a DATE per record, then join/aggregate by DATE.
• Forgetting time zone conversion before bucketing. Fix: convert first, then DATE_TRUNC/CAST.
• Broken charts due to missing dates. Fix: join to a date spine and COALESCE zeros.
• Wrong rolling window frame. Fix: for N-day moving average, use ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW on daily data.
• Comparing mismatched periods (e.g., partial current month vs full prior month). Fix: filter both periods to the same number of days or use complete months.

Practical projects

• Build a daily orders dashboard: revenue, orders, AOV, 7-day moving average, and week-over-week lines.
• Create a timezone-aware active users chart for one region and compare to UTC.
• Generate a robust date spine table (calendar) with flags: weekend, month_start, quarter_start, fiscal_week.

Learning path

• Start here: bucket dates and aggregate.
• Add: timezone handling and continuous date spines.
• Level up: rolling metrics, period-over-period, cohort month grouping.
• Then: performance tuning and reusable calendar tables in your warehouse.

Next steps

• Finish the exercises below and take the quick test.
• Refactor your queries into views or models that standardize date handling for your team.
• Share a chart with both UTC and local views to validate with stakeholders.

Mini challenge

You have events in UTC and need a weekly dashboard starting on Monday for the London office. Write the minimal SQL sketch describing:

• How you will convert to Europe/London time.
• How you will derive the Monday-based week bucket.
• How you will fill missing weeks.

Note: The quick test is available to everyone; only logged-in users will have their progress saved.