Date order error in a Bar Chart

Hello

I would like to now why my bar chart display an error in order of dates :

As you can see, Saturday and sunday are between monday and thursday of this month.

It can also be used for other days and dates.

I give you the json configuration :


{
  "datasource": {
    "type": "influxdb",
    "uid": "b383168e-2097-4f5b-a983-0d137adfb3aa"
  },
  "fieldConfig": {
    "defaults": {
      "custom": {
        "lineWidth": 1,
        "fillOpacity": 80,
        "gradientMode": "none",
        "axisPlacement": "auto",
        "axisLabel": "",
        "axisColorMode": "text",
        "axisBorderShow": false,
        "scaleDistribution": {
          "type": "linear"
        },
        "axisCenteredZero": false,
        "hideFrom": {
          "tooltip": false,
          "viz": false,
          "legend": false
        },
        "thresholdsStyle": {
          "mode": "line"
        }
      },
      "color": {
        "mode": "palette-classic"
      },
      "mappings": [],
      "thresholds": {
        "mode": "absolute",
        "steps": [
          {
            "color": "text",
            "value": null
          }
        ]
      }
    },
    "overrides": [
      {
        "matcher": {
          "id": "byFrameRefID",
          "options": "B"
        },
        "properties": [
          {
            "id": "unit",
            "value": "kwatth"
          }
        ]
      },
      {
        "matcher": {
          "id": "byFrameRefID",
          "options": "D"
        },
        "properties": [
          {
            "id": "displayName",
            "value": "IPE"
          },
          {
            "id": "unit",
            "value": "Wh/m²/°C"
          },
          {
            "id": "color",
            "value": {
              "fixedColor": "dark-green",
              "mode": "fixed"
            }
          }
        ]
      }
    ]
  },
  "gridPos": {
    "h": 8,
    "w": 11,
    "x": 9,
    "y": 34
  },
  "id": 50,
  "targets": [
    {
      "datasource": {
        "type": "influxdb",
        "uid": "b383168e-2097-4f5b-a983-0d137adfb3aa"
      },
      "hide": true,
      "query": "import \"timezone\"\r\n\r\noption location = timezone.location(name: \"Europe/Paris\")\r\n\r\nfrom(bucket: \"GIN\")\r\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\r\n  |> filter(fn: (r) => r[\"_measurement\"] == \"Consommation\")\r\n  |> filter(fn: (r) => r[\"_field\"] == \"Chauffage_chambre1\" or r[\"_field\"] == \"Chauffage_chambre2\" or r[\"_field\"] == \"Chauffage_cuisine\" or r[\"_field\"] == \"Chauffage_entree\" or r[\"_field\"] == \"Chauffage_salledebain\")\r\n  |> difference(nonNegative: true, initialZero: true, columns: [\"_value\"])\r\n  |> filter(fn: (r) => r[\"_value\"] > 0.01)\r\n  |> aggregateWindow(every: ${Lisser}, fn: sum, createEmpty: false, offset: -1s)\r\n  |> pivot(rowKey:[\"_time\"], columnKey: [\"_field\"], valueColumn: \"_value\")\r\n  |> map(fn: (r) => ({\r\n      r with\r\n      total_chauffage: \r\n        (if exists r.Chauffage_chambre1 then r.Chauffage_chambre1 else 0.0) +\r\n        (if exists r.Chauffage_chambre2 then r.Chauffage_chambre2 else 0.0) +\r\n        (if exists r.Chauffage_cuisine then r.Chauffage_cuisine else 0.0) +\r\n        (if exists r.Chauffage_entree then r.Chauffage_entree else 0.0) +\r\n        (if exists r.Chauffage_salledebain then r.Chauffage_salledebain else 0.0)\r\n  }))\r\n  |> keep(columns: [\"_time\", \"total_chauffage\"])\r\n",
      "refId": "A"
    },
    {
      "datasource": {
        "type": "influxdb",
        "uid": "b383168e-2097-4f5b-a983-0d137adfb3aa"
      },
      "hide": true,
      "query": "import \"timezone\"\r\n\r\noption location = timezone.location(name: \"Europe/Paris\")\r\n\r\n// Requête pour les autres champs de chauffage\r\nchauffage_others = from(bucket: \"GIN\")\r\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\r\n  |> filter(fn: (r) => r[\"_measurement\"] == \"Consommation\")\r\n  |> filter(fn: (r) => r[\"_field\"] == \"Chauffage_chambre1\" or r[\"_field\"] == \"Chauffage_chambre2\" or r[\"_field\"] == \"Chauffage_cuisine\" or r[\"_field\"] == \"Chauffage_entree\" or r[\"_field\"] == \"Chauffage_salledebain\")\r\n  |> difference(nonNegative: true, initialZero: true, columns: [\"_value\"])\r\n  |> filter(fn: (r) => r[\"_value\"] > 0.01)\r\n  |> aggregateWindow(every: ${Lisser}, fn: sum, createEmpty: false, offset: -1s)\r\n\r\n// Requête pour Chauffage_salon avec conversion en kWh\r\nchauffage_salon = from(bucket: \"GIN\")\r\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\r\n  |> filter(fn: (r) => r[\"_measurement\"] == \"Consommation\" and r[\"_field\"] == \"Chauffage_salon\")\r\n  |> difference(nonNegative: true, initialZero: false, columns: [\"_value\"])\r\n  |> filter(fn: (r) => r[\"_value\"] > 0.01)\r\n  |> aggregateWindow(every: ${Lisser}, fn: sum, createEmpty: false, offset: -1s)\r\n  |> map(fn: (r) => ({ r with _value: r._value * 0.001 })) // Conversion en kWh\r\n\r\n// Union des deux requêtes\r\nunion(tables: [chauffage_others, chauffage_salon])\r\n  |> pivot(rowKey:[\"_time\"], columnKey: [\"_field\"], valueColumn: \"_value\")\r\n  |> map(fn: (r) => ({\r\n      r with\r\n      total_chauffage: \r\n        (if exists r.Chauffage_chambre1 then r.Chauffage_chambre1 else 0.0) +\r\n        (if exists r.Chauffage_chambre2 then r.Chauffage_chambre2 else 0.0) +\r\n        (if exists r.Chauffage_cuisine then r.Chauffage_cuisine else 0.0) +\r\n        (if exists r.Chauffage_entree then r.Chauffage_entree else 0.0) +\r\n        (if exists r.Chauffage_salledebain then r.Chauffage_salledebain else 0.0) +\r\n        (if exists r.Chauffage_salon then r.Chauffage_salon else 0.0)\r\n  }))\r\n  |> keep(columns: [\"_time\", \"total_chauffage\"])\r\n",
      "refId": "B"
    },
    {
      "datasource": {
        "type": "influxdb",
        "uid": "b383168e-2097-4f5b-a983-0d137adfb3aa"
      },
      "hide": true,
      "query": "import \"timezone\"\r\n\r\noption location = timezone.location(name: \"Europe/Paris\")\r\n\r\nfrom(bucket: \"GIN\")\r\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\r\n  |> filter(fn: (r) => r[\"_measurement\"] == \"Meteo\")\r\n  |> filter(fn: (r) => r[\"_field\"] == \"DJC\")\r\n  |> aggregateWindow(every: ${Lisser}, fn: sum, createEmpty: false, offset: -1s)",
      "refId": "C"
    },
    {
      "datasource": {
        "name": "Expression",
        "type": "__expr__",
        "uid": "__expr__"
      },
      "expression": "$B*1000/80/$C",
      "hide": false,
      "refId": "D",
      "type": "math"
    },
    {
      "datasource": {
        "name": "Expression",
        "type": "__expr__",
        "uid": "__expr__"
      },
      "expression": "$B*1000/80/$C",
      "hide": false,
      "refId": "E",
      "type": "math"
    }
  ],
  "title": "IPE",
  "transformations": [
    {
      "id": "configFromData",
      "options": {
        "configRefId": "E",
        "mappings": [
          {
            "fieldName": "E",
            "handlerKey": "threshold1",
            "reducerId": "mean"
          },
          {
            "fieldName": "Time",
            "reducerId": "mean"
          }
        ]
      }
    },
    {
      "id": "formatTime",
      "options": {
        "timeField": "Time",
        "outputFormat": "ddd DD  MMM",
        "useTimezone": true,
        "timezone": ""
      }
    }
  ],
  "type": "barchart",
  "options": {
    "orientation": "auto",
    "xTickLabelRotation": 0,
    "xTickLabelSpacing": 100,
    "showValue": "auto",
    "stacking": "none",
    "groupWidth": 0.7,
    "barWidth": 0.97,
    "barRadius": 0,
    "fullHighlight": false,
    "tooltip": {
      "mode": "single",
      "sort": "none"
    },
    "legend": {
      "showLegend": true,
      "displayMode": "list",
      "placement": "bottom",
      "calcs": []
    }
  }
}

The “format time” transformation changes nothing

the problem does not appear on a “time series”

Thanks for your help

Can you please post your Influx query (no transformations) and the data returned (in CSV)?

That is the first query for the consumption (in kWh):

import "timezone"

option location = timezone.location(name: "Europe/Paris")

// Requête pour les autres champs de chauffage
chauffage_others = from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Consommation")
  |> filter(fn: (r) => r["_field"] == "Chauffage_chambre1" or r["_field"] == "Chauffage_chambre2" or r["_field"] == "Chauffage_cuisine" or r["_field"] == "Chauffage_entree" or r["_field"] == "Chauffage_salledebain")
  |> difference(nonNegative: true, initialZero: true, columns: ["_value"])
  |> filter(fn: (r) => r["_value"] > 0.01)
  |> aggregateWindow(every: ${Lisser}, fn: sum, createEmpty: false, offset: -1s)

// Requête pour Chauffage_salon avec conversion en kWh
chauffage_salon = from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Consommation" and r["_field"] == "Chauffage_salon")
  |> difference(nonNegative: true, initialZero: false, columns: ["_value"])
  |> filter(fn: (r) => r["_value"] > 0.01)
  |> aggregateWindow(every: ${Lisser}, fn: sum, createEmpty: false, offset: -1s)
  |> map(fn: (r) => ({ r with _value: r._value * 0.001 })) // Conversion en kWh

// Union des deux requêtes
union(tables: [chauffage_others, chauffage_salon])
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({
      r with
      total_chauffage: 
        (if exists r.Chauffage_chambre1 then r.Chauffage_chambre1 else 0.0) +
        (if exists r.Chauffage_chambre2 then r.Chauffage_chambre2 else 0.0) +
        (if exists r.Chauffage_cuisine then r.Chauffage_cuisine else 0.0) +
        (if exists r.Chauffage_entree then r.Chauffage_entree else 0.0) +
        (if exists r.Chauffage_salledebain then r.Chauffage_salledebain else 0.0) +
        (if exists r.Chauffage_salon then r.Chauffage_salon else 0.0)
  }))
  |> keep(columns: ["_time", "total_chauffage"])

There is the data (last 11 days):
2024-12-05 23:59:59

1.01 kWh

2024-12-06 23:59:59

23.1 kWh

2024-12-07 23:59:59

29.0 kWh

2024-12-08 23:59:59

29.4 kWh

2024-12-09 23:59:59

26.3 kWh

2024-12-14 23:59:59

42.9 kWh

2024-12-15 23:59:59

27.9 kWh

2024-12-16 21:24:55

35.9 kWh

2024-12-10 23:59:59

27.2 kWh

2024-12-11 23:59:59

29.1 kWh

2024-12-12 23:59:59

28.0 kWh

2024-12-13 23:59:59

26.5 kWh

That is the second query for the weather data (in °C)

import "timezone"

option location = timezone.location(name: "Europe/Paris")

from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Meteo")
  |> filter(fn: (r) => r["_field"] == "DJC")
  |> aggregateWindow(every: ${Lisser}, fn: sum, createEmpty: false, offset: -1s)

There is the data :
2024-12-05 23:59:59

16.4

2024-12-06 23:59:59

10.7

2024-12-07 23:59:59

15

2024-12-08 23:59:59

14.5

2024-12-09 23:59:59

13.8

2024-12-10 23:59:59

14.7

2024-12-11 23:59:59

14.9

2024-12-12 23:59:59

15.9

2024-12-13 23:59:59

16.2

2024-12-14 23:59:59

16.0

2024-12-15 23:59:59

15.0

If you change the above (in the 1st and 2nd queries) to:
|> aggregateWindow(every: 1d, fn: sum, createEmpty: false, offset: -1s)
how does that change your graph?

What choices exist for the variable Lisser?

Also, can you please share a screenshot of your graph settings (the right side of Grafana) and any transformations you have created?


Here are the possible choices of “Lisser”


I also noticed that it was doing the same on another graphic.

“91 Wh” is value of Monday, December 09 (It’s not really the value for this day, but given the relative time, it’s the value that corresponds to it.)

This is the query :

import "timezone"

option location = timezone.location(name: "Europe/Paris")

// Requête pour les autres champs de chauffage
chauffage_others = from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Consommation")
  |> filter(fn: (r) => r["_field"] == "Chauffage_chambre1" or r["_field"] == "Chauffage_chambre2" or r["_field"] == "Chauffage_cuisine" or r["_field"] == "Chauffage_entree" or r["_field"] == "Chauffage_salledebain")
  |> difference(nonNegative: true, initialZero: true, columns: ["_value"])
  |> aggregateWindow(every: ${Lisser}, fn: sum, createEmpty: false, offset: -1s)
  |> map(fn: (r) => ({ r with _value: float(v: r._value) * float(v: 1000) })) // Conversion en Wh

// Requête pour Chauffage_salon avec conversion en kWh
chauffage_salon = from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Consommation" and r["_field"] == "Chauffage_salon")
  |> difference(nonNegative: true, initialZero: false, columns: ["_value"])
  |> aggregateWindow(every: ${Lisser}, fn: sum, createEmpty: false, offset: -1s)

// Union des deux requêtes
union(tables: [chauffage_others, chauffage_salon])
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({
      r with
      total_chauffage: 
        (if exists r.Chauffage_chambre1 then r.Chauffage_chambre1 else 0.0) +
        (if exists r.Chauffage_chambre2 then r.Chauffage_chambre2 else 0.0) +
        (if exists r.Chauffage_cuisine then r.Chauffage_cuisine else 0.0) +
        (if exists r.Chauffage_entree then r.Chauffage_entree else 0.0) +
        (if exists r.Chauffage_salledebain then r.Chauffage_salledebain else 0.0) +
        (if exists r.Chauffage_salon then r.Chauffage_salon else 0.0)
  }))
  |> keep(columns: ["_time", "total_chauffage"])

And this is the transformations for this graph :

Hi @Ayno

Just to see if it makes a difference, can you ‘hard code’ the every: value in your aggregateWindow statements to be 1d (instead of the Lisser variable)?

Also, can you try removing or disabling this transformation (which I see is still in Alpha) and see if that makes any difference (the transformation works fine for me, but maybe it’s some conflict with French)?
image