Pilotbase result grid from a calendar table joined to orders: December 2025 day by day, with Christmas Day and New Year's Day shown as holidays

You group a year of orders by day and get 344 rows. The year had 365 days. The chart drawn from that result has no Christmas, no New Year's Day, no Thanksgiving and no quiet Sundays: the line just runs from the day before to the day after, as if nothing happened. The average per day comes out too high, because the days that sold nothing aren't in it.

The fix most people reach for is to make a list of dates inside the query each time. It works. A calendar table works better: one row per day, built once, with the things you keep recalculating already in columns (ISO week, weekday, month start, holiday, business-day number). Then a day with no sales is a LEFT JOIN away, and "ten business days after the 19th" is a lookup instead of a loop.

We built the same table on PostgreSQL 18.6, MySQL 8.4.13 and SQLite 3.50.4 and ran the same reports on each against two years of orders from a made-up small B2B shop: 21,389 orders from January 2024 to December 2025, busy on weekdays, a handful on Saturdays, about one on a Sunday, closed on New Year's Day, Thanksgiving and Christmas. Every number below comes from those runs, and all three engines agreed on every one.

What GROUP BY alone gives you

sql
SELECT ordered_at::date AS d, sum(amount)
FROM orders
WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01'
GROUP BY 1;
-- 344 rows

GROUP BY can only make groups from rows that exist. 21 days of 2025 had no orders at all: New Year's Day, Thanksgiving, Christmas Day and 18 Sundays. They aren't zero in this result; they're absent. Anything that reads the result as a series (a chart, a moving average, a "days since" calculation, a spreadsheet that assumes row 32 is February 1st) is now wrong without saying so.

The table

One row per date, and the columns you'd otherwise compute in every report:

text
column            example (2025-12-29)   what it's for
d                 2025-12-29             primary key, the join column
year, quarter,    2025, 4, 12, 29        filters and GROUP BY without date functions
  month, day
iso_year,         2026, 1                ISO weeks: this Monday is in week 1 of 2026
  iso_week
iso_dow, weekday  1, Mon                 1 = Monday ... 7 = Sunday
month_start       2025-12-01
week_start        2025-12-29             the Monday of the week
is_weekend        false
holiday_name      NULL                   from your own holidays table
is_business_day   true                   not a weekend, not a holiday
business_day_num  1542                   business days since the table starts, this day included

16 years, 2020 to 2035, is 5,844 rows. On PostgreSQL the table with its primary key takes 616 kB. Size is never the reason not to have one.

The holidays come from a small table you fill yourself, because nobody's list is the same: a US shop closes on Thanksgiving, a UK one on Boxing Day, a company on its own shutdown days. We loaded the US federal holidays for 2024 to 2026 (33 rows). For moving holidays (Easter, Thanksgiving, observed dates when a holiday falls on a weekend) type them in or load them from a library such as Python's holidays package; don't try to calculate them in SQL.

sql
CREATE TABLE holidays (d date PRIMARY KEY, name text NOT NULL);
INSERT INTO holidays VALUES
  ('2025-01-01', 'New Year''s Day'), ('2025-11-27', 'Thanksgiving'),
  ('2025-12-25', 'Christmas Day'), ('2026-01-01', 'New Year''s Day');
  -- ...one row per holiday you close for

Building it on PostgreSQL

generate_series makes the dates and a window function numbers the business days in the same statement:

sql
CREATE TABLE calendar (
  d                date PRIMARY KEY,
  year             smallint NOT NULL,
  quarter          smallint NOT NULL,
  month            smallint NOT NULL,
  day              smallint NOT NULL,
  iso_year         smallint NOT NULL,
  iso_week         smallint NOT NULL,
  iso_dow          smallint NOT NULL,
  weekday          text     NOT NULL,
  month_start      date     NOT NULL,
  week_start       date     NOT NULL,
  is_weekend       boolean  NOT NULL,
  holiday_name     text,
  is_business_day  boolean  NOT NULL,
  business_day_num int      NOT NULL
);

INSERT INTO calendar
SELECT d, extract(year FROM d), extract(quarter FROM d), extract(month FROM d), extract(day FROM d),
       extract(isoyear FROM d), extract(week FROM d), extract(isodow FROM d), to_char(d, 'Dy'),
       date_trunc('month', d)::date, date_trunc('week', d)::date,
       extract(isodow FROM d) >= 6, h.name,
       extract(isodow FROM d) < 6 AND h.d IS NULL,
       count(*) FILTER (WHERE extract(isodow FROM d) < 6 AND h.d IS NULL) OVER (ORDER BY d)
FROM generate_series(date '2020-01-01', date '2035-12-31', interval '1 day') AS g(ts)
CROSS JOIN LATERAL (SELECT g.ts::date AS d) x
LEFT JOIN holidays h USING (d);
-- INSERT 0 5844, 21.7 ms

extract(week ...) and date_trunc('week', ...) both follow ISO 8601: weeks start on Monday, and week 1 is the week with the year's first Thursday. That's why 2025-12-29 belongs to week 1 of 2026 and why 2021-01-03 belongs to week 53 of 2020. If your business counts weeks from Sunday, add a column for that too rather than bending the ISO one.

Building it on MySQL 8.4: the 1,000-row wall

MySQL has no generate_series, so the dates come from a recursive CTE. Run that for more than about two and three-quarter years and it stops:

Pilotbase on MySQL 8.4: a recursive CTE counting days from 2025-01-01 to 2027-12-31 fails with error 3636, Recursive query aborted after 1001 iterations, try increasing @@cte_max_recursion_depth

cte_max_recursion_depth defaults to 1000. It's a guard against runaway recursion, and it's per session, so raise it for the build and leave the server setting alone:

sql
SET SESSION cte_max_recursion_depth = 10000;

INSERT INTO calendar
WITH RECURSIVE days AS (
  SELECT DATE '2020-01-01' AS d
  UNION ALL SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2035-12-31')
SELECT d, YEAR(d), QUARTER(d), MONTH(d), DAY(d),
       YEARWEEK(d, 3) DIV 100, WEEK(d, 3), WEEKDAY(d) + 1, DATE_FORMAT(d, '%a'),
       d - INTERVAL (DAY(d) - 1) DAY, d - INTERVAL WEEKDAY(d) DAY,
       WEEKDAY(d) >= 5, h.name,
       WEEKDAY(d) < 5 AND h.d IS NULL,
       SUM(WEEKDAY(d) < 5 AND h.d IS NULL) OVER (ORDER BY d)
FROM days LEFT JOIN holidays h USING (d);
-- 5844 rows

Mode 3 is the ISO one in both WEEK(d, 3) and YEARWEEK(d, 3). Take the ISO year from YEARWEEK, not from YEAR(d): for 2027-01-01 YEAR says 2027, while the ISO year is 2026 (week 53). The column types are the same as on PostgreSQL, with tinyint for the small ones and varchar(60) for the holiday name.

Building it on SQLite: check your version first

sql
INSERT INTO calendar
WITH RECURSIVE days(d) AS (
  SELECT '2020-01-01'
  UNION ALL SELECT date(d, '+1 day') FROM days WHERE d < '2035-12-31')
SELECT d,
       CAST(strftime('%Y', d) AS INTEGER),
       (CAST(strftime('%m', d) AS INTEGER) + 2) / 3,
       CAST(strftime('%m', d) AS INTEGER),
       CAST(strftime('%d', d) AS INTEGER),
       CAST(strftime('%G', d) AS INTEGER),   -- ISO year
       CAST(strftime('%V', d) AS INTEGER),   -- ISO week
       CAST(strftime('%u', d) AS INTEGER),   -- 1 = Monday
       substr('MonTueWedThuFriSatSun', 3 * CAST(strftime('%u', d) AS INTEGER) - 2, 3),
       date(d, 'start of month'),
       date(d, '-' || (CAST(strftime('%u', d) AS INTEGER) - 1) || ' days'),
       CAST(strftime('%u', d) AS INTEGER) >= 6,
       h.name,
       CAST(strftime('%u', d) AS INTEGER) < 6 AND h.d IS NULL,
       SUM(CAST(strftime('%u', d) AS INTEGER) < 6 AND h.d IS NULL) OVER (ORDER BY d)
FROM days LEFT JOIN holidays h USING (d);

SQLite has no recursion limit to raise, but its date functions have grown recently. %u (ISO weekday) arrived in 3.44.0 (November 2023), and %G and %V (ISO year and week) in 3.46.0 (May 2024). On an older SQLite, strftime returns NULL for those letters and the NOT NULL columns make the INSERT fail, which is the better outcome. The SQLite that a program uses is the library bundled with it, not whatever the command line says: the same database file opened through the driver in our Docker test container reported 3.40.1. Run SELECT sqlite_version(); through the program that will build the table.

For older versions there's a trick that needs nothing new: the Thursday of a date's Monday-to-Sunday week decides its ISO year and week.

sql
CAST(strftime('%Y', date(d, '-3 days', 'weekday 4')) AS INTEGER)              AS iso_year,
(CAST(strftime('%j', date(d, '-3 days', 'weekday 4')) AS INTEGER) - 1) / 7 + 1 AS iso_week

date(d, '-3 days', 'weekday 4') steps back three days and then forward to the next Thursday, which lands on the Thursday of d's week. We compared it with %G/%V over all 5,844 days: zero differences.

The report that doesn't skip days

Start from the calendar and LEFT JOIN the orders to it. Every date in the range comes back, and a date with no orders gets NULLs from the orders side, which count() turns into 0 and COALESCE turns into 0 for the sum:

sql
SELECT c.d, c.weekday, c.holiday_name,
       count(o.id)                AS orders,
       coalesce(sum(o.amount), 0) AS revenue
FROM calendar c
LEFT JOIN orders o
       ON o.ordered_at >= c.d AND o.ordered_at < c.d + 1      -- MySQL: c.d + INTERVAL 1 DAY
WHERE c.d BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY c.d, c.weekday, c.holiday_name
ORDER BY c.d;
-- 365 rows, 21 with orders = 0
Pilotbase on PostgreSQL 18: the calendar joined to orders for 20 December 2025 to 4 January 2026, 16 rows, with Christmas Day showing 0 orders and false for is_business_day

Three details in that query matter more than they look.

count(o.id), not count(*). count() counts rows, and the LEFT JOIN gives Christmas Day one row with NULLs in it, so count() would report 1 order on a day with none.

The join is a range on the timestamp, not date(o.ordered_at) = c.d. Both give the same answer; the range can use an index on ordered_at, the function can't (unless you build an expression index for it).

Conditions on the orders go in the ON clause, not in WHERE. We added AND o.amount > 0 to the WHERE and got 344 rows back: the empty days have amount NULL, NULL > 0 isn't true, and the LEFT JOIN quietly becomes an inner join. Moved into ON, the same condition keeps all 365. sqlInspector flags this one when you paste a query ("a LEFT JOIN undone by WHERE").

Business days between two dates

business_day_num is a running count of business days. Weekends and holidays repeat the previous day's number instead of getting one of their own, so the number of business days after date a, up to and including date b, is a subtraction:

sql
SELECT b.business_day_num - a.business_day_num AS business_days,
       b.d - a.d                                 AS calendar_days
FROM calendar a, calendar b
WHERE a.d = '2025-11-20' AND b.d = '2025-12-31';

 business_days | calendar_days
---------------+---------------
            27 |            41

41 calendar days, 27 business days: 12 weekend days and two holidays (Thanksgiving and Christmas) fall in between. The same column runs the other way for due dates. Ten business days after Friday 19 December 2025:

sql
SELECT min(d) AS due FROM calendar
WHERE is_business_day
  AND business_day_num = (SELECT business_day_num FROM calendar WHERE d = '2025-12-19') + 10;
-- 2026-01-06

Count it by hand: 22, 23, 24, 26, 29, 30, 31 December, then 2, 5 and 6 January, skipping Christmas and New Year's Day. Getting that right with date arithmetic means a loop or a function; here it's one indexed lookup, and it's the same SQL on all three engines (MySQL needs DATEDIFF(b.d, a.d) for the calendar days, SQLite julianday(b.d) - julianday(a.d)).

Same weekday last year

"How did this Monday do against last year?" Last year's same date is usually the wrong day to compare with, because 2025-03-03 was a Monday and 2024-03-03 was a Sunday. For a business whose Mondays and Sundays look nothing alike, that comparison is noise:

text
            this year    same date last year     364 days back (same weekday)
Mon 03-03     2,867.74   Sun 2024-03-03     98.05   Mon 2024-03-04   2,686.16
Tue 03-04     4,616.18   Mon 2024-03-04  2,686.16   Tue 2024-03-05   3,220.73
Sat 03-08       250.21   Fri 2024-03-08  2,356.03   Sat 2024-03-09     615.25
Sun 03-09         0.00   Sat 2024-03-09    615.25   Sun 2024-03-10      62.07

Day by day, the same-date column says Monday grew 29-fold and Saturday fell 89%. Both are artefacts. 364 days is exactly 52 weeks, so t.d - 364 always lands on the same weekday, and the daily comparison becomes meaningful. Over a whole week the two methods come out close (15,060.01 vs 15,095.99 against this week's 18,469.14), which is why the error is easy to miss on weekly reports and painful on daily ones.

sql
WITH daily AS (
  SELECT c.d, coalesce(sum(o.amount), 0) AS revenue
  FROM calendar c
  LEFT JOIN orders o ON o.ordered_at >= c.d AND o.ordered_at < c.d + 1
  WHERE c.d BETWEEN '2024-01-01' AND '2025-12-31'
  GROUP BY c.d)
SELECT t.d, t.revenue, ly.revenue AS same_weekday_last_year
FROM daily t
LEFT JOIN daily ly ON ly.d = t.d - 364
WHERE t.d BETWEEN '2025-03-03' AND '2025-03-09'
ORDER BY t.d;

The calendar is what makes this safe: because both years have every day, a zero day last year compares as 0, instead of the join finding nothing. 364 days drifts one day against the date each year (two after 29 February), so after five or six years "the same weekday" is a week earlier in the season. If that matters (retail around holidays), join on iso_year - 1, the same iso_week and the same iso_dow instead, and decide what week 53 compares with: 2020 and 2026 have one, the years around them don't.

Weeks and averages that count every day

ISO weeks cross the year boundary, and the calendar knows where:

sql
SELECT c.iso_year, c.iso_week, min(c.d) AS week_start, count(DISTINCT c.d) AS days,
       count(o.id) AS orders, coalesce(sum(o.amount), 0) AS revenue
FROM calendar c
LEFT JOIN orders o ON o.ordered_at >= c.d AND o.ordered_at < c.d + 1
WHERE c.d BETWEEN '2024-12-23' AND '2025-01-12'
GROUP BY 1, 2 ORDER BY 1, 2;

 iso_year | iso_week | week_start | days | orders | revenue
----------+----------+------------+------+--------+----------
     2024 |       52 | 2024-12-23 |    7 |    137 | 11476.63
     2025 |        1 | 2024-12-30 |    7 |    183 | 14727.51
     2025 |        2 | 2025-01-06 |    7 |    202 | 15550.18

Week 1 of 2025 starts on 30 December 2024. Group by YEAR(d) and MySQL's default WEEK(d) (mode 0, weeks from Sunday) instead, and 29 to 31 December come out as week 52 of 2024 while 1 to 4 January 2025 come out as week 0 of 2025: one real week in two groups, each looking like a bad week. The days column is there as a check: every full week should say 7.

Averages are where the missing days cost the most. For the fourth quarter of 2025:

text
revenue per business day (62 days)            3,634.73
revenue per calendar day (92 days)            2,449.49
average of the days that had sales (86 days)  2,620.39

The third number is what you get by averaging the GROUP BY result from the start of this article. It's neither of the two numbers anyone actually wants: it's not per calendar day, because six days with no orders (Thanksgiving, Christmas and four Sundays) are missing, and it's not per business day, because weekends with a couple of orders are in it. With the calendar you choose the denominator on purpose: sum(revenue) / count(*) FILTER (WHERE is_business_day) on PostgreSQL, SUM(revenue) / SUM(is_business_day) on MySQL and SQLite.

Is it faster than generating dates each time?

Not by enough to matter. We ran the 365-day report 30 times on each engine (after three warm-up runs) and took the median, measured from the client:

text
engine            calendar table   dates generated in the query
PostgreSQL 18.6        4.14 ms      5.64 ms  (generate_series)
MySQL 8.4.13          13.39 ms     15.30 ms  (recursive CTE)
SQLite 3.50.4          2.66 ms      4.31 ms  (recursive CTE)

Same rows, same revenue (891,620.09) every time. A millisecond or two is not a reason to add a table. The reasons are the columns: holidays, business-day numbers and ISO weeks are defined once, the same in every report, instead of re-derived (slightly differently) in each one. And on MySQL, every report that generates its own dates for more than 1,000 days has to remember the SET SESSION first.

Keeping it right

Two jobs, once a year or when the holiday list changes. Extend the range before you reach the end of it: a report for a date the calendar doesn't have silently returns fewer rows, which is the exact problem the table was built to fix. A SELECT max(d) FROM calendar in whatever monitoring you have is cheap insurance. And when a holiday is added or moved, business_day_num changes for every later day, so rebuild those columns rather than patching one row:

sql
UPDATE calendar c
SET holiday_name    = (SELECT name FROM holidays h WHERE h.d = c.d),
    is_business_day = NOT c.is_weekend AND NOT EXISTS (SELECT 1 FROM holidays h WHERE h.d = c.d);

UPDATE calendar c
SET business_day_num = x.n
FROM (SELECT d, count(*) FILTER (WHERE is_business_day) OVER (ORDER BY d) AS n FROM calendar) x
WHERE x.d = c.d;

If other teams store business-day numbers anywhere (an SLA table, a cached due date), a holiday added later changes the answer for the past too. Usually that's right. Decide before someone asks why a ticket's due date moved.

Getting the report out

We ran each version of the report in Pilotbase against all three databases, next to each other, which made it easy to see that they agreed. The CSV button above the result grid saves the 365 rows as they are, zero days included. From there, pivotTable turns the daily file into a weekday-by-month grid in the browser (and its "Show SQL" writes the same pivot for your database), and our article on pivot tables in SQL covers doing that step in the database instead.

If what you're after is the days that are missing from the data rather than a report that includes them, that's a different job: our gaps and islands article finds missing dates, streaks and sessions with window functions, no calendar table needed.

Run the calendar build and the report on PostgreSQL, MySQL and SQLite side by side, and export the result as CSV. Pilotbase is free for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Paste your report query and see whether a WHERE clause has turned your LEFT JOIN back into an inner join, before the missing days do.

Open sqlInspector

Related: Gaps and islands in SQL: streaks, missing days and sessions with window functions · Pivot tables in SQL: crosstab, FILTER and CASE

Sources: PostgreSQL 18 documentation, Date/Time Functions (extract isoyear, week, isodow; date_trunc) and Set Returning Functions (generate_series); MySQL 8.4 Reference Manual, WITH (Common Table Expressions) and the cte_max_recursion_depth system variable, and Date and Time Functions (WEEK modes, YEARWEEK); SQLite release history (strftime %u in 3.44.0, 2023-11-01; %G and %V in 3.46.0, 2024-05-23) and Date And Time Functions. Test data, SQL and outputs are our own, run on 2026-10-23.