Time recorded in data is off by 7 hours

Configuration
1

If I look at any given sensor, the timeline shows the appropriate time in the browser for events, such as “home” and “Away” for presence tracking.

However, the time being recorded in MySQL in the time fields is +7 hours.

Where would I look to solve this problem?

HASS.IO platform.

2

Is your time zone UTC - 7?

The HA back-end uses UTC. The front end converts this to local time for display.

1 Like
3

Not sure. Are you speaking of the time zone on the Pi3 or the timezone on the server?

The MySQL server reports @@global.time_zone as SYSTEM. Which should be right. That indicates that the problem is on the Pi3 or in the config for HASS.io. Where do I find that?

The time_zone in configuration.yaml is America/Phoenix.

Does this information mean anything: https://www.home-assistant.io/blog/2015/05/09/utc-time-zone-awareness/

4

Time zone specified in HA config.

= UTC - 7.

5

Right, but the times recorded in MySQL in the recorded time columns are all UTC, in other words, +7 hours ahead of Phoenix even though I have America/Phoenix as the time-zone.

6

That is correct. All the time storage and calculation done by HA is in UTC. Only when you ask it to be displayed on the web interface is it converted to your local time zone.

7

This is expected.

8

Crap…hmm…how would we build accurate reporting for MySQL data then?

9

What do you mean?

10

Okay, for example, if I run a query using PhpMyAdmin and want to export that data and then run some sort of report on it, the time is wonky, so I would imagine I need to do some sort of conversion back at some point.

11

Yes if you need to interpret the data in local time (ie how many times did I visit the garage on this calendar day) then you need to convert the times to localtime. But hey, you have a computer, maths is what they do.

12

Sallight! I shall now go learn more.

13

you may be able to set the timezone in phpmyadmin for reporting

14

And here’s an answer for Google Sheets:

Set the format of the field to time, then…

=B1+TIME(7,0,0)

15

Until daylight savings changes :slight_smile:

16

So, UTC will be an hour different right? Arizona doesn’t change. I could just put a condition on the cell calculation to look for that and adjust accordingly.

17

No, UTC stays the same, only the local offset changes during daylight savings. This is one of the reasons using UTC for the back end is easier.

18

We don’t change, so then I would presume nothing would change here.

19

That’s correct.