Hello community. I’m struggling with this, seemingly simply, query to give me daily energy usage. There are a couple issues that I believe complicate this: 1) Data is stored in UTC, but I’m America/New_York, 2) range() is inclusive of start, but exclusive of stop.
Here is a sample dataset, values are every 15 minutes, UTC:
| timestamp | meterValue |
|---|---|
| 2025-05-01T04:45:00Z | 7046.335 |
| 2025-05-01T05:00:00Z | 7046.534 |
| 2025-05-01T05:15:00Z | 7046.640 |
| … | |
| 2025-05-02T04:45:00Z | 7067.175 |
| 2025-05-02T05:00:00Z | 7067.275 |
| … | |
| 2025-05-03T04:45:00Z | 7084.510 |
| 2025-05-03T05:00:00Z | 7084.590 |
My electric provider uses “midnight-to-midnight” in their calculations. Using the above data, and converting for timezone, I should have data like this:
| timestampStart | value | timestampStop | value | energyUsed |
|---|---|---|---|---|
| 2025-05-01T00:00:00Z | 7046.534 | 2025-05-02T00:00:00Z | 7067.275 | 20.741 |
| 2025-05-02T00:00:00Z | 7067.275 | 2025-05-03T00:00:00Z | 7084.590 | 17.315 |
I cannot seem to do this in Influx because range(stop:) does not include the “next midnight” value. It will use the previous value from HH:45:00, which is incorrect.
I attempted to do this:
|> range(start: v.timeRangeStart, stop: date.add(d: 1s, to: v.timeRangeStop))
This makes range() include both 00:00:00 values. But then when I use:
aggregateWindow(every: 1d, fn: spread, location: timezone.location(name: "America/New_York"))
it does not treat “1d” as midnight-to-midnight. The first value returned is 20.64, which is the difference between 7046.534 (midnight) and 7067.175 (15m before next day midnight), which is incorrect.
How do I make aggregateWindow include the next midnight? Changing it to “every: 24h” makes no difference. Adding “offset: 1s” shifts both start/stop, which is completely wrong.
The daily windows need to be inclusive of start and stop.
