mysql_tzinfo_to_sql — Load the Time Zone Tables

[dennissiemensma]

Originally reported by: Dennis Siemensma (Bitbucket: dennissiemensma, GitHub: dennissiemensma)

---

Django mentions somewhere mysql_tzinfo_to_sql — Load the Time Zone Tables. We should check whether MySQL data is stored timezone aware.

If not, we should mention the required steps in the installation guide of this project.

---

• Bitbucket: https://bitbucket.org/dennissiemensma/dsmr-reader/issue/35

[dennissiemensma]

Completed my fake datalogger, which I can use to have one running on a Pi with MySQL, disabling the need for a P1 port.

[dennissiemensma]

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Takes a minute or so.

Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

[dennissiemensma]

Well it seems that as long as the system's time is set to UTC, things go well. However, it's not a guarantee that everyone sets UTC as their system timezone.

[dennissiemensma]

Follow-up check is checking (day) boundaries. I'll have my fake datalogger run for the next day, so I can check ranges tomorrow.

[dennissiemensma]

Seems to work fine:

WHERE (`dsmr_consumption_electricityconsumption`.`read_at` < 2016-02-06 23:00:00 AND `dsmr_consumption_electricityconsumption`.`read_at` >= 2016-02-05 23:00:00) ORDER BY `dsmr_consumption_electricityconsumption`.`read_at` ASC

So Django selects 23:00 tot 23:00, which is expected (data is stored UTC).

When I make the selection naive:

WHERE (`dsmr_consumption_electricityconsumption`.`read_at` >= 2016-02-06 00:00:00 AND `dsmr_consumption_electricityconsumption`.`read_at` < 2016-02-07 00:00:00) ORDER BY `dsmr_consumption_electricityconsumption`.`read_at` ASC

Midnight UTC, which is wrong. Also, Django complains:

RuntimeWarning: DateTimeField GasConsumption.read_at received a naive datetime (2016-02-06 00:00:00) while time zone support is active.

The only thing is, I just added timezone tables before testing. I'll revert the database and test again, without them.

[dennissiemensma]

It seems Django performs the conversion, so it should be OK, as long as UTC is set as SYSTEM time.