0

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.

6
  • So basically you want to get the last State of the Device that falls before your date range if there is no State on the start date of your range? – J.D. Dec 15 '20 at 13:11
  • 1
    @J.D. exactly this! – feketegy Dec 15 '20 at 13:36
  • See Akina's answer using the ROW_NUMBER() window function which is a way to internally number things sorted by any column you choose and can be partitioned (broken up as individual number series) by any other field. By doing this you can sort the Dates of each State per Device from newest to oldest filtered WHERE the Date < your start Date so that later on you can filter on the latest Date (row number = 1) that is before your start Date. – J.D. Dec 15 '20 at 13:57
  • So essentially I need to join the table again and query the rows by date which are < start date, then count the rows, order by last date first, then limit it to row_number = 1? How performant would this be to join the table against itself then execute the window function on it? I potentially will have millions of rows (in partitioned tables) – feketegy Dec 15 '20 at 14:06
  • In the first table "date" also contains time, and in the results you want it's just a date, which begs the question, what happens when a device changes state several times in a day and there are several lines in the table with same date but different times of day? Do you want the state at 00:00 on that date or at a specific time in the day? – bobflux Dec 15 '20 at 16:39
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

2
  • @J.D. The problem with this solution is that I would also need the time not just the dates, my end goal is to calculate the duration between state changes where the queried date range start date would contain the state before the start date, like in the example DeviceID 1 is L7, so to include a row with "start date" L7 somehow. This is essentially my problem. I would need to query the first row for each device "before" the queried start date. – feketegy Dec 15 '20 at 13:54
  • @feketegy This is another task. Create another topic. so to include a row with "start date" L7 somehow. No problems, add it to CTE output columns list and to final output. – Akina Dec 15 '20 at 14:59
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.