Relative time of 1 week should start with complete day

Grafana InfluxDB
1

  • What Grafana version and what operating system are you using?
    9.3.2 on a raspberry pi 4 with Flux language.

  • What are you trying to achieve?
    Show the energy consumption of the last 7 days.

  • What happened?
    I configure the panel as the following.
    But it shows me always 8 days (the last day two times)
    I think it starts always 7 days back from now.

    image
    image1317Ă—812 65.1 KB

  • What did you expect to happen?
    I want to start with the last 7 days start with today and show only complete days.
    That means today and the last 6 days and nothing more.

I hope I explained my problem as good as possible. Please let me know I you need more informations.

2

Welcome @peter54879

Does this help?

3

Hi @peter54879,

Correct. Instead of using 7d try using 7d/d so that time starts from beginning of the day.

 

Best regards,
ldrascic

4

By adding this code the last day like today 10.04. is shown two times as well. With the code |> group() the view of the panel change to a table.

5

As is understand it is not working by using flux language.

6

Using Flux, I think this will do the trick (cannot test at the moment, so the syntax may be slightly wrong)"

import "date"
import "timezone"

from(bucket: "your-bucket")
  |> range(start: -7d, stop: date.truncate(t: now(), unit: 1d))

But I think changing the relative time (as @ldrascic suggested) to 7d/d may be easier and result in your desired output:

image
image929Ă—758 47.4 KB

7

I am not sure what I do wrong but by using 7d/d is shows me one more day and at the end the last day always two times.

image
image1317Ă—832 70.4 KB

8

Is your timestamp utc in your data?

9

The Dashboard is set to UTC+2

10

“The Dashboard is set to UTC+2”

That was not the question.

Are the timestamps in your datasource UTC, or something else?

Hint: if it’s something else, that’s probably your problem - data should
always be stored in UTC and then later displayed in “local time”.

Antony.

1 Like
11

In this case i have to manipulate the timestamp direktly in Node Red?
I read out the data by modbus in Node Red and the write it direktly to influxdb.

12

I believe that if you are using any of these nodes and are not specifying the timestamp, then as you read the modbus data and send to InfluxDB, the timestamp will be in UTC. This is what I use and all my timestamps are stored in UTC.

Have you gone to InfluxDB directly (http://your-ip-address:8086) and looked at a table of your data to inspect what time is being used? See last column in the screenshot below.

image
image1906Ă—889 120 KB

13

The time in my InfluxDB Data Explorer looks like UTC time. In Germany we have at the moment UTC+2 and the time stamp is 2 hours earlier then the actual time.

14

Looks like you might more than one reading on that date

How often do you write data?

15

In the csv export from InfluxDB I see every second a new value. Strange is that I read every 3 seconds the modbus interface and the config on InfluxDB node for Time Precision is set to Seconds (s). In the csv export there is for every second a new value. For sure changed only a little bit but it is changed.

16

That is the best solution until now. The only problem i have is that it looks like the first day in the table is only shown only with the data from the time right now until midnight. This screenshot is from today around 9:30 pm. The energy on this day is about 14 kWh.

image
image1336Ă—872 62.4 KB

17

@peter54879

Can you share the full query? Does it have an |> aggregateWindow function?

18

image
image1323Ă—868 54.8 KB

The last day is also filled with wrong data. For me would be also fine if i could see the last 7 days without the actual day.

19

Hi @peter54879

Can you try the timeSrc option in your aggregateWindow function and see if that helps, like this?

|> aggregateWindow(every: 1d, fn: spread, createEmpty: false, timeSrc: "_start")

More explanation here.

20

I have one table that is nearly what I want.
The problems are that I use the unit 1w to see the last 7 days. In this case is shows me only 3 days. If I change it to 1 mo for the last month it is working normaly. Also the performance is very slow with this query.

image
image1336Ă—922 57.3 KB