The Big Date and Time Debacle
Posted 07 February 2006 - 12:43 PM
I have searched the web and both PHP and MySql docs, but I cannot find a clear, concise article that describes the best practices when handling dates and times.
I am writing an application that may be used in many time zones. I am in GMT+2 (South Africa) which does not use DST. Living in a non-DST country, I am not sure of how DST works, exactly. I only understand why it was implemented. I want to know what the best practices would be for storing dates in MySql and being able to input them from and display them to a PHP user in any timezone - accurately.
So far, I have managed to use the mysql UTC_TIMESTAMP() function to create records that have an acceptable representation of the current time. I use this to track session timeouts in my session state manager. Because I am only ever working with the difference between a value that was originally recorded with UTC_TIMESTAMP() and the current UTC_TIMESTAMP() value, there is no problem. I don't know what I will do when i have to capture a time entered by a user, who may be in any timezone.
Posted 07 February 2006 - 06:39 PM
You'll probably want to write your own convert_to_gmt() and convert_from_gmt() based on the settings you offer your users. gmdate() can help you, as can mktime(), but there is no panacea that I've found.
Posted 07 February 2006 - 08:47 PM
Posted 07 February 2006 - 09:45 PM
Posted 08 February 2006 - 06:57 AM
I converted my UNIX timestamp to a MySql GMT format with:
$dateString = gmdate("Y-m-d H:i:s", $this->_dateTime);
I converted back with:
strtotime($dateString . " GMT")
The local time string values I checked came out of:
My unit test worked and, because I was storing it in the database as a DATETIME value, the round trip to MySql worked. When I printed the local time strings to the screen, to check that my itteration had actually tested the whole year round, I noticed an interesting anomaly: for some of the year it said that the two matching dates where in GMT +3, not +2. My location does not use DST, so I was very confused - resulting in this thread.
Could this be a configuration issue in my PHP config file?
Are my methods close enough to best practices to be reliable in a mission critical application?
Any suggestions on how I would convert $this->_dateTime (the private member that I use to store my unix timestamp) to a string that represents that time in any timezone? (Assume that I know the timezone from a setting in the user's profile)
I always assumed that I would have to store two settings in the user's profile: their timezone and whether times should be automatically adjusted for DST. I assumed that it is impossible to reliably determine the timezone from which an HTTP request originated.
Posted 08 February 2006 - 09:04 AM
Posted 08 February 2006 - 01:24 PM
For instance, the US is expanding DST in 2007.. it will come on different dates than in the past. Different countries all use different dates themselves. Does PHP accumulate all that information and put it in their new releases? I don't know. To be safe and portable I would try to compile and update the information for myself.
Then you just need to use that information to determine each user's offset from GMT. Convert it to UNIX_TIME, add the offset, convert it back, and you're done.
Posted 08 February 2006 - 01:50 PM
Your right about the lack of documentation.
Are UTC and GMT unaffected by DST? If so, then what you propose will work and so will the code that I have written so far.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users