DB used: MariaDB; Home Assistant Wrote 4 Hours Ahead In MyS

Configuration
1

While in MySQL command, I noticed something that is 4 hours off:

...
| 2018-08-06 03:56:30 |    -35.85 |
| 2018-08-06 03:57:30 |    -35.97 |
| 2018-08-06 03:58:30 |    -36.08 |
| 2018-08-06 03:59:30 |     -36.2 |
| 2018-08-06 04:00:30 |    -36.31 |
| 2018-08-06 04:01:30 |    -36.43 |
| 2018-08-06 04:02:30 |    -36.54 |
| 2018-08-06 04:03:30 |    -36.65 |
| 2018-08-06 04:04:30 |    -36.76 |
| 2018-08-06 04:05:30 |    -36.88 |
| 2018-08-06 04:06:30 |    -36.99 |
| 2018-08-06 04:07:30 |    -37.09 |
| 2018-08-06 04:08:30 |     -37.2 |
| 2018-08-06 04:09:30 |    -37.31 |
| 2018-08-06 04:10:30 |    -37.42 |
| 2018-08-06 04:11:30 |    -37.52 |
| 2018-08-06 04:12:30 |    -37.63 |
| 2018-08-06 04:13:30 |    -37.73 |
| 2018-08-06 04:14:30 |    -37.83 |
| 2018-08-06 04:15:30 |    -37.93 |
+---------------------+-----------+
156 rows in set (0.01 sec)

MariaDB [home_assistant]> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2018-08-06 00:16:18 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [home_assistant]> \q
Bye
hass@home-assistant:~$ date
Mon Aug  6 00:16:26 EDT 2018
hass@home-assistant:~$

The time zone for my Linux container and Home Assistant are both set to America/New_York.

The recorder is configured as follows:

recorder:
  purge_interval: 0
  db_url: "mysql://hass:[redacted]@127.0.0.1/home_assistant?charset=utf8"
  exclude:
    domains:
      - automation
      - updater

Even if both Home Assistant and MySQL showed me the correct time, why is the recorder writing 4 hours ahead of time for it to not show up in Grafana?

I’m using just a plain LXC container. No docker or virtual environment. Grafana and Home Assistant are able to communicate with MariaDB just fine. And just to be sure:

configuration.yaml:

homeassistant:
  ...
  time_zone: America/New_York

And in Linux container command line:

hass@home-assistant:~$ ls -l /etc/localtime
lrwxrwxrwx 1 root root 36 Jul 16 10:44 /etc/localtime -> /usr/share/zoneinfo/America/New_York
2

I think what you are seeing is that HA always works on UTC time, and that is what gets stored in the database.

UTC is translated in to local time when displayed to the user.

3

Thanks.

This is what I did to get the data to show up properly in Grafana:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
mysql_tzinfo_to_sql --leap /usr/share/zoneinfo/America/New_York | mysql -u root mysql

Then in Grafana and as MySQL/MariaDB as your data source, wrap the convert_tz() function around a field as an example:

convert_tz(last_updated,"Etc/UTC","America/New_York")

And you’re good to go.

1 Like
4

Hey, could you give a detailed instruction on how you made Grafana display the data properly?

I use the “Grafana-hosted” version of Grafana because my Raspberry Pi Zero W does not support the plugin and I have a -02:00 hour offset (i live in Berlin, Germany).

Where did you enter your commands?

Greetings

5

I have not used Grafana since last year and I don’t have detailed instructions in how to get Grafana configured with MySQL. You have to input SQL statements manually since MySQL is dissimilar to InfluxDB.

6

Where did you enter those mysql commands?

7

I have not been using Grafana for a year now, but this documentation will get you started. You should look under Queries tab when you create a graph.