Storing datetime as UTC in PHP/MySQL

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.