Flux query displays previous days data

InfluxDB 2
1

Hi all, I’m very much a beginner with flux and am trying to analyse the power use/generation/export from my home solar system.
I have InfluxDB v2.6.1 with 3 buckets each fed from one of IotaWatt, Smart Meter, Growatt Inverter.
I am getting the Power (W/kW) data into the buckets fine.
I am also calculating the Energy (kWh) using the Integral function. And I have queries set up to show the energy figures per day for the current month and last month setup in grafana.
I’m using Query Options/Relative Time = now/M in grafana to set the time frames.
I have 2 problems.

  1. The data is graphed a day out. The data for day 1 shows up in day 2 and so on. This occurs both in grafana and influxdb data explorer.
  2. I’m getting the data posted at 00:00:00Z but am getting timestamps showing up on the graph every 6 hours although there is no data. I just want one timestamp per day showing the data for that day.

Here is one of the queries I have running…

// Setup your timezone info so that the day resets for you correctly.
import "timezone" 
option location = timezone.location(name: "Europe/London")

// Query to grab Watt hours from db
from(bucket: "growattdb")
 |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "inverter_1")
  |> filter(fn: (r) => r["sensor_id"] == "load_power")
  |> filter(fn: (r) => r["_field"] == "value")
//|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> aggregateWindow(
    every: 1d,
    fn: (tables=<-, column) =>
      tables
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
|> keep(columns: ["_value", "_time"])
|> rename(columns: {_value: "Total Load Energy Per Day", _time: "Date"})

This is the returned data in a csv file. I can’t upload the file so here is the data copy and pasted…

result	table	Total Load Energy Per Day	Date
	0	0	2023-04-01T23:00:00Z
	0	9.080306094	2023-04-02T23:00:00Z
	0	8.26833442	2023-04-03T23:00:00Z
	0	4.358219427	2023-04-04T14:59:03Z

The results from the same query in grafana with the Query options set. Notice the time stamp changes.


Date

Total Load Energy Per Day
2023-04-02 00:00:00
7.58
2023-04-03 00:00:00
9.08
2023-04-04 00:00:00
8.27
2023-04-04 16:16:36
4.46

My theory for why it’s a day out:
Because the query options > Relative Time > now/M setting timestamps its calculation at 00:00:00Z at the end of the last day it is taking this as the start as the next day and posting the data as being for that day?
Am I right?
If so how do I get around it?
Or am I barking up completely the wrong tree?
And how do I get rid of the spurious timestamps in my graphs?
Is there a better way to achieve what I’m after?

Any help appreciated.