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,hidden FROM camera;
+----+------------------+--------+
| id |       name       | hidden |
+----+------------------+--------+
| 1  | CCD Simulator    | 0      |
| 2  | ZWO CCD ASI290MM | 0      |
+----+------------------+--------+

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

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;