How to calculate hourly electricity consumption in QuestDB?

Community
,
1

I have a table with electricity meter readings in QuestDB with the data as following:

SELECT timestamp, reading from energy_value

"timestamp","reading"
"2024-02-28T20:42:05.661089Z",5365299.0
"2024-02-28T20:56:05.448730Z",5365343.0
"2024-02-28T21:12:05.039730Z",5365353.0
"2024-02-28T21:28:05.183123Z",5365410.0
"2024-02-28T21:47:05.689410Z",5365420.0
"2024-02-28T22:16:04.791473Z",5365486.0
"2024-02-28T22:42:05.460595Z",5365551.0

The timings of the data points are random. How can I calculate the hourly electricity consumption? I tried

select last(reading) - first(reading) as diff, timestamp
from energy_value
sample by 1h align to calendar

but it seems to be inaccurate, it misses the difference on the edge of the hour.

2

It can be done with first_value window function to get previous value

SELECT timestamp, value - prev_value as diff
FROM (
  SELECT timestamp, value, 
     first_value(value)  OVER (ORDER BY timestamp ROWS 1 PRECEDING) as prev_value 
  FROM 
  (
    select first(value) value, timestamp
    FROM device_frmpayload_data_energy_value
    SAMPLE by 1h align to calendar
  )
)