I have several questions, for one, what ever I try Grafan does not play nice with my TimeScaleDB data and I don’t know why??
Anyway, my question I’m querying the postgresqldb, I’m trying to get SmartMeter reading data out of the DB, it is in there but what I want is the latest entry for that day and I want the daily delta.
I tried using a DISTINCT
SELECT DISTINCT ON (time::date) time,
state,
LAG(state,-1,0) OVER (ORDER BY time DESC),
TO_NUMBER(state, '99.999,00') - LAG( TO_NUMBER(state,'99.999,00'),-1,0) OVER (ORDER BY time DESC) AS "Delta"
FROM public.ltss
WHERE entity_id = 'sensor.gj_meter_heat_consumption'
ORDER BY time::date DESC, time DESC;
But that gives me no control what soever over which entry is chosen and sometimes it even skips days.
So I tried
SELECT time,
state,
TO_NUMBER(state, '99.999,00') - LAG( TO_NUMBER(state,'99.999,00'),-1,0) OVER (ORDER BY time DESC) AS "Delta"
FROM PUBLIC.ltss
WHERE entity_id = 'sensor.gj_meter_heat_consumption'
AND date_part('hour', time ) = 23 AND date_part('minute', time ) >58 AND date_part('seconds', time ) <35
ORDER BY id DESC)
ORDER BY time::date DESC, time DESC;
Stil no joy as now the Delta is “0” (because in the ltss, smart meter data is stored every 5 minutes of course so the lag clause (state,1) gives me the same results.
So, I guessed, let’s give the nice people at the forum a try, maybe somebody has a good idea?