Jump to content


Photo

The Big Date and Time Debacle


  • Please log in to reply
8 replies to this topic

#1 Charlie.

Charlie.
  • New Members
  • Pip
  • Newbie
  • 4 posts
  • LocationPort Elizabeth, South Africa

Posted 07 February 2006 - 12:43 PM

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
Why the Penguins Won - [a href="http://blue-wildebeest.blogspot.com" target="_blank"]http://blue-wildebeest.blogspot.com[/a]

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 07 February 2006 - 06:39 PM

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.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 07 February 2006 - 07:08 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 07 February 2006 - 08:47 PM

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.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 07 February 2006 - 09:45 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 Charlie.

Charlie.
  • New Members
  • Pip
  • Newbie
  • 4 posts
  • LocationPort Elizabeth, South Africa

Posted 08 February 2006 - 06:57 AM

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.
Why the Penguins Won - [a href="http://blue-wildebeest.blogspot.com" target="_blank"]http://blue-wildebeest.blogspot.com[/a]

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 February 2006 - 09:04 AM

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!
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 08 February 2006 - 01:24 PM

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.

#9 Charlie.

Charlie.
  • New Members
  • Pip
  • Newbie
  • 4 posts
  • LocationPort Elizabeth, South Africa

Posted 08 February 2006 - 01:50 PM

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.
Why the Penguins Won - [a href="http://blue-wildebeest.blogspot.com" target="_blank"]http://blue-wildebeest.blogspot.com[/a]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users