Weird timezone problems

[jrucker]

I’ve searched and searched, and haven’t been able to find a solution to this particular timezone problem. I don’t understand what’s wrong, or how to fix it.

Grafana version is v9.1.5

Here’s the setup:
I’ve got a mysql DB on the same VM grafana is installed on.
The timezone on the VM is GMT-7 (confirmed by running date)
The timezone in mysql is GMT-7 (confirmed by looking at configs and running select NOW())
The timezone in grafana is GMT-7 (confirmed by looking at configs both on the server side and in the browser UI)
The timezone in my browser is GMT-7 (confirmed by some random website that displays time on client side)

The table I’m looking at in the DB has a datetime datatype column (called date), which I’m using for a timeseries graph. My query is as follows:

SELECT
  date AS "time",
  weather_attribute,
  measurement
FROM log_weather
WHERE
  $__timeFilter(date)
  and weather_attribute in ('Garage Temp', 'Outside Temp', 'Pumphouse Temp', 'Shop Temp')
ORDER BY date

The graph shows up to 7 hours ago, despite the database including values up to now (this is with the time range set to “last 2 days”:

If I manually set the absolute time range to end at now+7h then it shows data up to the correct time:

All of time timezones everywhere I can find are configured correctly.
Why are these graphs not displaying the previous 7 hours when the timeFilter is correctly set to “now”?
Is there some timezone setting somewhere I’m missing?

[jangaraj]

Golden rule: save metrics into DB in the UTC timezone = metric must have saved time in the UTC timezone. (for advanced DB users only: or use timestamp with time zone)

Grafana queries in UTC timezone and then move in the browser to the dashboard timezone (by default browse timezone).

Yes, you can hack your current setup with some workaround, but you will very likely have still a problems: e.g. your collague in different timezone will have different time diff as you, you will have a problem with Daylight saving time, … The easiest solution is to follow mentioned golden rule.

[jrucker]

Ahhhh, that… does ring a bell.

Okay, looks like I’ve got about 12 million rows to update. Wish I would have remembered that back when I built this thing.

Thanks for the reminder!

[yosiasz]

I would not recommend that approach. create a new date column with UTC, update this new column with proper UTC date.

Once you are 100% sure everything else that uses the database and the old non UTC column works, then kill the old column.

[jrucker]

This dashboard is the only thing that uses this DB, and I’m the only user that uses it.
Updating a day’s worth of rows for testing will be adequate, I think.

[jrucker]

Hey, uhhh, I just got a chance to look at this, and I am saving metrics in the date column in UTC, so something else is wrong here.
In the table below, the date column is the one I’m using in the query above. The created_date column is mostly used for debugging, as sometimes there is a delay in when the sensor records the value and when it’s sent to the db. created_date is being recorded in the local timezone, date is being recorded in UTC

+---------+---------------------+----------------------------+-------------+--------------+---------------------+
| ID      | date                | weather_attribute          | measurement | weather_unit | created_date        |
+---------+---------------------+----------------------------+-------------+--------------+---------------------+
| 9215761 | 2022-09-23 23:04:17 | Pumphouse Temp             |       69.24 | F            | 2022-09-23 16:04:18 |
| 9215762 | 2022-09-23 23:04:24 | Outside Temp               |       71.60 | F            | 2022-09-23 16:04:25 |
| 9215763 | 2022-09-23 23:04:26 | Shop Temp                  |       71.71 | F            | 2022-09-23 16:04:27 |
| 9215764 | 2022-09-23 23:04:36 | Garage Temp                |       83.01 | F            | 2022-09-23 16:04:37 |
| 9215765 | 2022-09-23 23:04:36 | Garage Humidity            |       31.28 | %            | 2022-09-23 16:04:37 |
| 9215766 | 2022-09-23 23:04:36 | Garage Barometric Pressure |       30.59 | inHg         | 2022-09-23 16:04:37 |
+---------+---------------------+----------------------------+-------------+--------------+---------------------+

Also please note the two graphs in the original post. The data is correct in BOTH grahps, but the data being displayed is truncated by 7 hours in the first graph. This is not a matter of the data having the wrong timezone, it’s the dashboard not grabbing all of the data.

[jrucker]

I’ve continued researching this problem but haven’t been able to figure anything out. Any help would be appreciated

[yosiasz]

what timezone are you in?

[jrucker]

My local timezone is GMT-7

[yosiasz]

so somewhere in PST Seattle or whatever.
Now please share a screen shot of your grafana time filter

[jrucker]

I’m normally just using the default “Last 7 days”

However, as I mentioned in the original post, if I do to now+7h all of the data is populated, as seen in the second screenshot in the original post:

[yosiasz]

what do you have selected here?

it is working fine for me

[jrucker]

I have my browser timezone set as the default

The graph shows the correct timestamps for the data it displays, it just doesn’t show the last 7 hours:

I can only put two embedded images in a post, stand by for a followup

[jrucker]

If I change that time zone setting to UTC, it shifts the time shown on the graph, but it still doesn’t show the last 7 hours:

Here’s with the timezone set to browser timezone (UTC-7) with the time range set to now+7h
Note that not only is the timestamp correct, but there is 7 hours additional data than the two previous graphs (you can see the difference in the lines)
This is what I expect to see when the time zone is set to browser time, and the date range is set to end at now

[yosiasz]

@jrucker

Why are these graphs not displaying the previous 7 hours when the timeFilter is correctly set to “now”?

Which timeFilter are you referring above?

[jrucker]

This one:

[yosiasz]

Oh wait. I think I see what you mean now. I think you cant see them because of the default granularity of grafana in the below setting

[jrucker]

Here’s what I have set there:

[yosiasz]

keep playing with it to get what you want.

[jrucker]

That appears to break it worse. Setting a relative time removes everything before that time (in this case, -7 hours, - the configured relative time), but doesn’t add the missing 7 hours.