How to query time series with gaps between a given date range in PostgreSQL?

Question

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.

Answer 1

WITH cte AS (
SELECT Dates."Date",
       test.DeviceID,
       test.State,
       ROW_NUMBER() OVER (PARTITION BY test.DeviceID, 
                                       Dates."Date" 
                          ORDER BY test."Date" DESC) rn
FROM generate_series('2020-12-09'::timestamp, '2020-12-12', '1 DAY') Dates ("Date")
JOIN test ON Dates."Date" >= test."Date"::date
)
SELECT DeviceID, State, "Date"
FROM cte
WHERE rn = 1
ORDER BY 1, 3

fiddle

Answer 2

Generally when querying a point in time, using a correlated subquery is the most direct method to produce your desired output. With the correct primary key it will perform very well (which should be (DeviceID,Date) in your example).

SELECT
  Device.DeviceID
 ,Dates."Date"
 ,DeviceState.State
 ,DeviceState."Date" AS State_Date
FROM
  Device Device
CROSS JOIN
  generate_series('2020-12-09'::timestamp, '2020-12-12', '1 DAY') Dates ("Date")
LEFT JOIN
  DeviceState DeviceState
    ON DeviceState.DeviceId = Device.DeviceId
         AND DeviceState."Date" =
           (
             SELECT
               MAX("Date")
             FROM
               DeviceState
             WHERE
               DeviceID = Device.DeviceID
                 AND "Date" < Dates."Date" + INTERVAL '1 DAY' /* Gets value as of end of date provided */
           )
ORDER BY
  DeviceID
 ,"Date"

Fiddle (with credit due to Akina for doing most of the typing)