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
2020-12-09, so I would need to query outside the given date ranges to find the
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