using SQLite for consumption tracking

Question

I am trying to make a query for sqlite which gives me the consumption of energy meters. If I want to create a simple example:

CREATE TABLE datalog (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 tstamp DATETIME DEFAULT CURRENT_TIMESTAMP,
 rValue REAL);


INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 00:00:01', 25.1);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 00:15:01', 26.2);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 00:30:01', 27.5);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 00:45:01', 28.8);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 01:00:01', 29.4);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 01:15:01', 30.2);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 01:30:01', 31.5);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 01:45:01', 32.2);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 02:00:01', 33.9);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 02:15:01', 35.2);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 02:30:01', 36.1);
INSERT INTO datalog (tstamp,rValue) VALUES ('2020-01-02 15:30:01', 80.4);

I want to write a query for this table which gives me the consumption of the meter by selecting the first readed value and datetime today (if we assume it is 2020-01-02 is today) and last readed value and datetime of today and gives the below table

startDT             | startValue | lastDT              | lastValue | consumption
2020-01-02 00:00:01 | 25.1       | 2020-01-02 15:30:01 | 80.4      | 55.3

Answer 1

Nevermind I solved it by doing more research

answer is using multiple subqueries at FROM clause

SELECT
startData.tstamp AS 'starttime',
startData.rValue AS 'startvalue',
lastData.tstamp AS 'lasttime',
lastData.rValue AS 'lastvalue',
(lastData.rValue-startData.rValue) AS 'consumption'
FROM (  SELECT tstamp, rValue
        FROM datalog
        WHERE   tstamp>= datetime('now','start of day')
        ORDER BY tstamp ASC LIMIT 1) startData,
     (  SELECT tstamp,rValue
        FROM datalog
        WHERE tstamp>= datetime('now','start of day')
        ORDER BY tstamp DESC LIMIT 1) lastData;

Answer 2

There is no need to cross join 2 subqueries.
You can do it with window functions:

SELECT DISTINCT
       MIN(tstamp) OVER () startDT,
       FIRST_VALUE(rValue) OVER (ORDER BY tstamp) startValue,
       MAX(tstamp) OVER () lastDT,
       FIRST_VALUE(rValue) OVER (ORDER BY tstamp DESC) lastValue,
       FIRST_VALUE(rValue) OVER (ORDER BY tstamp DESC) - FIRST_VALUE(rValue) OVER (ORDER BY tstamp) consumption
FROM datalog
WHERE date(tstamp) = CURRENT_DATE