How to query time series with gaps between a given date range in PostgreSQL? My problem is I don't know how to query time series data between a specified date range where the data has gaps in it and the last known value which should carry forward for each date falls outside the queried date range.
I have the following data in a PostgreSQL table:
DeviceID    State    Date
1           L7       2020-12-05 15:18:00
1           L1       2020-12-10 00:15:00
1           L5       2020-12-11 15:00:00
1           L3       2020-12-14 13:00:00
2           L2       2020-12-08 14:00:00
2           L1       2020-12-14 05:32:00

I would like to query between 2020-12-09 -- 2020-12-12, but my problem is that I don't know the state of DeviceID 1 on 2020-12-09 because the series data has gaps in it.
In the example above DeviceID 1 state is L7 at 2020-12-09, so I would need to query outside the given date ranges to find the L7 state.
My queried table should look like this for the specified date ranges:
DeviceID    State    Date
1           L7       2020-12-09 
1           L1       2020-12-10
1           L5       2020-12-11
1           L5       2020-12-12
2           L2       2020-12-09
2           L2       2020-12-10
2           L2       2020-12-11
2           L2       2020-12-12

My end goal is to calculate the time difference between the state changes for a DeviceID, so I could still do that from data with gaps, my only problem is that I don't know the last state change event that falls outside the queried date ranges, in the example above I don't know the state is L7 for DeviceID 1 and L2 for DeviceID 2 at 2020-12-09.
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