Count al entries and filter by time series


I try to calculate the energy consumption, that works very well, if i want to calculate the total consumption.

I to that with:

SELECT unixepoch(d.time) - 7200 as time,
 COUNT(CASE WHEN d.time like '%' THEN 1 END) As usage
  Data d

this shows me the total consumption, but when i set the time series to last 24h i just want to see the consumption in the last 24h, how can i filter that?

ok, i found it on my own, but for upcoming questions in the future here is the solution:

the query is this:

SELECT unixepoch(d.time) - 7200 as Zeit,
 COUNT(CASE WHEN unixepoch(d.time) >= $__from / 1000  and unixepoch(d.time) <= $__to / 1000 THEN 1 END) / 10 As Verbrauch
  Data d

there are still errors in the calculation, like timeshifts, but the filter here is the imporrtant thing:

WHEN unixepoch(d.time) >= $__from / 1000  and unixepoch(d.time) <= $__to / 1000
1 Like