SQL queries

General

Start a sqlite session with the following command

sqlite3 /var/lib/indi-allsky/indi-allsky.sqlite

Queries

• Show number of seconds between each image and the last image for the last 24 hours.

SELECT
  i.id,
  i.createDate,
  ROUND(i.exposure, 1),
  strftime('%s', i.createDate) - LAG(strftime('%s', i.createDate))
    OVER (ORDER BY i.createDate) AS date_diff
FROM image i
WHERE
  i.createDate > datetime(datetime('now'), '-24 HOUR')
ORDER BY
  i.createDate DESC;

• Show SQM values and relative increase from last SQM value for the last hour.

SELECT
  i.id,
  i.createDate,
  CAST(i.sqm AS int),
  CAST((i.sqm - LAG(i.sqm) OVER (ORDER BY i.createDate)) AS int)
FROM image i
WHERE
  i.createDate > datetime(datetime('now'), '-1 HOUR')
ORDER BY
  i.createDate DESC;