Storing datetime as UTC in PHP/MySQL

by

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.

More Related Contents:

  1. PHP, MySQL and Time Zones
  2. Set timezone in PHP and MySQL
  3. Storing time information: Timezone required?
  4. Comma separated numeric values – PHP CodeIgniter
  5. Using PHP data in MySQL [closed]
  6. How to sort user data by id
  7. Select those record only from table 1 where city exist against state id in table 2 in PHP
  8. PHP PDO prepared statements
  9. PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?
  10. mysqli::query(): Couldn’t fetch mysqli
  11. select count(*) from table of mysql in php
  12. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  13. Loading .sql files from within PHP
  14. Incorrect Integer (2147483647) is inserted into MySQL?
  15. I need my PHP page to show my BLOB image from mysql database
  16. How to loop through a mysql result set
  17. Cannot connect to MySQL 4.1+ using old authentication
  18. Object of class DateTime could not be converted to string
  19. PHP Commands Out of Sync error
  20. PHP MYSQL UPDATE if Exist or INSERT if not?
  21. how to check and set max_allowed_packet mysql variable [duplicate]
  22. mysql_real_escape_string VS addslashes
  23. PHP: multiple SQL queries in one mysql_query statement
  24. MySql Error: 1364 Field ‘display_name’ doesn’t have default value
  25. Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
  26. Codeigniter Transactions
  27. Eloquent many-to-many-to-many – how to load distant relation easily
  28. How do you make good use of multicore CPUs in your PHP/MySQL applications? [closed]
  29. How to get a list of databases?
  30. How to sort MYSQL fulltext search results by relevancy

Leave a Comment