I have a big Problem with the X-Axis

Hello dear community,
I have the following problem in all my dashboards with the display on the X axis. I would like the days or the date to be displayed continuously on the X-axis. Unfortunately, for me, some days or dates are displayed twice or more than once.
I have uploaded a screenshot here as an example so that you can see exactly what I mean.
How do I get it to show only one date on the x-axis? I’ve already tried everything possible, but I can’t get it. Unfortunately, I don’t know anything about script commands.
Can you please explain to me how I can do that.

Greetings Andy

Could you please provide an export of that data as csv or provide them here inline something similar to this (obfuscated of course)

table,_start,_stop,_time,_value,_field,_measurement,uic
0,2022-07-08T23:26:22.938437362Z,2022-07-08T23:27:22.938437362Z,2022-07-08T23:26:24Z,494,gpsaltitude,test,111
0,2022-07-08T23:26:22.938437362Z,2022-07-08T23:27:22.938437362Z,2022-07-08T23:26:25Z,494,gpsaltitude,test,111
0,2022-07-08T23:26:22.938437362Z,2022-07-08T23:27:22.938437362Z,2022-07-08T23:26:26Z,494,gpsaltitude,test,111```

I would like to do that, but I don’t know how to create such a .csv. For me it looks completely different than your example.
I use Grafana 8.5.5

That was just a sample. You can just type it here in the forum or go to influxdb and dowbload it as csv after you run the same query you are showing us here

basically the issue seems to be you have 2 readings per day. and they seem to be identical readings to the naked eye :eye:

Okay, here is the download from my infux

#group	false	false	true	true	false	false	true	true
#datatype	string	long	dateTime:RFC3339	dateTime:RFC3339	dateTime:RFC3339	double	string	string
#default	mean							
	result	table	_start	_stop	_time	_value	_field	_measurement
		0	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-26T22:30:00Z	18.15	value	Drucker_gestern
		0	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-27T22:30:00Z	0	value	Drucker_gestern
		0	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-28T22:30:00Z	0	value	Drucker_gestern
		0	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T11:00:00Z	0	value	Drucker_gestern
		0	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T22:30:00Z	0	value	Drucker_gestern
		0	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T21:00:00Z	0	value	Drucker_gestern
		0	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T22:30:00Z	19.13	value	Drucker_gestern
		0	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-31T22:30:00Z	0	value	Drucker_gestern
		0	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-08-01T07:00:00Z	0	value	Drucker_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-26T22:30:00Z	894.4	value	FeFo_TV_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-27T22:30:00Z	664.41	value	FeFo_TV_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-28T22:30:00Z	783.93	value	FeFo_TV_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T11:00:00Z	783.93	value	FeFo_TV_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T22:30:00Z	543.48	value	FeFo_TV_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T21:00:00Z	543.48	value	FeFo_TV_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T22:30:00Z	409.65	value	FeFo_TV_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-31T22:30:00Z	953.22	value	FeFo_TV_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-08-01T07:00:00Z	953.22	value	FeFo_TV_gestern
		1	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-08-01T22:30:00Z	606.02	value	FeFo_TV_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-26T22:30:00Z	413.7	value	Imac_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-27T22:30:00Z	302.4	value	Imac_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-28T22:30:00Z	612.64	value	Imac_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T11:00:00Z	612.64	value	Imac_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T22:30:00Z	662.4	value	Imac_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T21:00:00Z	662.4	value	Imac_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T22:30:00Z	730.17	value	Imac_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-31T22:30:00Z	300.96	value	Imac_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-08-01T07:00:00Z	300.96	value	Imac_gestern
		2	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-08-01T22:30:00Z	1021.44	value	Imac_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-26T22:30:00Z	832.73	value	Kuehlschrank_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-27T22:30:00Z	722.04	value	Kuehlschrank_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-28T22:30:00Z	736.38	value	Kuehlschrank_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T11:00:00Z	736.38	value	Kuehlschrank_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T22:30:00Z	681.73	value	Kuehlschrank_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T21:00:00Z	681.73	value	Kuehlschrank_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T22:30:00Z	752.04	value	Kuehlschrank_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-31T22:30:00Z	823.55	value	Kuehlschrank_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-08-01T07:00:00Z	823.55	value	Kuehlschrank_gestern
		3	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-08-01T22:30:00Z	724.55	value	Kuehlschrank_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-26T22:30:00Z	221.5	value	Ventilator_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-27T22:30:00Z	83.53	value	Ventilator_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-28T22:30:00Z	57.6	value	Ventilator_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T11:00:00Z	57.6	value	Ventilator_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-29T22:30:00Z	71.77	value	Ventilator_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T21:00:00Z	71.77	value	Ventilator_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-30T22:30:00Z	49.26	value	Ventilator_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-07-31T22:30:00Z	54.1	value	Ventilator_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-08-01T07:00:00Z	54.1	value	Ventilator_gestern
		4	2022-07-26T19:48:12.136000766Z	2022-08-02T19:48:12.136000766Z	2022-08-01T22:30:00Z	55.38	value	Ventilator_gestern
1 Like

So that now I have your data in my influxdb (avoids the back and forth questions/confusions) What do you want grafana to do with these?
same date different data.

I would like to display the daily power consumption in Grafana, but only have one date on the x-axis, then the next, etc. It bothers me that some dates appear several times on the X-axis and I hope that you can adjust it so that each date only appears once. I hope I have made myself clear.
Thank you for your help.

so from the given data sample, where 2 appear per day, which one do you want? The smallest, average, largest?

I want the small like 07/29 07/30 07/31

Again, there are 2 values still with that date format which of these values, do you want to keep and plot

Your initial issue was 2 of the same dates plotted. So even if you changed that format it won’t exclude the second value

Just changing the date format won’t magically fix the issue of 2 values in ssme date

Sorry, but now it’s getting a little too complicated for me. I honestly admit that. If there is a possibility that only one date is displayed on the x-axis, I would follow your suggested solution, since I also have the problem in other dashboards.

@yosiasz @damrak2022
i think if you only want one value by day you can still group by day and use
|>last() function to get last value of each group

@alexandrearmand can you try that theory out using the data provided by @damrak2022 ? Here is a python script way of importing that csv

import pandas as pd
from influxdb_client import InfluxDBClient, Point, WritePrecision, WriteOptions
from influxdb_client.client.write_api import SYNCHRONOUS
from datetime import datetime
from dotenv import load_dotenv
import os
load_dotenv()

URL = os.getenv('URL')
TOKEN = os.getenv('TOKEN')
ORG = os.getenv('ORG')

bucket = "DBANDY1"

"""
Write data into InfluxDB
"""
client = InfluxDBClient(url=URL, token=TOKEN, org=ORG, debug=True)
write_api = client.write_api(write_options=SYNCHRONOUS)  

col_list = ["_time","_value","_field","_measurement"]
df = pd.read_csv("./data/dammrak.csv", usecols=col_list)

for index, row in df.iterrows():   
    _time =  row['_time']
    _field =  row['_field']
    _measurement =  row['_measurement']
    _value = int(row['_value'])

    point = Point(_measurement) \
            .field(_field, _value) \
            .time(_time, WritePrecision.NS)

    write_api.write(bucket, ORG, point) 
write_api.__del__()

"""
Close client
"""
client.__del__()
1 Like

here is my result :


with this query :

from(bucket: "DBANDY1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Ventilator_gestern" or r["_measurement"] == "Kuehlschrank_gestern" or r["_measurement"] == "Imac_gestern" or r["_measurement"] == "FeFo_TV_gestern" or r["_measurement"] == "Drucker_gestern")
  |> filter(fn: (r) => r["_field"] == "value") 
  |> window(every: 1d)
  |> group(columns: ["_start","_measurement"])
  |>last()
  |> group(columns: ["_measurement"])

equivalent to this single line
|> aggregateWindow(every: 1d, fn: sum)

edit : this is _stop field to get coherent Xaxis

i think you should check time series panel too with this kind of query ,
don’t you like think of stacked bar , you can see sum of all by one look

awesome! now filter down to FeFo_TV_gestern by clicking on legend and hover over values on 7/29 and post back please

Thanks!

Thanks again for your help. Since I translate everything with Google Translator, I may not have fully understood it yet.
In any case, I like the first panel better, where the bars are visible next to each other.
If I understand correctly, then I have to use this query
from(bucket: “DBANDY1”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Fan_yesterday” or r[“_measurement”] == “Fridge_yesterday” or r[“_measurement”] == “Imac_yesterday” or r[" _measurement"] == “FeFo_TV_yesterday” or r[“_measurement”] == “Printer_yesterday”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> window(every: 1d)
|> group(columns: [“_start”,“_measurement”])
|>last()
|> group(columns: [“_measurement”])

And then, after customizing it, I can apply that to my other boards too, where I only want to have a date on the x-axis.
I’ll try that right now.

Now I’ve inserted the query and validated it with Appy and now my chart looks like this. Apparently I’ve done something terribly wrong here, or where is my mistake

image

1 Like

Could you please be so kind and post me the entire code, which I then have to insert in the query. Then I would have a base that I could write down to adapt to other boards. That was really nice of you