What's the best way to manage dates across PHP, MySQL, etc?

Question

My server is in Dallas. I'm in New York City.. and both PHP and MySQL have configuration variables for setting the timezone.

How do I get them all to work together? What dates should I store in MySQL? How do I get PHP to handle changing the date based on the user's preference?

Bear in mind: I don't think I'm ever having PHP explicitly set the date, it's always using "NOW()" in queries.. however I foresee the need to do this. How would this be done?

I'm hoping SO's experience can help me out here.

Answer 1

Record your dates in GMT (zero offset) and then calculate the offset based on the local timezone (EST is +6, for example, so you'd add 6 hours to the GMT).

Check the Date docs for the date_default_timezone_set() function.

Just remember, when writing to the database, you'll have to change time zones, store the date, then change back. Likewise, when you're retrieving the date, don't forget to add the timezone offset.

Answer 2

Use Unix Time everywhere. It's using UTC so it's the same for every timezone. Methods for dates usually convert to it and back from it using timezone information they have, so you would have yourself a correct time.

Alternatively you could use Unix Time only to transfer time from one computer to another (like from DB to your server running PHP, or to JavaScript client). There's functions to convert to it and from it in every language. For MySQL it is:

UNIX_TIMESTAMP(date)
FROM_UNIXTIME(unix_timestamp)

That way you could have your time properly formatted on the DB and in logs but still have correct local time everywhere.

Answer 3

The mysql-server stores dates in a timezone independent format (UTC).
But before it stores the date it will be converted using its timezone.

U can change the mysql timezone per connection *1:

mysql_query('SET time_zone = "'.$timezone.'"');

You can also change the timezone per script.

date_default_timezone_set($timezone);

If you set them to the same timezone "2009-01-10 13:30:00" will mean the same thing to both mysql and php.

But keep in mind that the 2 servers have different internal clock values, so if you want to generate timestamps based on current time. Do that in mysql OR php.

*1) MySQL timezone support may require additional configuration. check the manual

Answer 4

I prefer using dates and times in the native form with respect to the environment, that is, Unix timestamps in PHP and DATE/TIME/DATETIME/TIMESTAMP fields in MySQL. I translate both values into another using FROM_UNIXTIME() and UNIX_TIMESTAMP(). I prefer this instead of Unix timestamps, because native dates/times are much easier to read.