Everywhere I read about converting time to a user's timezone says that the best method is to store a date and time in UTC then just add the user's timezone offset to this time.
How can I store a date in UTC time? I use the MySQL DATETIME field.
When adding a new record to MySQL in my PHP code I would use now() to insert into MySQL DATETIME.
Would I need to use something different than now() to store UTC time?
MySQL: UTC_TIMESTAMP()
Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context
PHP: gmdate()
Also PHP date_default_timezone_set() is used in PHP to set the current time zone for the script. You can set it to the client time zone so all the formatting functions return the time in his local time.
In truth though I had a hard time getting this to work and always stumble into some gotcha. Eg. time information returned from MySQL is not formatted as 'UTC' so strtotime transforms it into a local time if you are not careful. I'm curious to hear if someone has a reliable solution for this problem, one that doesn't break when dates traverse media boundaries (HTTP->PHP->MySQL and MySQL->PHP->HTTP), also considering XML and RSS/Atom.
I would suggest inserting the date in UTC time zone. This will save you a lot of headaches in the future with daylight saving problems.
INSERT INTO abc_table (registrationtime) VALUES (UTC_TIMESTAMP())
When I query my data I use the following PHP script:
<?php
while($row = mysql_fetch_array($registration)) {
$dt_obj = new DateTime($row['message_sent_timestamp'] ." UTC");
$dt_obj->setTimezone(new DateTimeZone('Europe/Istanbul'));
echo $formatted_date_long=date_format($dt_obj, 'Y-m-d H:i:s');
}
?>
You can replace the DateTimeZone value with one of the available PHP timezones.
NOW() gives you the time (including the timezone offset) of the system running your database. To get UTC date/time you should use UTC_TIMESTAMP() as described in the MySQL Reference Manual.
https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp
To go along with @ypercube's comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC.
By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may not be UTC!):
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 16:28:45 |
+---------------------+
1 row in set (0.00 sec)
You can set this dynamically:
mysql> SET @@session.time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | +00:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
Or permanently in your my.cnf:
[mysqld]
**other variables**
default_time_zone='+00:00'
Restart your server, and you will see the change:
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00 | +00:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 20:27:50 |
+---------------------+
1 row in set (0.01 sec)
As @Haluk suggests, you can store the date as a UTC datetime .
I'm complementing his answer for situations when the date you want to insert comes from PHP code, and adding some details about how it works :
$pdo = new PDO('mysql:host=mydbname.mysql.db;dbname=mydbname', 'myusername', 'mypassword');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$insertStmt = $pdo->prepare("insert into test (last_modified)"
." values(:last_modified)");
$insertStmt->bindParam(':last_modified', $lastModified, PDO::PARAM_STR);
$lastModified = gmdate("Y-m-d H:i:s");
$insertStmt->execute();
Actually datetime in PHP doesn't have a timezone associated to it. What is passed to PDO is just a string, in one of the formats recognized by MySQL, representing the date in UTC timezone. For example if the date is 2015-10-21 19:32:33 UTC+2:00, the code above just tells MySQL to insert 2015-10-21 17:32:33. gmtdate("Y-m-d H:i:s") gives you the current date in such a format. In any case, all you need to do is build the string representing the date you want to insert in UTC time.
Then, when you read the date, you have to tell PHP that the timezone of the date you just retrieved is UTC. Use the code in Haluk's answer for this.
Random: UTC_TIMESTAMP(6) for time with 6 digits of microseconds.
MySQL 5.7+
As per (https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html) set the timezone upon connection:
SET TIME_ZONE = 'UTC';
After that all datetime functions should be in UTC.
Refrain from using TIMESTAMP() it is not Y2038 save.
