SQL queries

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

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

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

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