Weird timezone problems

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?

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.

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!

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.

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.

1 Like

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.