Sqlite return row difference between latest value and value 24 hours earlier

Question

Sqlite noob here. I have an SQLite time series database that is updated every four minutes and I want to report a difference in row value between the most recent datapoint and one that was (approx) 24 hours earlier. If I run

SELECT entity_id, state, last_changed
FROM "states" where entity_id = "sensor.nodered_46ce6408_d3dd5c" and last_changed > "1002020-05-14 18:12:28.008172"

I get

entity_id   state   last_changed
sensor.nodered_46ce6408_d3dd5c  6940.38     2020-05-04 18:14:35.969094
sensor.nodered_46ce6408_d3dd5c  6940.4      2020-05-04 18:18:45.913404
sensor.nodered_46ce6408_d3dd5c  6940.42     2020-05-04 18:22:55.892357

But using the following as a first step I do not even get a result for difference between the last two values.

SELECT entity_id, state, last_changed,
    (SELECT A.state - B.state
        FROM states as B
        WHERE B.entity_id = A.entity_id AND
            B.last_changed = (SELECT MAX(last_changed) FROM states C WHERE C.last_changed > A.last_changed AND C.entity_id = A.entity_id)) AS difference
FROM states as A

I don't need to write the difference result into the table I just need the result with the query being run every few hours.

Any clues for resolving this? TIA.

EDIT

I have improved my query but not there yet. I get a near "SELECT": syntax error

SELECT state
    FROM states
    SELECT (A.state - B.state)
    WHERE B.(entity_id = "sensor.nodered_46ce6408_d3dd5c" AND last_changed >= datetime('now', 'localtime','-24 hours') order by last_changed limit 1) AND A.(entity_id = "sensor.nodered_46ce6408_d3dd5c"
    AND last_changed <=  datetime('now', 'localtime') order by last_changed desc limit 1)

score 1 · Answer 1 · answered May 17 '20 at 13:47

The working answer is:

SELECT b.entity_id, 
       b.state, 
       b.last_changed, 
       ( b.state - (SELECT a.state 
                    FROM   states a 
                    WHERE  a.entity_id = "sensor.nodered_46ce6408_d3dd5c" 
                           AND a.last_changed >= Datetime('now', 'localtime', 
                                                 '-24 hours' 
                                                 ) 
                    ORDER  BY a.last_changed 
                    LIMIT  1) ) AS difference 
FROM   states b 
WHERE  b.entity_id = "sensor.nodered_46ce6408_d3dd5c" 
       AND b.last_changed <= Datetime('now', 'localtime') 
ORDER  BY b.last_changed DESC 
LIMIT  1

Credit to @sukitha

Sqlite return row difference between latest value and value 24 hours earlier

1 Answers1