Time-Series Charts in SQL: Bucketing, Gap-Filling, and Time Zones That Don't Lie

Almost every chart in a dashboard is a time series. Signups per day, revenue per week, active users per hour, API calls per minute. They all boil down to the same shape: group rows into time buckets, count or sum something, plot the result.

It sounds trivial. Then you ship it, and a customer emails: "Why does my chart show zero signups on Tuesday? We definitely had signups." Or worse, they don't email — they just quietly stop trusting the numbers, because Tuesday's bar is missing entirely and the line jumps straight from Monday to Wednesday as if nothing happened.

Time-series charts have three failure modes that bite almost everyone building customer-facing analytics: buckets that don't line up, gaps that get silently skipped, and time zones that shift the whole chart by a day. This post walks through each one with plain SQL you can drop into a dashboard query, using a realistic SaaS schema. By the end you'll be able to produce a chart series that's dense, correctly bucketed, and shows each customer their own local days.

The setup

Let's assume a multi-tenant SaaS app with a familiar events table:

CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  workspace_id BIGINT NOT NULL,   -- the tenant
  user_id     BIGINT NOT NULL,
  event_type  TEXT NOT NULL,      -- 'signup', 'login', 'purchase', ...
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Note created_at is TIMESTAMPTZ (timestamp with time zone). Store everything in UTC and keep this type everywhere — it's the single most important decision for getting time-series charts right later. If you're storing naive TIMESTAMP columns, fix that first; the rest of this post assumes you know what instant each row actually happened at.

Step 1: Bucketing with date_trunc

The workhorse for grouping timestamps into intervals is date_trunc. It chops a timestamp down to the start of a given unit — hour, day, week, month:

SELECT
  date_trunc('day', created_at) AS bucket,
  count(*) AS signups
FROM events
WHERE workspace_id = 42
  AND event_type = 'signup'
  AND created_at >= now() - interval '7 days'
GROUP BY 1
ORDER BY 1;

That gives you one row per day that had at least one signup:

bucket	signups
2026-07-25 00:00:00+00	12
2026-07-26 00:00:00+00	8
2026-07-28 00:00:00+00	5

Look closely. July 27 is missing. Not because of a bug — there were simply no signups that day, so GROUP BY produced no row. Your charting library receives three points and draws a line straight from the 26th to the 28th. The dip to zero on the 27th vanishes, and the trend looks smoother and healthier than it actually was.

date_trunc handles the "line them up" problem. It does nothing about the "missing days" problem. For that you need a spine.

Step 2: Gap-filling with generate_series

The fix is to generate a complete, dense list of buckets and LEFT JOIN your aggregated data onto it. Postgres ships generate_series for exactly this:

SELECT
  spine.bucket,
  COALESCE(e.signups, 0) AS signups
FROM generate_series(
       date_trunc('day', now() - interval '6 days'),
       date_trunc('day', now()),
       interval '1 day'
     ) AS spine(bucket)
LEFT JOIN (
  SELECT date_trunc('day', created_at) AS bucket,
         count(*) AS signups
  FROM events
  WHERE workspace_id = 42
    AND event_type = 'signup'
    AND created_at >= now() - interval '7 days'
  GROUP BY 1
) e ON e.bucket = spine.bucket
ORDER BY spine.bucket;

Now every day in the range shows up, and the empty ones report zero:

bucket	signups
2026-07-25	12
2026-07-26	8
2026-07-27	0
2026-07-28	5

generate_series builds the calendar, the LEFT JOIN attaches whatever data exists, and COALESCE turns the NULL from days-with-no-match into a real zero. This pattern is portable, works on plain PostgreSQL with no extensions, and is the single technique that separates an honest line chart from a misleading one.

If you're on TimescaleDB you get time_bucket() and time_bucket_gapfill() which do bucketing and gap-filling in one call — and let you bucket by arbitrary intervals like 5 minutes or 4 hours, which date_trunc can't. But you don't need an extension to get correct charts. The generate_series + LEFT JOIN spine works everywhere.

Step 3: Time zones, or "whose Tuesday is it?"

Here's the one that silently corrupts numbers for months before anyone notices. date_trunc('day', created_at) truncates in UTC. If your customer is in Los Angeles, an event at 6pm Monday their time happened at 1am Tuesday UTC — so it lands in Tuesday's bucket, not Monday's. Every evening event gets shoved into the next day. Daily totals are wrong, and "which day is our busiest" points at the wrong day.

The fix is to convert to the customer's zone before truncating, using AT TIME ZONE:

SELECT
  date_trunc('day', created_at AT TIME ZONE 'America/Los_Angeles') AS bucket,
  count(*) AS signups
FROM events
WHERE workspace_id = 42
GROUP BY 1
ORDER BY 1;

created_at AT TIME ZONE 'America/Los_Angeles' converts the UTC instant into local wall-clock time, so date_trunc('day', ...) now means "midnight-to-midnight in LA." That's the day boundary the customer actually experiences.

In a multi-tenant app, the zone isn't a constant — it's a property of the tenant. Join it in:

SELECT
  date_trunc('day', e.created_at AT TIME ZONE w.timezone) AS bucket,
  count(*) AS signups
FROM events e
JOIN workspaces w ON w.id = e.workspace_id
WHERE e.workspace_id = 42
GROUP BY 1
ORDER BY 1;

Store an IANA zone name like America/Los_Angeles per workspace (not a fixed -08:00 offset — offsets don't know about daylight saving). Then the same query renders correct local days for every customer, whether they're in California, Berlin, or Sydney. Combine this with the gap-filling spine from Step 2, generating that spine in the customer's zone too, and the chart is both dense and locally correct.

A complete, chart-ready query

Putting all three together — bucketed, gap-filled, and time-zone-aware for a single tenant:

WITH bounds AS (
  SELECT w.timezone AS tz
  FROM workspaces w WHERE w.id = 42
),
spine AS (
  SELECT generate_series(
    date_trunc('day', (now() AT TIME ZONE (SELECT tz FROM bounds)) - interval '29 days'),
    date_trunc('day', (now() AT TIME ZONE (SELECT tz FROM bounds))),
    interval '1 day'
  ) AS bucket
),
daily AS (
  SELECT date_trunc('day', e.created_at AT TIME ZONE (SELECT tz FROM bounds)) AS bucket,
         count(*) FILTER (WHERE e.event_type = 'signup')   AS signups,
         count(*) FILTER (WHERE e.event_type = 'purchase') AS purchases
  FROM events e
  WHERE e.workspace_id = 42
    AND e.created_at >= now() - interval '31 days'
  GROUP BY 1
)
SELECT s.bucket::date AS day,
       COALESCE(d.signups, 0)   AS signups,
       COALESCE(d.purchases, 0) AS purchases
FROM spine s
LEFT JOIN daily d ON d.bucket = s.bucket
ORDER BY s.bucket;

That's a 30-day, two-metric series with no gaps, bucketed on the customer's local calendar — exactly what a line or bar chart needs, ready to hand straight to the frontend.

Common mistakes and gotchas

Filtering by local time instead of UTC. Keep your WHERE created_at >= ... bound in UTC (that's what the index is on). Convert to local time only inside date_trunc for the grouping. Wrapping the column in AT TIME ZONE in the WHERE clause can also throw away your index and force a full scan.

Fixed offsets instead of IANA zones. AT TIME ZONE '-08:00' breaks twice a year when daylight saving flips. Always store and use named zones like Europe/London so the database applies DST for you.

Forgetting the range bounds match the spine. If your generate_series covers 30 days but the aggregate subquery only pulls 7, you'll get 23 days of zeros that look real. Make the spine and the data query cover the same window.

Gap-filling a metric where zero is wrong. COALESCE(..., 0) is right for counts and sums. But for a running balance or a gauge like "active subscriptions," a missing bucket should carry the last known value forward (last-observation-carried-forward), not drop to zero. Pick the fill strategy that matches what the metric means.

Bucketing by week without agreeing on the start day. date_trunc('week', ...) starts weeks on Monday in Postgres. If your product defines the week as starting Sunday, your chart and your customer's mental model disagree by a day. Decide, document it, and be consistent.

Not pre-aggregating for big tenants. Scanning millions of rows on every dashboard load is slow. Once the raw query is correct, roll it into a daily summary table or materialized view keyed by (workspace_id, day), and query that for the chart.

Key takeaways

Bucketing, gap-filling, and time zones are the three things that make time-series charts either trustworthy or quietly wrong. date_trunc lines rows up into intervals but leaves holes; a generate_series spine plus a LEFT JOIN and COALESCE fills those holes so a zero day reads as zero instead of vanishing; and AT TIME ZONE with a per-tenant IANA zone makes sure each customer sees their own days, not UTC's. Get all three right and the chart matches what the customer actually experienced — which is the whole point of putting analytics in front of them.

If you're wiring these queries into a real dashboard, tools like Draxlr let you turn SQL like this into embeddable charts without hand-rolling the frontend — handy once the query is correct and you just want it on a screen.

How do you handle gap-filling and per-customer time zones in your dashboards — spine joins, a time-series extension, or pre-aggregated rollups? Drop your approach in the comments; I'm always curious what patterns people land on at scale.

---

Sources: Tiger Data — Mind the Gap: SQL Functions for Time-Series Analysis, Tiger Data — Simplified time-series analytics: time_bucket(), PostgreSQL Documentation — Date/Time Functions and Operators, Neon Docs — Postgres date_trunc() function.