Handle timezone mismatch between webserver and DB (MySQL) server
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Mahara |
Fix Released
|
Medium
|
Aaron Wells |
Bug Description
If the timezone of the MySQL DB server doesn't match the timezone of the webserver (e.g. you're in AWS, where the DB server is always unchangeably UTC, and you want the webserver to be in a reasonable timezone for your users), then you get odd offset issues in, for example, the timestamps on forum posts.
The webserver takes "now" in it's local timezone, formatted as a date/time (text), and puts it into the INSERT statement. The DB sees that, interprets it as the formatted date/time, but in it's time zone (UTC), which is some number of hours offset from the actual time. It stores that as a unix epoch time (seconds from 00:00:00 1-1-1970). When this is sent back to the client, the epoch time is converted back to the local timezone, and it displays as some number of hours offset.
The simplest solution is to set the timezone on the MySQL connection, forcing the text date/time values to be interpreted as being in that timezone; they are stored correctly in epoch format, which will then display properly on viewing. It's a simple patch:
--- lib/dml.php.orig 2013-05-22 22:33:27.229452915 -0400
+++ lib/dml.php 2013-05-22 22:35:44.403280333 -0400
@@ -1474,6 +1474,9 @@
if (is_mysql()) {
+ if(!empty(
+ $db->Execute("SET time_zone=
+ }
}
It requires you to set dbtimezone in config.php, to the timezone of the server (so maybe it's a poorly named option; I'm open to alternative suggsions).
Changed in mahara: | |
milestone: | none → 1.8.0rc1 |
assignee: | nobody → Aaron Wells (u-aaronw) |
importance: | Undecided → Medium |
status: | New → Triaged |
status: | Triaged → In Progress |
Changed in mahara: | |
milestone: | 1.8rc1 → 1.8.0 |
Changed in mahara: | |
milestone: | 1.8.0 → 1.9.0 |
tags: | added: nominatedfeature |
Changed in mahara: | |
status: | Fix Committed → Fix Released |
Loaded into gerrit: https:/ /reviews. mahara. org/2257
I think Moodle avoids this whole issue by converting dates to Unix epochs and then storing them as integers.
Since this is only like to happen in power-user situations, it may be enough to just add a $cfg option (and document it in config- defaults. php). According to the MySQL manual, you can provide the timezone as a UTC offset. So perhaps we could auto-detect the web server's timezone in PHP, and then decide what timezone to send based on that?