Jump to content

Archived

This topic is now archived and is closed to further replies.

Charlie.

The Big Date and Time Debacle

Recommended Posts

Can someone please explain the theory of dates, timestamps, zones, DST, MySql and PHP to me, or point me in the direction of a really good article.

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.

Please help,
Thanks

Share this post


Link to post
Share on other sites
Your best bet is to store everything in GMT and build up a database of parameters for converting to other people's times. Then you can offer them some settings in their profile or something like that.

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.

Share this post


Link to post
Share on other sites
Both MySQL and PHP have variables to set the timezones on a global basis (e.g. my.cnf / php.ini), so I don't see what the problem is.

Share this post


Link to post
Share on other sites
It sounds like he wants to display times to users relative to their own locale, and (eventually) allow them to enter times in some kind of web form, again relative to their own locale. To do that you have to have some information about your user's zone & DST settings and convert.

Share this post


Link to post
Share on other sites
Perhaps so -- a simple time zone flag for each user record will make this trivial, as you suggest. At least on the MySQL side, a simple DATE_ADD(datetime, N HOUR) should solve that side of things. Not sure how to best handle this in PHP, though some custom date wrapper will probably work well.

Share this post


Link to post
Share on other sites
I wrote a custom date handler that managed to successfully perform a roundtrip to MySql and back. For a unit test, I started with time() and added about three hours for each itteration. (This cycled through dates round the whole year, there were 2000 itterations) My date wrapper would hold, as a private member, a UNIX timestamp. It had functions to get this out as a local time or a GMT time. For each itteration, I called the local time fuction, storing the result, called the GMT time function, storing the result. I then created a new instance of the wrapper and told it to parse the GMT string (effectively what would come out of MySql) and then compared the local time string to that of the original. All 2000 itterations succeeded.

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:
date(DATE_W3C, $this->_dateTime);

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.

Share this post


Link to post
Share on other sites
This is far beyond what I know about PHP -- but you would need to store both timezone and DST setting, for sure. Hopefully, wickning1 will be able to shed more light on the subject. Good luck!

Share this post


Link to post
Share on other sites
I really need to do more investigation, but I don't know how certain PHP functions behave in regard to DST. The documentation isn't very clear. For forward compatibility, personally I would attempt to gather data on my own about who is observing DST and when (or I might elicit such information from users).

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.

Share this post


Link to post
Share on other sites
Heh, sounds easy. LOL. Grunt..

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.

Thanks.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.