Cannot calculate correct daily spread for energy usage

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.

Hi @utdrmac

Does adding timeSrc: "_start" to you aggregateWindow function change anything?

|> aggregateWindow(every: 1d, fn: spread, createEmpty: false, timeSrc: "_start") // calculates the amount per day using the spread

You can also see how this changes the results:
|> aggregateWindow(every: 1d, fn: spread, createEmpty: false, timeSrc: "_stop")