10-minute difference between timestamps in InfluxDB and Grafana

Grafana InfluxDB
1

Hello, thanks for looking.

I have electricity consumption data in InfluxDB v2.4.0 which I want to view with a Time Series in Grafana v9.1.1.

This is a snip showing the raw data in InfluxDB:

image
image1082×213 21.7 KB

I’ve highlighted the value of 1.098 kWh at 2022-08-29T08:40:00.000Z.

In Grafana I have created a Time Series with the following query:

from(bucket:"Home Assistant")
  |> range(start: 2022-08-28T00:00:00Z)
  |> filter(fn: (r) => r._measurement == "electricity_supply" and r._field == "kWh_in" and r.type == "electricity_consumed")
  |> yield()

Because my timezone is UTC1, I would expect that datapoint to appear as 09:40 in Grafana, but it actually appears as 2022-08-29T09:30:00:

image

I’ve tried deleting and recreating the TIme Series in Grafana, leaving all options as default, but I can’t work out why there is a 10-minute difference between the timestamps in the InfluxDB data and the representation of that data in Grafana.

I feel like it might be something to do with grouping values to the nearest half hour, but I can’t find any options for that.

Any help would be appreciated!

Chris

2

Hello,
I’m not sure how youachieve to get different result,
can you make a screen of your query in grafana inspector ?
This way you can check if the data from influx are good. If they are good then this come from your UTC change.
By the way good way to query timeshifting value in Flux is to use

This way you can group by your timeshifting range
If you will aggregate value over shifting period and compute calculation over this time you can use :

3

This is the query from the Query Inspector (I hope this is what you meant):

  "request": {
    "url": "api/ds/query",
    "method": "POST",
    "data": {
      "queries": [
        {
          "datasource": {
            "uid": "YHeJJbW4z",
            "type": "influxdb"
          },
          "query": "from(bucket:\"Home Assistant\")\n  |> range(start: 2022-08-28T00:00:00Z)\n  |> filter(fn: (r) => r._measurement == \"electricity_supply\" and r._field == \"kWh_in\" and r.type == \"electricity_consumed\")\n  |> yield()",
          "refId": "A",
          "datasourceId": 5,
          "intervalMs": 60000,
          "maxDataPoints": 1489
        }
      ],
      "range": {
        "from": "2022-08-28T23:00:00.000Z",
        "to": "2022-08-29T22:59:59.999Z",
        "raw": {
          "from": "now-1d/d",
          "to": "now-1d/d"
        }
      },
      "from": "1661727600000",
      "to": "1661813999999"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "frames": [
          {
            "schema": {
              "name": "electricity_supply",
              "refId": "A",
              "meta": {
                "executedQueryString": "from(bucket:\"Home Assistant\")\n  |> range(start: 2022-08-28T00:00:00Z)\n  |> filter(fn: (r) => r._measurement == \"electricity_supply\" and r._field == \"kWh_in\" and r.type == \"electricity_consumed\")\n  |> yield()"
              },
              "fields": [
                {
                  "name": "Time",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time",
                    "nullable": true
                  }
                },
                {
                  "name": "kWh_in",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float64",
                    "nullable": true
                  },
                  "labels": {
                    "type": "electricity_consumed"
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  1661646600000,
                  1661648400000,
                  1661650200000,
                  1661652000000,
                  1661653800000,
                  1661655600000,
                  1661657400000,
                  1661659200000,
                  1661661000000,
                  1661662800000,
                  1661664600000,
                  1661666400000,
                  1661668200000,
                  1661670000000,
                  1661671800000,
                  1661673600000,
                  1661675400000,
                  1661677200000,
                  1661679000000,
                  1661680800000,
                  1661682600000,
                  1661684400000,
                  1661686200000,
                  1661688000000,
                  1661689800000,
                  1661691600000,
                  1661693400000,
                  1661695200000,
                  1661697000000,
                  1661698800000,
                  1661700600000,
                  1661702400000,
                  1661704200000,
                  1661706000000,
                  1661707800000,
                  1661709600000,
                  1661711400000,
                  1661713200000,
                  1661715000000,
                  1661716800000,
                  1661718600000,
                  1661720400000,
                  1661722200000,
                  1661724000000,
                  1661725800000,
                  1661727600000,
                  1661729400000,
                  1661731200000,
                  1661733000000,
                  1661734800000,
                  1661736600000,
                  1661738400000,
                  1661740200000,
                  1661742000000,
                  1661743800000,
                  1661745600000,
                  1661747400000,
                  1661749200000,
                  1661751000000,
                  1661752800000,
                  1661754600000,
                  1661756400000,
                  1661758200000,
                  1661760000000,
                  1661761800000,
                  1661763600000,
                  1661765400000,
                  1661767200000,
                  1661769000000,
                  1661770800000,
                  1661772600000,
                  1661774400000,
                  1661776200000,
                  1661778000000,
                  1661779800000,
                  1661781600000,
                  1661783400000,
                  1661785200000,
                  1661787000000,
                  1661788800000,
                  1661790600000,
                  1661792400000,
                  1661794200000,
                  1661796000000,
                  1661797800000,
                  1661799600000,
                  1661801400000,
                  1661803200000,
                  1661805000000,
                  1661806800000,
                  1661808600000,
                  1661810400000,
                  1661812200000,
                  1661814000000,
                  1661815800000,
                  1661817600000
                ],
                [
                  0.135,
                  0.125,
                  0.16,
                  0.119,
                  0.125,
                  0.144,
                  0.119,
                  0.144,
                  0.115,
                  0.117,
                  0.144,
                  0.11,
                  0.11,
                  0.133,
                  0.114,
                  0.328,
                  0.119,
                  0.148,
                  0.227,
                  0.452,
                  0.597,
                  0.124,
                  0.153,
                  0.158,
                  0.546,
                  0.554,
                  0.143,
                  0.175,
                  0.139,
                  0.18,
                  0.137,
                  0.15,
                  0.143,
                  0.151,
                  0.177,
                  0.163,
                  1.102,
                  0.185,
                  0.337,
                  0.331,
                  0.308,
                  0.314,
                  0.36,
                  0.306,
                  0.257,
                  0.281,
                  0.168,
                  0.119,
                  0.148,
                  0.122,
                  0.147,
                  0.117,
                  0.112,
                  0.147,
                  0.119,
                  0.137,
                  0.121,
                  0.114,
                  0.143,
                  0.111,
                  0.106,
                  1.023,
                  0.157,
                  0.153,
                  1.098,
                  0.134,
                  0.17,
                  0.265,
                  0.166,
                  0.156,
                  0.134,
                  0.349,
                  0.152,
                  0.135,
                  0.172,
                  0.147,
                  0.154,
                  0.267,
                  0.601,
                  0.201,
                  0.447,
                  0.41,
                  0.15,
                  0.124,
                  0.119,
                  0.33,
                  0.215,
                  0.491,
                  0.611,
                  0.231,
                  0.241,
                  0.219,
                  0.273,
                  0.172,
                  0.126,
                  0.123
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

I have converted the UNIX timestamps to human-readable date/time and the times are all either on the hour (XX:00:00) or at half past (XX:30:00), whereas InfluxDB times are all XX:10:00 and XX:40:00.

I tried the window() function but couldn’t get it to produce the results I wanted. I did have success with the aggregateWindow() function using the following query:

from(bucket:"Home Assistant")
  |> range(start: 2022-08-28T00:00:00Z)
  |> filter(fn: (r) => r._measurement == "electricity_supply" and r._field == "kWh_in" and r.type == "electricity_consumed")
  |> aggregateWindow(every: 30m, offset: -20m, fn: mean)
  |> yield()

That has now got the correct time values in Grafana, but something doesn’t feel right… It’s solved the issue, but it doesn’t explain why the 10-minute offset occurs. Also, the bars of the graph are much skinnier than they were before adding the aggregateWindow() function.

image

image

Thanks,

Chris

4

i do not explain the offset.
this is probably a miss understanding of something since grafana is using data from influx.
It’s can’t create new data by his own as you can imagine…

In you query inspector you have a data tab showing you data formated from influx and by grafana step by step. you can compare this from your flux query result in influxdb.
his only way to change by UTC time config but it’s about hours delta, not minutes.

5

if you provide me csv and screen i can try to insert this data in my influx and reproduce this.

6

I understand what you’re saying. Thanks for your offer to have a look at the data - I hope this is usable.

Screenshot of Grafana Query Inspector > Data tab

image
image518×525 34.4 KB

InfluxDB CSV

#group,false,false,true,true,false,false,true,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double,string,string,string
#default,mean,,,,,,,,
,result,table,_start,_stop,_time,_value,_field,_measurement,type
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T13:10:00Z,0.554,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T13:40:00Z,0.143,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T14:10:00Z,0.175,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T14:40:00Z,0.139,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T15:10:00Z,0.18,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T15:40:00Z,0.137,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T16:10:00Z,0.15,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T16:40:00Z,0.143,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T17:10:00Z,0.151,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T17:40:00Z,0.177,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T18:10:00Z,0.163,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T18:40:00Z,1.102,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T19:10:00Z,0.185,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T19:40:00Z,0.337,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T20:10:00Z,0.331,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T20:40:00Z,0.308,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T21:10:00Z,0.314,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T21:40:00Z,0.36,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T22:10:00Z,0.306,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T22:40:00Z,0.257,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T23:10:00Z,0.281,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-28T23:40:00Z,0.168,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T00:10:00Z,0.119,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T00:40:00Z,0.148,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T01:10:00Z,0.122,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T01:40:00Z,0.147,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T02:10:00Z,0.117,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T02:40:00Z,0.112,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T03:10:00Z,0.147,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T03:40:00Z,0.119,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T04:10:00Z,0.137,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T04:40:00Z,0.121,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T05:10:00Z,0.114,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T05:40:00Z,0.143,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T06:10:00Z,0.111,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T06:40:00Z,0.106,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T07:10:00Z,1.023,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T07:40:00Z,0.157,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T08:10:00Z,0.153,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T08:40:00Z,1.098,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T09:10:00Z,0.134,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T09:40:00Z,0.17,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T10:10:00Z,0.265,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T10:40:00Z,0.166,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T11:10:00Z,0.156,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T11:40:00Z,0.134,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T12:10:00Z,0.349,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T12:40:00Z,0.152,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T13:10:00Z,0.135,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T13:40:00Z,0.172,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T14:10:00Z,0.147,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T14:40:00Z,0.154,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T15:10:00Z,0.267,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T15:40:00Z,0.601,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T16:10:00Z,0.201,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T16:40:00Z,0.447,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T17:10:00Z,0.41,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T17:40:00Z,0.15,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T18:10:00Z,0.124,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T18:40:00Z,0.119,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T19:10:00Z,0.33,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T19:40:00Z,0.215,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T20:10:00Z,0.491,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T20:40:00Z,0.611,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T21:10:00Z,0.231,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T21:40:00Z,0.241,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T22:10:00Z,0.219,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T22:40:00Z,0.273,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T23:10:00Z,0.172,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-29T23:40:00Z,0.126,kWh_in,electricity_supply,electricity_consumed
,,0,2022-08-28T12:50:26.065907259Z,2022-08-30T12:50:26.065907259Z,2022-08-30T00:10:00Z,0.123,kWh_in,electricity_supply,electricity_consumed

Grafana Unformatted

sep=,
"Time","kWh_in electricity_consumed"
1661646600000,0.135
1661648400000,0.125
1661650200000,0.16
1661652000000,0.119
1661653800000,0.125
1661655600000,0.144
1661657400000,0.119
1661659200000,0.144
1661661000000,0.115
1661662800000,0.117
1661664600000,0.144
1661666400000,0.11
1661668200000,0.11
1661670000000,0.133
1661671800000,0.114
1661673600000,0.328
1661675400000,0.119
1661677200000,0.148
1661679000000,0.227
1661680800000,0.452
1661682600000,0.597
1661684400000,0.124
1661686200000,0.153
1661688000000,0.158
1661689800000,0.546
1661691600000,0.554
1661693400000,0.143
1661695200000,0.175
1661697000000,0.139
1661698800000,0.18
1661700600000,0.137
1661702400000,0.15
1661704200000,0.143
1661706000000,0.151
1661707800000,0.177
1661709600000,0.163
1661711400000,1.102
1661713200000,0.185
1661715000000,0.337
1661716800000,0.331
1661718600000,0.308
1661720400000,0.314
1661722200000,0.36
1661724000000,0.306
1661725800000,0.257
1661727600000,0.281
1661729400000,0.168
1661731200000,0.119
1661733000000,0.148
1661734800000,0.122
1661736600000,0.147
1661738400000,0.117
1661740200000,0.112
1661742000000,0.147
1661743800000,0.119
1661745600000,0.137
1661747400000,0.121
1661749200000,0.114
1661751000000,0.143
1661752800000,0.111
1661754600000,0.106
1661756400000,1.023
1661758200000,0.157
1661760000000,0.153
1661761800000,1.098
1661763600000,0.134
1661765400000,0.17
1661767200000,0.265
1661769000000,0.166
1661770800000,0.156
1661772600000,0.134
1661774400000,0.349
1661776200000,0.152
1661778000000,0.135
1661779800000,0.172
1661781600000,0.147
1661783400000,0.154
1661785200000,0.267
1661787000000,0.601
1661788800000,0.201
1661790600000,0.447
1661792400000,0.41
1661794200000,0.15
1661796000000,0.124
1661797800000,0.119
1661799600000,0.33
1661801400000,0.215
1661803200000,0.491
1661805000000,0.611
1661806800000,0.231
1661808600000,0.241
1661810400000,0.219
1661812200000,0.273
1661814000000,0.172
1661815800000,0.126
1661817600000,0.123

Grafana Formatted

sep=,
"Time","kWh_in electricity_consumed"
2022-08-28 01:30:00,0.1 kWh
2022-08-28 02:00:00,0.1 kWh
2022-08-28 02:30:00,0.2 kWh
2022-08-28 03:00:00,0.1 kWh
2022-08-28 03:30:00,0.1 kWh
2022-08-28 04:00:00,0.1 kWh
2022-08-28 04:30:00,0.1 kWh
2022-08-28 05:00:00,0.1 kWh
2022-08-28 05:30:00,0.1 kWh
2022-08-28 06:00:00,0.1 kWh
2022-08-28 06:30:00,0.1 kWh
2022-08-28 07:00:00,0.1 kWh
2022-08-28 07:30:00,0.1 kWh
2022-08-28 08:00:00,0.1 kWh
2022-08-28 08:30:00,0.1 kWh
2022-08-28 09:00:00,0.3 kWh
2022-08-28 09:30:00,0.1 kWh
2022-08-28 10:00:00,0.1 kWh
2022-08-28 10:30:00,0.2 kWh
2022-08-28 11:00:00,0.5 kWh
2022-08-28 11:30:00,0.6 kWh
2022-08-28 12:00:00,0.1 kWh
2022-08-28 12:30:00,0.2 kWh
2022-08-28 13:00:00,0.2 kWh
2022-08-28 13:30:00,0.5 kWh
2022-08-28 14:00:00,0.6 kWh
2022-08-28 14:30:00,0.1 kWh
2022-08-28 15:00:00,0.2 kWh
2022-08-28 15:30:00,0.1 kWh
2022-08-28 16:00:00,0.2 kWh
2022-08-28 16:30:00,0.1 kWh
2022-08-28 17:00:00,0.2 kWh
2022-08-28 17:30:00,0.1 kWh
2022-08-28 18:00:00,0.2 kWh
2022-08-28 18:30:00,0.2 kWh
2022-08-28 19:00:00,0.2 kWh
2022-08-28 19:30:00,1.1 kWh
2022-08-28 20:00:00,0.2 kWh
2022-08-28 20:30:00,0.3 kWh
2022-08-28 21:00:00,0.3 kWh
2022-08-28 21:30:00,0.3 kWh
2022-08-28 22:00:00,0.3 kWh
2022-08-28 22:30:00,0.4 kWh
2022-08-28 23:00:00,0.3 kWh
2022-08-28 23:30:00,0.3 kWh
2022-08-29 00:00:00,0.3 kWh
2022-08-29 00:30:00,0.2 kWh
2022-08-29 01:00:00,0.1 kWh
2022-08-29 01:30:00,0.1 kWh
2022-08-29 02:00:00,0.1 kWh
2022-08-29 02:30:00,0.1 kWh
2022-08-29 03:00:00,0.1 kWh
2022-08-29 03:30:00,0.1 kWh
2022-08-29 04:00:00,0.1 kWh
2022-08-29 04:30:00,0.1 kWh
2022-08-29 05:00:00,0.1 kWh
2022-08-29 05:30:00,0.1 kWh
2022-08-29 06:00:00,0.1 kWh
2022-08-29 06:30:00,0.1 kWh
2022-08-29 07:00:00,0.1 kWh
2022-08-29 07:30:00,0.1 kWh
2022-08-29 08:00:00,1.0 kWh
2022-08-29 08:30:00,0.2 kWh
2022-08-29 09:00:00,0.2 kWh
2022-08-29 09:30:00,1.1 kWh
2022-08-29 10:00:00,0.1 kWh
2022-08-29 10:30:00,0.2 kWh
2022-08-29 11:00:00,0.3 kWh
2022-08-29 11:30:00,0.2 kWh
2022-08-29 12:00:00,0.2 kWh
2022-08-29 12:30:00,0.1 kWh
2022-08-29 13:00:00,0.3 kWh
2022-08-29 13:30:00,0.2 kWh
2022-08-29 14:00:00,0.1 kWh
2022-08-29 14:30:00,0.2 kWh
2022-08-29 15:00:00,0.1 kWh
2022-08-29 15:30:00,0.2 kWh
2022-08-29 16:00:00,0.3 kWh
2022-08-29 16:30:00,0.6 kWh
2022-08-29 17:00:00,0.2 kWh
2022-08-29 17:30:00,0.4 kWh
2022-08-29 18:00:00,0.4 kWh
2022-08-29 18:30:00,0.2 kWh
2022-08-29 19:00:00,0.1 kWh
2022-08-29 19:30:00,0.1 kWh
2022-08-29 20:00:00,0.3 kWh
2022-08-29 20:30:00,0.2 kWh
2022-08-29 21:00:00,0.5 kWh
2022-08-29 21:30:00,0.6 kWh
2022-08-29 22:00:00,0.2 kWh
2022-08-29 22:30:00,0.2 kWh
2022-08-29 23:00:00,0.2 kWh
2022-08-29 23:30:00,0.3 kWh
2022-08-30 00:00:00,0.2 kWh
2022-08-30 00:30:00,0.1 kWh
2022-08-30 01:00:00,0.1 kWh
7

i think i understand :
On this screen you can see :

image
image787×317 62.1 KB

You have data every half hours in influxdb
on this screen you can see you have indeed data every half hours in grafana :
image
but with UTC +1 it will add one hours to all time value

|> aggregateWindow(every: 30m, fn: mean)

you do not need offset. i think weirdness come from here and the fact you have data on each Hours + 10 min and hours+ 40 min
If you did not resolve this, i’ll take time tonight to do this at home

8

If I use

|> aggregateWindow(every: 30m, fn: mean)

it actually shifts the 1.1 kWh value to 10:00 instead of 09:40… so even further away from what it’s supposed to be! I added the offset: -20m when I was playing with aggregateWindow() to bring it back to the right time.

image

I sure am confused by this!

9

please post your current time from your clock and UTC current time side by side
also how are you pushing this data into influxdb?

if via telegraf, where is that running?
where is your influxdb located? on what server , what tis the time on that influxdb server?
please show us also the code that is writing to influxdb

somewhere in that mix we can sort out the issue

10

My server: 18:00 (UTC+01:00, Europe/London)
UTC: 17:00

Using Node-RED, I make an HTTP request to an online service that provides my energy consumption in JSON format:

{
  "resource":"/electricity/consumption/1",
  "responseTimestamp":"2022-08-30T17:03:49.822Z",
  "start":"202208290030",
  "end":"202208300000",
  "granularity":"halfhour",
  "values":[
    {
      "timestamp":"2022-08-29 00:30",
      "value":0.148
    },
    {
      "timestamp":"2022-08-29 01:00",
      "value":0.122
    },
    {
      "timestamp":"2022-08-29 01:30",
      "value":0.147
    },
    {
      "timestamp":"2022-08-29 02:00",
      "value":0.117
    },
    {
      "timestamp":"2022-08-29 02:30",
      "value":0.112
    },
    {
      "timestamp":"2022-08-29 03:00",
      "value":0.147
    },
    {
      "timestamp":"2022-08-29 03:30",
      "value":0.119
    },
    {
      "timestamp":"2022-08-29 04:00",
      "value":0.137
    },
    {
      "timestamp":"2022-08-29 04:30",
      "value":0.121
    },
    {
      "timestamp":"2022-08-29 05:00",
      "value":0.114
    },
    {
      "timestamp":"2022-08-29 05:30",
      "value":0.143
    },
    {
      "timestamp":"2022-08-29 06:00",
      "value":0.111
    },
    {
      "timestamp":"2022-08-29 06:30",
      "value":0.106
    },
    {
      "timestamp":"2022-08-29 07:00",
      "value":1.023
    },
    {
      "timestamp":"2022-08-29 07:30",
      "value":0.157
    },
    {
      "timestamp":"2022-08-29 08:00",
      "value":0.153
    },
    {
      "timestamp":"2022-08-29 08:30",
      "value":1.098
    },
    {
      "timestamp":"2022-08-29 09:00",
      "value":0.134
    },
    {
      "timestamp":"2022-08-29 09:30",
      "value":0.17
    },
    {
      "timestamp":"2022-08-29 10:00",
      "value":0.265
    },
    {
      "timestamp":"2022-08-29 10:30",
      "value":0.166
    },
    {
      "timestamp":"2022-08-29 11:00",
      "value":0.156
    },
    {
      "timestamp":"2022-08-29 11:30",
      "value":0.134
    },
    {
      "timestamp":"2022-08-29 12:00",
      "value":0.349
    },
    {
      "timestamp":"2022-08-29 12:30",
      "value":0.152
    },
    {
      "timestamp":"2022-08-29 13:00",
      "value":0.135
    },
    {
      "timestamp":"2022-08-29 13:30",
      "value":0.172
    },
    {
      "timestamp":"2022-08-29 14:00",
      "value":0.147
    },
    {
      "timestamp":"2022-08-29 14:30",
      "value":0.154
    },
    {
      "timestamp":"2022-08-29 15:00",
      "value":0.267
    },
    {
      "timestamp":"2022-08-29 15:30",
      "value":0.601
    },
    {
      "timestamp":"2022-08-29 16:00",
      "value":0.201
    },
    {
      "timestamp":"2022-08-29 16:30",
      "value":0.447
    },
    {
      "timestamp":"2022-08-29 17:00",
      "value":0.41
    },
    {
      "timestamp":"2022-08-29 17:30",
      "value":0.15
    },
    {
      "timestamp":"2022-08-29 18:00",
      "value":0.124
    },
    {
      "timestamp":"2022-08-29 18:30",
      "value":0.119
    },
    {
      "timestamp":"2022-08-29 19:00",
      "value":0.33
    },
    {
      "timestamp":"2022-08-29 19:30",
      "value":0.215
    },
    {
      "timestamp":"2022-08-29 20:00",
      "value":0.491
    },
    {
      "timestamp":"2022-08-29 20:30",
      "value":0.611
    },
    {
      "timestamp":"2022-08-29 21:00",
      "value":0.231
    },
    {
      "timestamp":"2022-08-29 21:30",
      "value":0.241
    },
    {
      "timestamp":"2022-08-29 22:00",
      "value":0.219
    },
    {
      "timestamp":"2022-08-29 22:30",
      "value":0.273
    },
    {
      "timestamp":"2022-08-29 23:00",
      "value":0.172
    },
    {
      "timestamp":"2022-08-29 23:30",
      "value":0.126
    },
    {
      "timestamp":"2022-08-30 00:00",
      "value":0.123
    }
  ],
  "availableCacheRange":{
    "start":"202107281630",
    "end":"202208300400"
  },
  "unit":"kWh"
}

I then use a function node to process this data into a format suitable for InfluxDB 2:

[
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.148
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661733000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.122
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661734800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.147
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661736600000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.117
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661738400000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.112
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661740200000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.147
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661742000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.119
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661743800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.137
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661745600000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.121
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661747400000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.114
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661749200000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.143
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661751000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.111
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661752800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.106
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661754600000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":1.023
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661756400000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.157
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661758200000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.153
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661760000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":1.098
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661761800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.134
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661763600000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.17
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661765400000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.265
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661767200000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.166
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661769000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.156
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661770800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.134
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661772600000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.349
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661774400000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.152
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661776200000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.135
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661778000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.172
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661779800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.147
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661781600000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.154
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661783400000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.267
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661785200000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.601
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661787000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.201
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661788800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.447
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661790600000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.41
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661792400000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.15
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661794200000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.124
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661796000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.119
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661797800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.33
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661799600000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.215
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661801400000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.491
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661803200000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.611
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661805000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.231
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661806800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.241
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661808600000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.219
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661810400000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.273
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661812200000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.172
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661814000000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.126
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661815800000000000
  },
  {
    "measurement":"electricity_supply",
    "fields":{
      "kWh_in":0.123
    },
    "tags":{
      "type":"electricity_consumed"
    },
    "timestamp":1661817600000000000
  }
]

I then use an influx batch node to send this data to InfluxDB.

image
image1136×107 11.6 KB

Looking at the formatted data that is sent to InfluxDB, the timestamp associated with a kWh_in value of 1.098 is 1661761800000000000 (in nanoseconds), which translates to Monday, 29 August 2022 09:30:00 UTC+01:00. So the processing in Node-RED seems to be correct.

InfluxDB, Grafana and Node-RED are all running in separate Docker containers on the same physical server.

  • Server timezone is UTC+01:00 (my local time)
  • InfluxDB container’s timezone is UTC
  • Grafana container’s timezone is UTC
  • Node-RED container’s timezone is UTC+01:00. I don’t know if you’re familiar with Node-RED, but if I tell it to output $now() it returns 2022-08-30T17:00:00.000Z, which is the correct UTC.

The data seems to get into InfluxDB absolutely correctly, as the timestamps all match. There seems to be some discrepancy between InfluxDB and Grafana…

Thanks for your efforts!

Chris

11

ohoh maybe there is a disturbance in the space time continuum where you are at. you are 10 minutes behind!!! :scream: :running_man:

ok will load your data on my influxdb and see what is up.

12

I checked the data again this morning, after Node-RED loaded a new batch into InfluxDB, and the times have all changed from :10 and :40 to :00 and :30, and now everything is working exactly as it should :man_facepalming:

I am really sorry to have wasted people’s time on this - it definitely looks like it was initially an issue with the data provided by the energy monitoring service, possibly due to my only signing up with the service a day before finding this issue with the timestamps.

All the best,

Chris

2 Likes