Dates on the x-axis don't match dates when hovering over plot

[david00]

Hi, I have encountered an issue I don’t understand and am looking for guidance.

First off, I’m running InfluxDB version 1.8.3 and Grafana 7.3.7, both in Docker containers.

I’ve captured a screenshot of the issue showing as much of the setup as I can, including the issue, the time range, the queries, and the group by setting.

The problem is that the x-axis has dates that don’t match the date when I hover over the actual plot. As pictured, the x-axis label shows 04/11, but hovering over the plot shows 4/10 17:00:00.

Data is sourced from my own application and timestamps are sent to InfluxDB in UTC. The client accessing the dashboard is configured for the PST timezone as you can see in the time range selection box.

The dashboard is supposed to show figures from the database beginning at 00:00 and ending at 23:59 for each day. From my understanding the /d in the time range selection should accomplish this, and the group by 24h or group by 1d should both start at 00:00 and end at 00:00 the following day.

The sentiment here on related threads about timezones basically just seems to be “write your data in UTC timestamps and you’ll be fine”, so I’m not sure what’s going on.

Interestingly enough, I changed the timezone in the dashboard to UTC, and the x-axis dates now match when hovering. See below:

Here is what the latest data looks like from the shell:

> select * from rp_5min.home_energy_5m order by time desc limit 5;
name: home_energy_5m
time                energy
----                ------
1619243400000000000 0.07275790056125306
1619243100000000000 0.0720926660989204

Aside from all the annoying extra zeros on the timestamp, it does appear to be a UTC timestamp, and it’s within the last 10 minutes at the time of writing.

Thank you for your time and any feedback!

[svetb]

For bar charts, the left edge of the bar is aligned to the time stamp of the data (which you see when you hover over the bar), so the bar essentially appears to the right of the time stamp it represents. I think that explains what you’re seeing, but let me know if I’m not making sense?

Time zones shouldn’t have much to do with this behavior.

FWIW, the left-align behavior has changed a couple of times in different versions - see e.g. Graph: Revert bar centering to fix tooltip issue by torkelo · Pull Request #26876 · grafana/grafana · GitHub and references therein. Since 7.4 there’s also an option to determine centering, but only for the new “time series” visualization type: Graph time series as bars | Grafana Labs

[david00]

Hi svetb, thanks for your feedback. The issue isn’t really that the bars aren’t aligned - it’s that the popup shows 17:00:00 when hovering over the data, which makes me think that Grafana is grouping the data together incorrectly.
What I mean is, instead of showing me a single day’s worth of data in my timezone, I think it’s actually showing me data from 17:00 local time of one day to 17:00 local time the next day:

Looking at the raw data via Grafana’s Query Inspector, I see timestamps that represent 00:00 UTC or 17:00 PST:

name:"home_energy_5m"
columns:Array[2]
values:Array[31]
  0:Array[1616630400000,7.7985095857770546]
  1:Array[1616716800000,28.068126438943867]
  2:Array[1616803200000,25.954646117659085]
  3:Array[1616889600000,25.379337032730337]
  4:Array[1616976000000,24.959306342737925]

I just want to understand why Grafana is showing 17:00 in the popup/hover.

My timezone is UTC-7. So, for example, April 25th at 00:00 UTC represents April 24th 17:00 PST. It makes sense to me that the 17:00 is coming from the UTC offset, but what I fail to understand is what exactly the data represents that Grafana is showing me.

If I zoom in, the date on the x-axis suggests the data is for the date listed at 00:00. Here’s another photo of what I’m trying to portray:

I positioned my mouse over the bar labeled by 04/21 00:00 and I think great, this bar represents the data beginning from 04/21 00:00 local time and it’s grouped by 1d, so it represents that day’s data. However, when I hover over the bar, the popup suggests the time is actually 4/20 17:00:00, which makes me now wonder if the bar is representing data from 4/20 17:00 to 4/21 17:00.

So, my questions are:

1. What time range is the bar actually representing? Is it 00:00 to 00:00 local time, or 17:00 to 17:00 local time?

2. Why is Grafana showing me two different times for the same representation of the data?

3. How can I fix this confusing nature and be certain that I am visualizing the data for the time range that I intend to? (Which is the 24 hour period from 00:00 to 00:00 local time)

[svetb]

Hi @david00 thanks for the clear explanation.

To your specific questions:

1. What time range is the bar actually representing? Is it 00:00 to 00:00 local time, or 17:00 to 17:00 local time? – I’m assuming you have a GROUP BY time(1d) or similar in your Influx query - and Influx by default will group according to UTC date boundaries. So the boundary will be 00:00 UTC or 17:00 local time. Influx will return each day’s data timestamped at 00:00 UTC, which Grafana will visualize as 17:00 local time.

2. Why is Grafana showing me two different times for the same representation of the data? –
   it’s actually not; this is what I was trying to point out in my original post. I agree it’s slightly counter-intuitive, but bear with me. The timestamp of each of the bars is 17:00 local time - which is indeed the timestamp that you see when you hover over the bar. But what leads to confusion is that the left edge of the bar is aligned to 17:00. In other words, the bar is not centered on 17:00, but appears to the right of the 17:00 mark. So in your case it appears to be (roughly) centered on the 00:00 mark, but that’s just pure coincidence. And when you hover over any part of the bar, what Grafana shows you is the timestamp and value for that bar, which is 17:00 on the previous day.

3. How can I fix this confusing nature and be certain that I am visualizing the data for the time range that I intend to? (Which is the 24 hour period from 00:00 to 00:00 local time ) – I think you need to do two things:

• First, get your Influx query to use the time boundaries you actually want to use. Keep in mind this is purely in Influx’s court, and Grafana has nothing to do with this bit. You should be able to do this by adding an offset of +7h to your GROUP BY time() clause - check out the docs here
• If you want Grafana to center the bars on each timestamp, rather than left-align them, take a look at the links in my previous post (especially the last one).

Hope that helps!

[david00]

@svetb, thanks again. Your answers really helped clarify my confusion and now I am confident in diving deeper into this. Also I’m glad you thought my explanation was clear - it was kind of difficult to portray given the counter-intuitive nature.

I now see that the left edge of the bar is aligned to 17:00. I was previously thinking it was centered on 00:00 (which is what I expected) - boy what a coincidence!

Yes, I have a group by time(1d) in the query. It appears the resolution is to - like you said - add an offset in the InfluxDB query. InfluxDB supports the tz() parameter in the query. It’s listed on the link that you sent in your previous post. (This should account for daylight savings time too as opposed to a fixed integer offset). I added a dashboard variable for the timezone so I only have to set the timezone string in one place. Here is what the query looks like for anyone reading this in the future:
SELECT sum("energy") FROM "rp_5min"."home_energy_5m" WHERE $timeFilter GROUP BY time(1d) tz('${timezone}')

The timezone variable is defined like so:

… where the timezone string is selected from the official IANA timezone database posted on Wikipedia:

List of tz database time zones | List

With the UTC offset applied, now I see why the labels not being centered on the bar is one of those annoying quirks:

I will look into the potential workarounds you shared previously if it bugs me.

Thanks so much for your assistance! I am good to go now and I’m super glad we were able to clear that up.