Dealing with timezones

[NotionCommotion]

I've read some blogs about the virtues of setting MySQL's timezone, and wanted to better understand how they worked.  At the time of running the following was 2018-02-27 20:16:22 UTC.  My conclusion is that it should never (or very rarely) be done unless the database is just being used to retrieve results.  Agree?

<?php
date_default_timezone_set('America/Los_Angeles');

$config=parse_ini_file(__DIR__.'/../config.ini',true);
$db = $config['mysql'];
$db=new \PDO("mysql:host={$db['host']};dbname={$db['dbname']};charset={$db['charset']}",$db['username'],$db['password'],array(\PDO::ATTR_EMULATE_PREPARES=>false,\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true,\PDO::ATTR_ERRMODE=>\PDO::ERRMODE_EXCEPTION,\PDO::ATTR_DEFAULT_FETCH_MODE=>\PDO::FETCH_OBJ));

$os=(new \DateTime())->getOffset();
if($os>(13*60*60)) $os=-24*60*60; //MySQL/MariaDB bug for Pacific/Kiritimati, Pacific/Chatham, and Pacific/Apia
$os = $os >= 0?'+'.gmdate("G:i", $os):'-'.gmdate("G:i", -$os);

$stmtSelect = $db->prepare("SELECT id, NOW() now, mydatetime FROM test WHERE id = ?");
$stmtInsert = $db->prepare("INSERT INTO test(id, mydatetime) VALUES(?,NOW())");
$stmtSelectConvert1 = $db->prepare("SELECT id, NOW() now, CONVERT_TZ(mydatetime, '+00:00', '$os') mydatetime FROM test WHERE id = ?");

$stmtInsert->execute([1]);

$stmtSelect->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERT\n");
print_r($stmtSelect->fetch());

$stmtSelectConvert1->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1\n");
print_r($stmtSelectConvert1->fetch());

echo("\n\nSET SQL TIMEZONE\n");
$db->exec("SET time_zone='$os';");

$stmtInsert->execute([2]);

$stmtSelectConvert2 = $db->prepare("SELECT id, NOW() now, CONVERT_TZ(mydatetime, '+00:00', '$os') mydatetime FROM test WHERE id = ?");

$stmtSelect->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERT\n");
print_r($stmtSelect->fetch());

$stmtSelectConvert1->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1\n");
print_r($stmtSelectConvert1->fetch());

$stmtSelectConvert2->execute([1]);
echo("\n\nINSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT2\n");
print_r($stmtSelectConvert2->fetch());

$stmtSelect->execute([2]);
echo("\n\nINSERTED AFTER SETTING SQL TIMEZONE.  NO CONVERT\n");
print_r($stmtSelect->fetch());

$stmtSelectConvert1->execute([2]);
echo("\n\nINSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT1\n");
print_r($stmtSelectConvert1->fetch());

$stmtSelectConvert2->execute([2]);
echo("\n\nINSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT2\n");
print_r($stmtSelectConvert2->fetch());

 

 

 

 

INSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERT
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 20:16:22
    [mydatetime] => 2018-02-27 20:16:22
)




INSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 20:16:22
    [mydatetime] => 2018-02-27 12:16:22
)




SET SQL TIMEZONE




INSERTED BEFORE SETTING SQL TIMEZONE.  NO CONVERT
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 20:16:22
)




INSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT1
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 12:16:22
)




INSERTED BEFORE SETTING SQL TIMEZONE.  YES CONVERT2
stdClass Object
(
    [id] => 1
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 12:16:22
)




INSERTED AFTER SETTING SQL TIMEZONE.  NO CONVERT
stdClass Object
(
    [id] => 2
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 12:16:22
)




INSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT1
stdClass Object
(
    [id] => 2
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 04:16:22
)




INSERTED AFTER SETTING SQL TIMEZONE.  YES CONVERT2
stdClass Object
(
    [id] => 2
    [now] => 2018-02-27 12:16:22
    [mydatetime] => 2018-02-27 04:16:22
)

[requinix]

I agree, if only for the fact that setting up timezones in MySQL is awkward.

 

I suggest only ever dealing with UTC, as in having that the default PHP timezone and the implied timezone for MySQL dates and times. If you need to show times relative to some other timezone then I would use the DateTime class.

...unless you know you'll never care about any timezone other than your own, in which case go with it because fixing changing timezones later isn't too difficult to do.

[NotionCommotion]

If multiple records need to be queried and presented with local times, this seems like a good solution.  A stored procedure with a session variable could even be used if I get really motivated.

$os=(new \DateTime())->getOffset();
if($os>(13*60*60)) $os=-24*60*60; //MySQL/MariaDB bug for Pacific/Kiritimati, Pacific/Chatham, and Pacific/Apia
$os = $os >= 0?'+'.gmdate("G:i", $os):'-'.gmdate("G:i", -$os);
$stmtSelectConvert2 = $db->prepare("SELECT id, NOW() now, CONVERT_TZ(mydatetime, '+00:00', '$os') mydatetime FROM test WHERE id = ?");

My primary reason for never doing so is the danger or this occurring.  I forgot to post this with my previous post, and it shows the two records that we entered at the same time using NOW() have different datetimes.

+----+---------------------+
| id | mydatetime          |
+----+---------------------+
|  1 | 2018-02-27 20:16:22 |
|  2 | 2018-02-27 12:16:22 |
+----+---------------------+
2 rows in set (0.00 sec)

[gizmola]

I'm with requinix on this 100%.

 

Store everything as UTC.  Your servers should all be UTC as well. 

 

A user's timezone is a property of their profile and/or location.  When you present datetimes to them your application code can take this into account and transform the UTC date accordingly.  When I design systems, I have a timezone property that allows them to set their timezone.  Typically the system includes a provision that sets a reasonable default at signup time.

 

For the backend to mobile apps, which are typically done as a REST API you can get the timezone/locale at session start, and use that to override the default if you want.

 

Needless to say, if you do this well, you are always using a timezone for any datetime display and converting that data on the fly using the appropriate timezone, but you are never storing anything but UTC datetimes, and there is no need to have sprocs or anything else like that.

[NotionCommotion]

Thanks gizmola,  My stance has always been to always store in UTC, but was uncertain whether one should (one definitely could) retrieve in local time by setting MySQL's timezone.  My stance is one should never or rarely do so.