I'm struggling to come up with an answer to this problem and so hope to find some help here. I have the following time series data table with missing entries:
CREATE TABLE foo
AS
SELECT timestamp::timestamp, deviceid, eventcount
FROM ( VALUES
( '2017-01-01 00:00:00', 'AAAAA', 1000 ),
( '2017-01-01 00:30:00', 'AAAAA', 1000 ),
( '2017-01-01 00:40:00', 'AAAAA', 1000 ),
( '2017-01-01 00:00:00', 'BBBBB', 2000 ),
( '2017-01-01 00:10:00', 'BBBBB', 2000 ),
( '2017-01-01 00:50:00', 'BBBBB', 2000 )
) AS t(Timestamp, DeviceID, EventCount);
I need to generate from this a complete series i.e.
+---------------------+----------+------------+
| Timestamp | DeviceID | EventCount |
+---------------------+----------+------------+
| 2017-01-01 00:00:00 | AAAAA | 1000 |
| 2017-01-01 00:10:00 | AAAAA | 0 |
| 2017-01-01 00:20:00 | AAAAA | 0 |
| 2017-01-01 00:30:00 | AAAAA | 1000 |
| 2017-01-01 00:40:00 | AAAAA | 1000 |
| 2017-01-01 00:50:00 | AAAAA | 0 |
| 2017-01-01 00:00:00 | BBBBB | 2000 |
| 2017-01-01 00:10:00 | BBBBB | 2000 |
| 2017-01-01 00:20:00 | BBBBB | 0 |
| 2017-01-01 00:30:00 | BBBBB | 0 |
| 2017-01-01 00:40:00 | BBBBB | 0 |
| 2017-01-01 00:50:00 | BBBBB | 2000 |
+---------------------+----------+------------+
I have tried generating a series of timestamps with an interval of 10 minutes and then left joining on the first table but what I end up with is this:
+---------------------+----------+------------+
| Timestamp | DeviceID | EventCount |
+---------------------+----------+------------+
| 2017-01-01 00:00:00 | NULL | 0 |
| 2017-01-01 00:10:00 | NULL | 0 |
| 2017-01-01 00:20:00 | NULL | 0 |
| 2017-01-01 00:30:00 | NULL | 0 |
| 2017-01-01 00:40:00 | NULL | 0 |
| 2017-01-01 00:50:00 | NULL | 0 |
| 2017-01-01 00:00:00 | AAAAA | 1000 |
| 2017-01-01 00:30:00 | AAAAA | 1000 |
| 2017-01-01 00:40:00 | AAAAA | 1000 |
| 2017-01-01 00:00:00 | BBBBB | 2000 |
| 2017-01-01 00:10:00 | BBBBB | 2000 |
| 2017-01-01 00:50:00 | BBBBB | 2000 |
+---------------------+----------+------------+