mahara-contributors team mailing list archive

[Craig Miskell <1187212@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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()) {
         $db->Execute("SET SQL_MODE='POSTGRESQL'");
+        if(!empty($CFG->dbtimezone)) {
+           $db->Execute("SET time_zone='$CFG->dbtimezone'");
+        }
     }

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).

** Affects: mahara
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contrib members
https://bugs.launchpad.net/bugs/1187212

Title:
  Handle timezone mismatch between webserver and DB (MySQL) server

Status in Mahara ePortfolio:
  New

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()) {
           $db->Execute("SET SQL_MODE='POSTGRESQL'");
  +        if(!empty($CFG->dbtimezone)) {
  +           $db->Execute("SET time_zone='$CFG->dbtimezone'");
  +        }
       }

  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).

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1187212/+subscriptions