sqlcmd.net validated sql reference

Command guide

advanced date MySQL MariaDB SQL Server PostgreSQL SQLite

Fill Gaps in a Time Series

Produce a row for every date in a range — even dates with no data — by generating a date spine and left-joining actual records onto it.

Created Last updated 5/5 supported engines validation-green 1 example 2 scenarios
Docker-validated Not currently validation-green

Example 1

Fill missing dates in a sparse daily revenue table with zero revenue

The daily_sales table has rows for Jan 1, 3, and 5 — Jan 2 and 4 are missing. The date spine generates all five dates in the range. The LEFT JOIN matches each spine date against the actual table: Jan 1, 3, and 5 match and carry their real revenue; Jan 2 and 4 match nothing, so revenue is NULL and COALESCE substitutes 0. The spine CTE is intentionally narrow — it only knows the start and end date, not the gaps — so it works regardless of how many dates are missing. To fill with NULL instead of 0, remove the COALESCE; to carry the last known value forward (last-observation-carried-forward), replace COALESCE(s.revenue, 0) with LAST_VALUE(s.revenue IGNORE NULLS) OVER (ORDER BY ds.sale_date) in PostgreSQL or a correlated subquery in other engines.

MySQL MariaDB
Engine-specific syntax
Source table data
Setup
CREATE TABLE daily_sales (sale_date VARCHAR(10), revenue INT);

INSERT INTO
  daily_sales
VALUES
  ('2024-01-01', 100),
  ('2024-01-03', 150),
  ('2024-01-05', 200);
Validated query
SQL
WITH RECURSIVE
  date_spine (sale_date) AS (
    SELECT
      '2024-01-01'
    UNION ALL
    SELECT
      DATE_FORMAT (DATE_ADD (sale_date, INTERVAL 1 DAY), '%Y-%m-%d')
    FROM
      date_spine
    WHERE
      sale_date < '2024-01-05'
  )
SELECT
  ds.sale_date,
  COALESCE(s.revenue, 0) AS revenue
FROM
  date_spine ds
  LEFT JOIN daily_sales s ON ds.sale_date = s.sale_date
ORDER BY
  ds.sale_date;
Expected result
sale_daterevenue
2024-01-01100
2024-01-020
2024-01-03150
2024-01-040
2024-01-05200
SQL Server
Engine-specific syntax
Source table data
Setup
CREATE TABLE daily_sales (sale_date VARCHAR(10), revenue INT);

INSERT INTO
  daily_sales
VALUES
  ('2024-01-01', 100),
  ('2024-01-03', 150),
  ('2024-01-05', 200);
Validated query
SQL
WITH
  date_spine AS (
    SELECT
      CONVERT(VARCHAR(10), CAST('2024-01-01' AS DATE), 120) AS sale_date
    UNION ALL
    SELECT
      CONVERT(
        VARCHAR(10),
        DATEADD (DAY, 1, CAST(sale_date AS DATE)),
        120
      )
    FROM
      date_spine
    WHERE
      sale_date < '2024-01-05'
  )
SELECT
  ds.sale_date,
  COALESCE(s.revenue, 0) AS revenue
FROM
  date_spine ds
  LEFT JOIN daily_sales s ON ds.sale_date = s.sale_date
ORDER BY
  ds.sale_date;
Expected result
sale_daterevenue
2024-01-01100
2024-01-020
2024-01-03150
2024-01-040
2024-01-05200
PostgreSQL
Engine-specific syntax
Source table data
Setup
CREATE TABLE daily_sales (sale_date VARCHAR(10), revenue INT);

INSERT INTO
  daily_sales
VALUES
  ('2024-01-01', 100),
  ('2024-01-03', 150),
  ('2024-01-05', 200);
Validated query
SQL
WITH date_spine AS (SELECT TO_CHAR(d, 'YYYY-MM-DD') AS sale_date FROM generate_series('2024-01-01'::date, '2024-01-05'::date, '1 day'::interval) d) SELECT ds.sale_date, COALESCE(s.revenue, 0) AS revenue FROM date_spine ds LEFT JOIN daily_sales s ON ds.sale_date = s.sale_date ORDER BY ds.sale_date;
Expected result
sale_daterevenue
2024-01-01100
2024-01-020
2024-01-03150
2024-01-040
2024-01-05200
SQLite
Engine-specific syntax
Source table data
Setup
CREATE TABLE daily_sales (sale_date VARCHAR(10), revenue INT);

INSERT INTO
  daily_sales
VALUES
  ('2024-01-01', 100),
  ('2024-01-03', 150),
  ('2024-01-05', 200);
Validated query
SQL
WITH RECURSIVE
  date_spine (sale_date) AS (
    SELECT
      '2024-01-01'
    UNION ALL
    SELECT
      DATE(sale_date, '+1 day')
    FROM
      date_spine
    WHERE
      sale_date < '2024-01-05'
  )
SELECT
  ds.sale_date,
  COALESCE(s.revenue, 0) AS revenue
FROM
  date_spine ds
  LEFT JOIN daily_sales s ON ds.sale_date = s.sale_date
ORDER BY
  ds.sale_date;
Expected result
sale_daterevenue
2024-01-01100
2024-01-020
2024-01-03150
2024-01-040
2024-01-05200

PostgreSQL uses generate_series for the date spine. MySQL and MariaDB use a recursive CTE with DATE_FORMAT/DATE_ADD. SQL Server uses DATEADD and CONVERT. SQLite uses the DATE() function. All produce the same output rows.

Useful when

Where this command helps.

  • filling missing dates in a daily revenue table so a dashboard shows zero bars instead of gaps
  • ensuring every date in a reporting period appears in a pivot before grouping by week

Explanation

What the command is doing.

Sparse time-series tables skip dates that have no events. Charting or aggregating such data directly produces misleading gaps. The fix is a two-step pattern: generate a complete sequence of dates (the 'spine'), then LEFT JOIN the actual data so every date has a row, with COALESCE substituting a zero or NULL where data is absent. PostgreSQL generates the spine in a single expression using generate_series. All other engines use a recursive CTE: the anchor row is the start date and each recursive step adds one day, stopping when the end date is reached. The recursive approach requires MySQL 8.0+, MariaDB 10.2+, SQL Server 2005+, or SQLite 3.35.0+. To scale to longer ranges without hitting the default SQL Server recursion limit of 100, add OPTION (MAXRECURSION 0) at the end of the query. For monthly or weekly granularity, change the interval from 1 day to 1 month or 7 days.

Tags

datetime-seriesgap-fillrecursionleft-joinanalyticsgenerate-series

Related commands

Keep moving through nearby tasks.

Official documentation