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):

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:

_{^{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?

CodePudding user response：

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;