How to store a datatime as UTC in php/mysql?

Question

Everywhere I read about converting time to a users timezone time says the best method is to store a date adn time in UTC then just add the users timezone offset to this time.

So I am wanting to know, how can I store a date in UTC time, I use mysql's 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 then 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 use din PHP to set the current time zone for the script. You can set it to the client time zone so all the formatting funcitons return the time in his local time.

In truth though I had hard time to get this to work and always stumble into some gotcha. Eg. time information returned from MySQL is not formated as 'UTC' so strtotime transforms it into a local time if not carefull. I'm courious 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

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.

HTH, flokra

Answer 3

I would suggest inserting the date in UTC time zone. This will save you a lot of headache in the future (Daylight saving problems etc...)

"INSERT INTO abc_table (registrationtime) VALUES (UTC_TIMESTAMP())"

When I query my data I use the following PHP script

<?  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 here: