MySQL database history is in the future?

Configuration
1

Hi All,

I have a sensor that I’m looking at the history of and noticed that I have state entries for the future. Can anyone point me in the direction on why this is?

Screen Shot 2021-03-18 at 1.39.39 PM
Screen Shot 2021-03-18 at 1.39.39 PM1894×794 266 KB

1 Like
2

First guess is some inconsistence in time zone settings, thus 4 hour offset.
I suspect HA is not using database NOW() function to store time. It passes explicit value of the time.
If I am right, then there timezone of HA is set wrong.

Check in Configuration/Info/SystemHealth if timezone matches your location.

3

The HA timezone is correct for me – New York. I just realized that the +4 hours equals UTC time. So, it looks like the inserts are using UTC. I’m no DBA, so I don’t know where I make that change at!

4

You would need to set the timezone in MariaDB. I believe you can do that in the add-on settings (though I’m not completely certain about that).

5

Thanks, Bill. Even if the system timezone is correct and mysql is using that?

root@hass:/docker/automation/includes# date
Thu Mar 18 14:12:42 EDT 2021
root@hass:/docker/automation/includes# cat /etc/timezone 
America/New_York

MariaDB [hass]> SELECT @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM             |
+--------------------+
1 row in set (0.000 sec)

MariaDB [hass]> SELECT @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| SYSTEM              |
+---------------------+
1 row in set (0.000 sec)

MariaDB [hass]> SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------------------+
| TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------------------+
| -04:00:00                      |
+--------------------------------+
1 row in set (0.000 sec)

MariaDB [hass]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2021-03-18 14:15:04 |
+---------------------+
1 row in set (0.000 sec)
6

AFAIK, yeah because MariaDB is in a docker container, it has it’s own timezone setting. However, looking at your query, it seems that MariaDB is set properly.

Is it doing this regularly (times set in the future) or just upon start-up? I’m wondering if maybe there’s something going on with a weird NTP call or if it was just a one off thing.

7

I’m running Mariadb the ole’ fashioned way and directly on the server - not in a container.

It is doing this all the time. Looking at any state will show the latest created date as +4 hours from my local time / utc time.

Clearly the HA UI knows how to handle this, because all states show the correct time when viewed through there. I’m trying to create some sql sensors that are based on time and dates and I’m getting 4 hours from the previous day in my calculations. Not a big deal, but I’m curious about how to fix it!