# How to calculate hourly electricity consumption in QuestDB?

**URL:** <https://community.questdb.com/t/how-to-calculate-hourly-electricity-consumption-in-questdb/40>\
**Category:** Community\
**Tags:** sql, question\
**Created:** [June 26, 2024, 6:16pm UTC](https://community.questdb.com/t/how-to-calculate-hourly-electricity-consumption-in-questdb/40 "2024-06-26T18:16:30Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex004/uploads/questdb/original/1X/b6b1b349e598d0550947e63d7923a9d7df0279eb.png) [@system](https://community.questdb.com/u/system)\
**Post date:** [June 26, 2024, 6:16pm UTC](https://community.questdb.com/t/how-to-calculate-hourly-electricity-consumption-in-questdb/40/1 "2024-06-26T18:16:30Z")

</div>

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

```auto
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

```auto
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.

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [June 26, 2024, 6:16pm UTC](https://community.questdb.com/t/how-to-calculate-hourly-electricity-consumption-in-questdb/40/2 "2024-06-26T18:16:40Z")

</div>

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

```auto
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
  )
)

```