Postgresql query to count/group by day and display days with no data

sql

performance

best-practices

join

byNikita Barsukov·Mar 2, 2025

Use a LEFT JOIN with a generate_series(), to compile a complete calendar of dates. Align your data with this series to count entries per day, marking zero on empty days:

WITH dates AS (
  -- Here's where we generate our "calendar"
  SELECT generate_series('2021-01-01', '2021-01-10', '1 day')::date AS day
)
SELECT dates.day, COUNT(t.your_date_column) AS count
FROM dates
LEFT JOIN your_table t ON t.your_date_column::date = dates.day
-- GROUP BY day: because everyone deserves to be a part of a group, even days!
GROUP BY dates.day
-- Get things in order because everyone likes a sorted list!
ORDER BY dates.day;

This query generates a continuous sequence of dates, aligning your data against it to create a count for each day, even when there are no corresponding records.

Deep dive: Enhancing the query

Handing timestamps with precision using `date_trunc`

When working with timestamps, ensure accurate grouping by day using PostgreSQL's date_trunc function. It truncates down to the day level, avoiding hour or minute disruption:

SELECT dates.day, COUNT(t.your_timestamp_column) AS count
FROM dates
-- Date truncation to the rescue! Now every timestamp knows its day home.
LEFT JOIN your_table t ON date_trunc('day', t.your_timestamp_column) = dates.day
GROUP BY dates.day
ORDER BY dates.day;

Wrangle dates for readability with `to_char`

Use the to_char function to format dates. It's like a tuxedo for your date data, making them more presentable in 'YYYY-MM-DD' style:

SELECT to_char(dates.day, 'YYYY-MM-DD') AS formatted_date, COUNT(t.your_date_column) AS count
FROM dates
-- We don't judge dates by their "hour" content. Only the day matters here!
LEFT JOIN your_table t ON t.your_date_column::date = dates.day
GROUP BY dates.day
-- Column 1 gets its moment in the spotlight
ORDER BY 1;

Improving organization using Common Table Expressions (CTEs)

For complex queries, use CTEs to compartmentalize your logic. It's like defining your workspace, keeping the mess out of your final SQL statement:

WITH date_series AS (
  SELECT generate_series(MIN(your_date_column), MAX(your_date_column), '1 day')::date AS day FROM your_table
), aggregate_data AS (
  SELECT your_date_column::date, COUNT(*) AS count FROM your_table GROUP BY your_date_column
)
SELECT date_series.day, COALESCE(aggregate_data.count, 0)
FROM date_series
LEFT JOIN aggregate_data ON date_series.day = aggregate_data.your_date_column
-- Dates in single file, please!
ORDER BY date_series.day;

Optimizing for large date ranges

For massive date ranges, beware of performance concerns. It's wise to limit the date range to relevant periods for the user; no one cares about the daily weather from the Cretaceous period!

Best practices in SQL queries

Boosting performance with the magic of LEFT JOIN

Using a LEFT JOIN on date types vastly improves performance on large datasets. This works because the ::date conversion can happen just once rather than each time the ON clause is evaluated.

Prevention is better than cure

Avoid naked WHERE clauses like it's a pandemic. Always use explicit subquery conditions with LEFT JOINs, as it prevents issues where an unmatched row leads to a NULL condition, misrepresenting the final data.

Handling zero counts like a boss

Use COALESCE to turn NULL counts into zero. This makes your results consistent and clean, sort of like washing your hands of any NULL-based troubles.