Lorixen.
EN

Sales charts that skip quiet days: fill the gaps correctly

Short answer: A query that groups orders by day only returns days that had orders, so charts connect Friday straight to Monday and quiet days vanish. Generate the full list of days with PostgreSQL's generate_series, left join your data onto it, and turn missing values into zeros. Also decide which time zone defines "a day": group with date_trunc in your business's time zone rather than the server's, or late-evening orders land on the wrong date.

Why days go missing

A typical dashboard query:

SELECT date_trunc('day', created_at) AS day, count(*), sum(total_cents)
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY 1 ORDER BY 1;

It's correct, but it can only return groups that exist. A day with no orders produces no row. Many charting libraries then draw a line straight between the surrounding days, or space bars evenly, so a three-day outage or a closed weekend becomes invisible. Totals per week can also look odd if a week has fewer rows than expected.

Generate every day, then join

PostgreSQL's documentation describes generate_series for timestamps: it produces values from a start to a stop with a step interval. Use it to make a row for every day in the range, then attach your data:

WITH days AS (
  SELECT generate_series(
    date_trunc('day', now() - interval '29 days'),
    date_trunc('day', now()),
    interval '1 day'
  ) AS day
),
daily AS (
  SELECT date_trunc('day', created_at) AS day,
         count(*) AS orders, sum(total_cents) AS revenue
  FROM orders
  WHERE created_at >= now() - interval '30 days'
  GROUP BY 1
)
SELECT d.day, coalesce(o.orders, 0) AS orders, coalesce(o.revenue, 0) AS revenue
FROM days d LEFT JOIN daily o ON o.day = d.day
ORDER BY d.day;

Every day appears; quiet days show zero. The same pattern works hourly (interval '1 hour'), weekly or monthly.

Get the day boundaries right

"Which day did this order happen on?" depends on time zone. Store timestamps as timestamptz, which records the exact moment. Then group in the time zone your business thinks in.

PostgreSQL's date/time documentation explains that date_trunc on a timestamptz truncates in the session's current time zone by default, and accepts an optional time zone argument to use a different one. Its example truncates a UTC timestamp to the day in New York and gets the New York date, not the UTC one.

So, for a business in Chicago:

date_trunc('day', created_at, 'America/Chicago')

and generate the series in the same time zone. The documentation notes that the time-zone-aware form of generate_series takes a time zone argument and handles daylight saving adjustments in that zone, which keeps days aligned when clocks change:

generate_series(start, stop, interval '1 day', 'America/Chicago')

If your app server or database session runs in UTC, as many do, skipping this step moves evening orders on the US west coast into the next day's bar.

Filter with half-open ranges

Filter periods as "from the start of the first day, up to but not including the start of the day after the last", for example created_at >= start AND created_at < end. It avoids missing orders in the last second of a day and double-counting at boundaries.

Weeks and months

  • Weeks: date_trunc('week', ...) starts weeks on Monday. If your business reports Sunday-to-Saturday, shift the dates or generate your own week starts.
  • Months: generate month starts with interval '1 month' and truncate data with 'month'. Month lengths vary, so compare daily averages rather than raw totals if months are compared side by side.

Zero or "no data"?

A zero means "we were open and nothing happened". Sometimes the truth is "we weren't tracking" or "the integration was down". If you know about such gaps, mark them separately in the chart rather than showing a misleading zero.

Do it in the database or the app?

You can also fill gaps in application code, by building the list of dates and merging results. Doing it in SQL keeps every report consistent and means CSV exports, dashboards and emailed summaries show the same numbers. Other databases have equivalents, such as recursive queries or calendar tables, if they lack generate_series.

Test with awkward data

Before trusting the chart, check a few awkward cases against the raw orders: a day with no orders, an order placed a minute before midnight local time, the days either side of a daylight saving change, and the first and last day of the range. If each lands where a person at the shop counter would expect, the report is right. Keep these as automated tests if the dashboard matters to decisions.

Make it fast

For big tables, index created_at, and pre-aggregate into a daily summary table refreshed on a schedule, then apply the gap-filling join to the summary. Charts load instantly and still show every day.

A worked example

An illustrative Portland bike shop's dashboard showed online sales by day, but the chart skipped days when the site took no orders and seemed to show steady sales during a week when checkout had been broken for three days. Orders placed after 5pm also appeared on the next day, because the query grouped in UTC. The developer added a generate_series calendar with zero-filled values and grouped in the shop's time zone. The broken-checkout gap became obvious, and daily totals matched the till reports.

FAQ

Can I use a permanent calendar table instead?

Yes. A table of dates, with flags for holidays or opening days, is handy for many reports.

Why do totals differ from my payment provider's?

Different time zones or payout dates. Compare using the same boundaries.

Should averages include zero days?

Usually yes for "per day" averages; decide and label it.

Does this work for hourly charts?

Yes; use an hourly series and truncate to the hour.

If your dashboards or reports don't match what really happened, show us the query behind them and we'll help you make them accurate.

Sources

WhatsAppBook a call