How to join generated datetimes with data in SQLite database?

Question

I have a table with data from a sensor created like that:

CREATE TABLE IF NOT EXISTS "aqi" (
  "time" datetime,
  "pm25" real,
  "pm10" real
);

When is sensor running, it sends data to a server (which it writes to a database) every second. But when the sensor is not running, there are "gaps" in data in the database like that (I've rewritten time column to a readable format and timezone GMT+01, leaving raw data in parentheses):

time	pm25	pm10
...	...	...
`2021-12-28 18:44` (`1640713462`)	`9.19`	`9.27`
`2021-12-28 18:45` (`1640713522`)	`9.65`	`9.69`
`2021-12-28 18:46` (`1640713582`)	`9.68`	`9.76`
`2021-12-29 10:17` (`1640769421`)	`7.42`	`7.42`
`2021-12-29 10:18` (`1640769481`)	`7.94`	`7.98`
`2021-12-29 10:19` (`1640769541`)	`7.42`	`7.43`
...	...	...

I wanted to create a query, that selects data from the last 24 hours, outputting pm25 and pm10 as NULL if there aren't data in the table for the current time. So the table above would look like that:

time	pm25	pm10
...	...	...
`2021-12-28 18:44` (`1640713462`)	`9.19`	`9.27`
`2021-12-28 18:45` (`1640713522`)	`9.65`	`9.69`
`2021-12-28 18:46` (`1640713582`)	`9.68`	`9.76`
`2021-12-28 18:47` (`1640713642`)	`NULL`	`NULL`
`2021-12-28 18:48` (`1640713702`)	`NULL`	`NULL`
`2021-12-28 18:49` (`1640713762`)	`NULL`	`NULL`
...	...	...
`2021-12-29 10:14` (`1640769262`)	`NULL`	`NULL`
`2021-12-29 10:15` (`1640769322`)	`NULL`	`NULL`
`2021-12-29 10:16` (`1640769382`)	`NULL`	`NULL`
`2021-12-29 10:17` (`1640769421`)	`7.42`	`7.42`
`2021-12-29 10:18` (`1640769481`)	`7.94`	`7.98`
`2021-12-29 10:19` (`1640769541`)	`7.42`	`7.43`
...	...	...

_{^{I don't mind if the seconds would be different because of the generation of time...}}

I tried generating time for the last 24 hours using code from https://stackoverflow.com/a/32987070 and that works, as I wanted:

WITH RECURSIVE dates(generated_time) AS (
  VALUES(datetime('now', '-1 minute', 'localtime'))
  UNION ALL
  SELECT datetime(generated_time, '-1 minute')
  FROM dates
  LIMIT 1440
)
SELECT strftime('%Y-%m-%d %H:%M', datetime(generated_time)) AS time
FROM dates;

But I don't know how to add (JOIN) data from the sensor (columns pm25, pm10) to query above... I tried something, but it outputs 0 rows:

WITH RECURSIVE dates(generated_time) AS (
  VALUES(datetime('now', '-1 minute', 'localtime'))
  UNION ALL
  SELECT datetime(generated_time, '-1 minute')
  FROM dates
  LIMIT 1440
)
SELECT
    strftime('%Y-%m-%d %H:%M', datetime(generated_time)) AS generated_time,
    pm25, 
    pm10
FROM
    dates
    INNER JOIN aqi ON generated_time = strftime('%Y-%m-%d %H:%M', datetime(aqi.time));

Probably it's something really obvious, that I'm missing, but I have no idea :/

EDIT:
As @DrummerMann pointed out, it works with LEFT JOIN, but it takes around one whole minute to execute the query (in the database is around 14 000 values):

WITH RECURSIVE dates(time) AS (
  VALUES(datetime('now', '-1 minute', 'localtime'))
  UNION ALL
  SELECT datetime(time, '-1 minute')
  FROM dates
  LIMIT 1440
)
SELECT
    dates.time,
    aqi.pm25, 
    aqi.pm10
FROM
    dates
    LEFT JOIN aqi ON strftime('%Y-%m-%d %H:%M', datetime(dates.time)) = strftime('%Y-%m-%d %H:%M', datetime(aqi.time, 'unixepoch', 'localtime'))
    ORDER BY dates.time;

Is there any better way to do that?

Have you checked out `LEFT JOIN`? https://www.w3schools.com/sql/sql_join_left.asp — DrummerMann, Jan 07 '22 at 14:21
I find out, that I forgot to add `'unixepoch', 'localtime'` to `datetime` conversion, so I edited my question with working `LEFT JOIN` code, but it takes one whole minute to execute the query... — BelKed, Jan 07 '22 at 15:01
move `strftime('%Y-%m-%d %H:%M', datetime(dates.time))` to cte. Using a precomputed value in the ON clause should be a bit faster. — Serg, Jan 07 '22 at 15:10

forpas · Accepted Answer · 2022-01-07T15:23:04.520

1

Try this version of the cte, which uses integer unix timestamps where the seconds are stripped off and there are no functions in the ON clause of the join:

WITH RECURSIVE dates(generated_time) AS (
  SELECT strftime('%s', 'now', '-1 minute', 'localtime') / 60 * 60
  UNION ALL
  SELECT generated_time - 60
  FROM dates
  LIMIT 1440
)
SELECT strftime('%Y-%m-%d %H:%M', d.generated_time, 'unixepoch', 'localtime') AS generated_time,
       a.pm25, 
       a.pm10
FROM dates d LEFT JOIN aqi a
ON d.generated_time = a.time / 60 * 60;

edited Jan 07 '22 at 15:23

answered Jan 07 '22 at 15:01

forpas

160,666
10
38
76

Thanks, it works, but second line of query should look like this `SELECT strftime('%s', 'now', '-1 minute') / 60 * 60`, because with the `'localtime'` option `time` will be shifted in output... Now it takes about 3.5 second for execution, which is really good improvement :) – BelKed Jan 07 '22 at 16:33
@BelKed I use the `'localtime'` modifier because you have it in your code and I assumed that the timestamps in your table are localized. – forpas Jan 07 '22 at 16:41
Oh, I see... I have in the table UTC unix timestamp, so it needs conversion to `'localtime'`, but in your answer you use `a.time` in last line without conversion to `'localtime'`, so that's probably the cause of time shift... – BelKed Jan 07 '22 at 16:50

How to join generated datetimes with data in SQLite database?

1 Answers1