How to query time series with gaps between a given date range in PostgreSQL?
WITH cte AS (
SELECT Dates.&quot;Date&quot;,
       test.DeviceID,
       test.State,
       ROW_NUMBER() OVER (PARTITION BY test.DeviceID, 
                                       Dates.&quot;Date&quot; 
                          ORDER BY test.&quot;Date&quot; DESC) rn
FROM generate_series('2020-12-09'::timestamp, '2020-12-12', '1 DAY') Dates (&quot;Date&quot;)
JOIN test ON Dates.&quot;Date&quot; &gt;= test.&quot;Date&quot;::date
)
SELECT DeviceID, State, &quot;Date&quot;
FROM cte
WHERE rn = 1
ORDER BY 1, 3

fiddle