Storing datetime as UTC in PHP/MySQL

Question

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?

Answer 1

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.

Answer 2

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.