SQL queries

General

Start a sqlite session with the following command

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

Hide cameras

Use this to hide cameras from web interface.

sqlite> SELECT id, name, friendlyName, hidden FROM camera;
+----+------------------+--------------+--------+
| id |       name       | friendlyName | hidden |
+----+------------------+--------------+--------+
| 1  | CCD Simulator    |              | 0      |
| 2  | ZWO CCD ASI290MM |              | 0      |
+----+------------------+--------------+--------+

sqlite> UPDATE camera SET hidden=1 WHERE id=1;

Queries

• Example of chart data
  • Averaging stars by 5 entries
  • Showing SQM deltas from previous entry

SELECT
    i.exposure,
    i.temp,
    i.adu,
    i.sqm,
    i.stars,
    i.detections,
    i.sqm,
    avg(i.stars) OVER (ORDER BY i.createDate ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS stars_rolling,
    i.sqm - lag(i.sqm) OVER (ORDER BY i.createDate) AS sqm_diff
FROM image i
JOIN camera c
    ON c.id = i.camera_id
WHERE
    c.id = 1 AND
    i.createDate > datetime(datetime('now'), '-15 MINUTE')
ORDER BY
    i.createDate DESC;

• 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
JOIN camera c
  ON i.camera_id = c.id
WHERE
  c.id = 1 AND
  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
JOIN camera c
  ON i.camera_id = c.id
WHERE
  c.id = 1 AND
  i.createDate > datetime(datetime('now'), '-1 HOUR')
ORDER BY
  i.createDate DESC;