How do I fill missing days with zero in a DuckDB SQL task-completion report?

In DuckDB SQL, generate an inclusive daily date series with generate_series, then LEFT JOIN tasks to those dates using each task's completion date. Group by the generated day and use COUNT(t.task_id), not COUNT(*), to return zero for days without matching completions while preserving every reporting date.

Fill Missing Days with Zero in a SQL Task-Completion Report

What counts as a completed task in this DuckDB report?

This DuckDB report counts one task record on the reporting date derived from its non-null completed_at timestamp. Each task must have one row and a unique, non-null task_id; an unfinished task has a null completion timestamp.

A task created on January 1 and completed on January 3 counts on January 3. This report does not measure tasks created, tasks due, or tasks still open.

The reporting window is January 1 through January 3, 2026, inclusive. All completion timestamps must follow one agreed reporting timezone before date extraction. The sample stores UTC values; mixing local timestamps from different timezones would make daily counts inconsistent.

What sample task data demonstrates a missing completion day?

The sample task table contains two January 1 completions, no January 2 completions, and one January 3 completion within the reporting window. It also includes an out-of-range completion and an unfinished task to show which records do not count.

The completed_at values below are UTC by convention, stored in a plain DuckDB TIMESTAMP column.

task_id	task_title	completed_at
1001	Draft onboarding checklist	2026-01-01 09:15:00
1002	Review support handoff	2026-01-01 17:40:00
1003	Update project notes	2026-01-03 14:05:00
1004	Publish team schedule	2026-01-04 10:00:00
1005	Prepare meeting agenda	NULL

NULL means unfinished, not an unknown but countable completion. Neither the January 4 task nor the unfinished task needs removal from the source table: neither matches a reporting date.

How do you set up and run the DuckDB zero-filled daily report?

Create the sample tasks in a scratch DuckDB database, then generate reporting dates and left join matching completions to produce daily counts.

Open a DuckDB SQL connection. Use a scratch database. Do not replace an existing working table named tasks.
Choose the reporting timezone. The sample uses UTC. The session setting below also controls date extraction from TIMESTAMPTZ values, but does not convert plain TIMESTAMP values.
Create and populate the sample table. Run this setup block once in the scratch database. The primary key ensures unique, non-null task identifiers.

SET TimeZone = 'UTC';

CREATE TABLE tasks (
    task_id INTEGER PRIMARY KEY,
    task_title VARCHAR NOT NULL,
    completed_at TIMESTAMP
);

INSERT INTO tasks (task_id, task_title, completed_at)
VALUES
    (1001, 'Draft onboarding checklist', TIMESTAMP '2026-01-01 09:15:00'),
    (1002, 'Review support handoff', TIMESTAMP '2026-01-01 17:40:00'),
    (1003, 'Update project notes', TIMESTAMP '2026-01-03 14:05:00'),
    (1004, 'Publish team schedule', TIMESTAMP '2026-01-04 10:00:00'),
    (1005, 'Prepare meeting agenda', NULL);

Run the reporting query. This query includes both reporting endpoints.

WITH days AS (SELECT CAST(d AS DATE) AS day FROM generate_series(DATE '2026-01-01', DATE '2026-01-03', INTERVAL '1 day') AS s(d)) SELECT days.day, COUNT(t.task_id) AS completed_tasks FROM days LEFT JOIN tasks t ON CAST(t.completed_at AS DATE) = days.day GROUP BY days.day ORDER BY days.day;

Change the reporting dates for another period. Replace the two date literals inside generate_series. Keep INTERVAL '1 day', and ensure the start date is no later than the end date.

The reporting query only reads data. Generated dates exist within the query result; no placeholder tasks or timestamp changes are required.

What output should the January 1-3 task-completion report return?

The sample DuckDB report returns January 1 with two completions, January 2 with zero, and January 3 with one.

day	completed_tasks
2026-01-01	2
2026-01-02	0
2026-01-03	1

The counts total three, matching the completed task records inside the reporting window. January 4 is excluded, and the unfinished task cannot match a date.

The output should contain three rows even if the tasks table is empty; an empty table produces three zero counts. Zero means no matching completion records for that reporting day, not proof that nobody worked or that the source data is complete.

Why do generate_series, LEFT JOIN, and COUNT produce zero-filled daily task counts?

generate_series, LEFT JOIN, and COUNT produce zero-filled daily counts by defining reporting dates independently, preserving them during matching, and counting non-null task identifiers. First, the days common table expression calls generate_series with January 1 and January 3, 2026, and a one-day interval. This creates all three reporting dates without requiring stored calendar rows. The alias AS s(d) names the generated column, and CAST(d AS DATE) supplies a date-only key rather than a timestamp. Next, LEFT JOIN retains each generated day while matching tasks through CAST(t.completed_at AS DATE) = days.day. Comparing dates groups completion times into daily buckets rather than requiring identical timestamps. GROUP BY days.day collects those matches into one group per reporting date. Within each group, COUNT(t.task_id) counts task identifiers directly, so no separate COALESCE expression is needed. The unique, non-null identifiers support counting records without DISTINCT. Finally, ORDER BY days.day explicitly requests chronological output: January 1, 2, and 3, with counts of two, zero, and one.

Why do generate_series, LEFT JOIN, and COUNT preserve empty days?

DuckDB preserves empty reporting days by generating dates independently of task records, retaining those dates with a LEFT JOIN, and counting only non-null task identifiers.

Query component	Purpose
`generate_series`	Produces daily values, including the stop date when it aligns with the interval
`AS s(d)`	Names the generated column `d`
`CAST(d AS DATE)`	Creates a date-only join key
`LEFT JOIN`	Retains every generated day without requiring a task match
`COUNT(t.task_id)`	Returns zero when no non-null task identifiers match
`GROUP BY days.day`	Produces one count per date
`ORDER BY days.day`	Ensures chronological output

An unmatched day has null task columns, so COUNT(t.task_id) naturally returns zero without COALESCE. Grouping the task table alone cannot create missing dates because those dates have no source rows.

How do you fix missing dates or incorrect counts in this DuckDB query?

Fix incorrect DuckDB daily results by checking the count expression, task-filter placement, timestamp interpretation, and reporting-date order.

Empty days show one completion

Symptom: January 2 appears with completed_tasks equal to 1.

Cause: COUNT(*) counts the preserved left-join row even when its task columns are null.

Exact fix: Replace COUNT(*) with COUNT(t.task_id). If imported task identifiers can be null, repair them or count another field guaranteed non-null for every matching completion.

A project or team filter removes empty days

Symptom: Adding a task filter makes January 2 disappear.

Cause: A task-side WHERE condition rejects the null-extended rows that preserve empty days.

Exact fix: Put task-side filters in the join’s ON clause. For a source containing project_id, use:

LEFT JOIN tasks t
    ON CAST(t.completed_at AS DATE) = days.day
   AND t.project_id = 42

Do not add WHERE t.project_id = 42 afterward. This fragment requires a real project_id column; the sample table does not contain one.

Late-night completions appear on the wrong date

Symptom: A completion near midnight appears on a different date from the reporting convention.

Cause: The stored timestamp convention and reporting timezone disagree. Casting alone does not resolve an unspecified timezone.

Exact fix: For a TIMESTAMPTZ completion column and New York reporting days, replace the join’s completion-date expression with:

CAST(t.completed_at AT TIME ZONE 'America/New_York' AS DATE)

For UTC values stored in a plain TIMESTAMP column, interpret them as UTC before converting:

CAST(
    (t.completed_at AT TIME ZONE 'UTC')
    AT TIME ZONE 'America/New_York'
    AS DATE
)

Choose the expression matching the column type and storage convention. Apply the same reporting timezone to every completion.

The report returns no dates

Symptom: The query returns no rows instead of zero counts.

Cause: The start date is later than the end date with a positive interval.

Exact fix: Put the earlier date first in generate_series. An empty task table does not remove generated reporting dates.

When should you avoid this raw daily task-counting approach?

Avoid an unrestricted daily series over raw task rows when the report requires working days only, repeated-event cleanup, or a maintained daily aggregate.

Working-day reports: Use a calendar table with an explicit working-day flag for weekends and organization-specific holidays. Keep the left join, but generate reporting dates from that calendar.
Multiple events per task: Reopen events and repeated completion notifications can inflate counts. Derive the intended completion record per task first. Use distinct task counts only if counting each task once per reporting day matches the definition.
Large, repeatedly queried histories: Maintain a daily aggregate using the agreed reporting date and completion count. Left join reporting dates to that smaller table and apply COALESCE to its stored count: a missing aggregate row produces null rather than a counted zero.