Use case: Timeseries data that’s stored densely, but needs to be reported sparsely, i.e. with explicit zeros for missing periods. (RIGHT JOIN for the win!)

sparse_timeseries.sql

-- PostgreSQL 14

SELECT 
	days AS observation_date, 
	-- Explicitly coalescing missing values to zero.
	-- This logic will be use case-dependent.
	coalesce(metrics.metric, 0) AS metric
FROM 
	-- Replace this with the dense metrics table
	(values ('2023-01-01'::date, 111111), ('2023-06-15'::date, 222222)) AS metrics (observation_date, metric)
RIGHT JOIN
	generate_series('2023-01-01'::timestamp, now()::timestamp, '1 day'::interval) AS days
	ON metrics.observation_date = days
ORDER BY
	days ASC
;