How to match Values based on time stamps and when time stamp does not exist the value is the previous time stamp's value

0
votes

I am trying to generate a report That will join a table based on a time series created which is in 1 minute interval for a total of 1440 rows per day.

I would like to join every interval with a timestamp on another table with values, but when that time stamp does not exist in the table the value is from the previous timestamp

Generated_TIME_STAMP | TIMESTAMP             | VALUE
12/01/2019 00:01:000 | 12/01/2019 00:01:000  | 1
12/01/2019 00:02:000 | NULL                  | 1
12/01/2019 00:03:000 | 12/01/2019 00:03:000  | 3
12/01/2019 00:04:000 | 12/01/2019 00:04:000  | 7
12/01/2019 00:03:000 | NULL                  | 7

So, generating a time series, joining that time series with a table's time stamp to identify values for those time stamps, when a timestamp does not exist in the table the value column takes the value from the previous time stamp that did exist.

1
sqlpostgresqltimestamp
what is your current query? – richyen

1 Answers

0
votes

You can generate the rows with generate_series(). You can then fetch the row using a lateral join:

select ts.ts,
       (case when t.timestamp = ts.ts then t.timestamp end) as timestamp,
       t.value
from generate_series('2019-12-01'::timestamp, '2019-12-01 23:59:00'::timestamp, interval '1 minute') ts(ts) left join lateral
     (select t.*
      from yourtable t
      where t.timestamp <= ts.ts
      order by t.timestamp desc
      limit 1
     ) t;