Page 3 of 5

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 6:02 pm
by Christopher
allspiritseve wrote:I posted another thread about this, but I do remember PHP in action went pretty in-depth with date/time objects. I don't remember it that well, but I remember thinking it looked useful. I've ordered it through our inter-library loan, so I should have it in a couple of days.
I will be getting a book report! ;) It should be interesting to hear what they said.
allspiritseve wrote:Have you used the DateTime classes in any projects? Are they useful, or does better code need to be written?
Yes, the built-in DateTime class is pretty useful. I just extended it for simple things like _toString, fluent interface, and so you can do date comparisons like ($date1 > $date2). The only function I added is one that tries to sort out user entered dates in yyyy/mm/dd, dd/mm/yy, mm/dd/yy. styles with any delimiter and 2/4 digit years.

For a timezone solution, could the interface be as easy as a TzToUtc() and UtcToTz() methods so you could go back and forth for stored values? How could it internally deal with the stuff pytrin mentions to "just work" ?

Code: Select all

class MyDateTime extends DateTime {
 
    public function TzToUtc($tztime) {
        // ...
        return $utctime;
    }
 
    public function UtcToTz($utctime) {
        // ...
        return $tztime;
    }
 
}

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 6:12 pm
by volomike
Pytrin, here's my current strategy. Store the dates and times in UTC/GMT in your database. Set the bootstrap server date to UTC/GMT. When the user logs in to your site, or simply hits the homepage, gather their timezone via Javascript but without regard for DST (see previous item from me in this thread). So, for instance, if I'm on the East Coast of the USA, Javascript will always return -5 (-05:00), whether DST or not. Now, cache that value in a cookie whenever the user hits other pages on your site, and you can always access $_COOKIE to get it and process it. And if you don't trust $_COOKIE, then use $_SESSION or shared memory or whatever.

Then, when you write dates to the database, write them in UTC/GMT format -- gmdate('Y-m-d H:i:s').

When you read dates from the database, use SQL functions in MySQL or PostgreSQL or whatever database you use to translate the timezone on the fly, rather than PHP functions. You can even have the SQL format the dates and times for you. To me, it seems that SQL could do it faster than iterating the result one more time in PHP and using its date and date/time functions, right?

The drawback with my strategy, however, is portability. It makes it a little harder to switch between database platforms at a later date. It would be nice if the core devs for MySQL, PostgreSQL, and SQLite got together and decided on a standard format for date and time functions because they are so completely different.

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 6:25 pm
by volomike
aborint:

Some method ideas:

- utc to tz, tz to utc <-- and so utc to tz would not need to know DST, but tz to utc would need to ensure tz is in non-DST format

- date comparison

- date projection -- project a date forward or backward by an interval in milliseconds, seconds, minutes, hours, days, and/or years

- MySQL's timestamp format is not compatible with Unix/Epoch, so a tool to flip to/from that might help?

- The same as all of the above, but takes Unix/Epoch timestamps as the input parameter instead of strings

And with all of these, make it work many years before and after Unix/Epoch time. Also, return all dates in PHP's new DateTime object.

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 6:29 pm
by Eran
Store the dates and times in UTC/GMT in your database. Set the bootstrap server date to UTC/GMT
The reason I don't use GMT is that MySQL "CURRENT TIMESTAMP" and NOW() function return the timestamp in the server timezone. PHP's date/time functions also perform timezone differences automatically against the server's timezone. I don't want to deal with calculating differences manually, it is too much of a chore (been there, done that).

Regarding depending on javascript for timezone detection, I rather avoid that in general.

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 6:34 pm
by Christopher
Obviously yours uses server settings and database functions. That sounds like the best solution. But it is pretty common that someone cannot modify server settings or wants database portability, so a PHP solution is an alternate.
volomike wrote:Store the dates and times in UTC/GMT in your database. Set the bootstrap server date to UTC/GMT.
So if the server in not in UTC then we need to know what timezone it is in so we can translate the dates to store to UTC.
volomike wrote:When the user logs in to your site, or simply hits the homepage, gather their timezone via Javascript but without regard for DST (see previous item from me in this thread). So, for instance, if I'm on the East Coast of the USA, Javascript will always return -5 (-05:00), whether DST or not.
So the time information from the the user might be a time offset like you showed, or the local time (which you could compare to UTC to get the offset), or a timezone name. From any one of those you could sort out the offset.
pytrin wrote:PHP's date/time functions also perform timezone differences automatically against the server's timezone. I don't want to deal with calculating differences manually, it is too much of a chore (been there, done that).
How to normalize then?
pytrin wrote:Regarding depending on javascript for timezone detection, I rather avoid that in general.
So the preference is for the user to tell you their timezone?

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 6:38 pm
by Eran
But it is pretty common that someone cannot modify server settings or wants database portability, so a PHP solution is an alternate.
If needed, you can set the php timezone yourself at runtime to match the server's real timezone (and MySQL's). I use this approach on servers I can not influence the configuration directly. Regarding database portability - you are right. This a tradeoff I am willing to make though, and converting a database to another timezone is not that difficult if you are completely consistent (always store at the server's timezone).

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 6:47 pm
by Christopher
volomike wrote:Some method ideas:

- utc to tz, tz to utc <-- and so utc to tz would not need to know DST, but tz to utc would need to ensure tz is in non-DST format
I was hoping that you guys had already figured out a bulletproof way to do that. ;)
volomike wrote:- date comparison
I allow if ($date < $date2) style comparisons of two objects
volomike wrote:- date projection -- project a date forward or backward by an interval in milliseconds, seconds, minutes, hours, days, and/or years
I think DateTime::modify() does that. I add a newModify() method that returns a DateTime object.
volomike wrote:- MySQL's timestamp format is not compatible with Unix/Epoch, so a tool to flip to/from that might help?

- The same as all of the above, but takes Unix/Epoch timestamps as the input parameter instead of strings
Anyone have code for those? :)
pytrin wrote:If needed, you can set the php timezone yourself at runtime to match the server's real timezone (and MySQL's). I use this approach on servers I can not influence the configuration directly.
How is this done? I am looking for a way to make this process easier, so how would a useServerTimezone() method work?

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 6:54 pm
by Eran
Using the function I mentioned before - date_default_timezone_set(). You need to know the timezone of the server to do this correctly obviously.

A way to do this that just occurred to me, is to ask the MySQL server for a timestamp and compare the differences to tell if the default time zone is set up correctly. You can then cache the result into a configuration file for future use.

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 7:23 pm
by Christopher
Ugh.

MySQL4 you do SHOW VARIABLES WHERE Variable='timezone';

MySQL5 it is SHOW VARIABLES WHERE Variable_name='system_time_zone';

MySQL4 produces (in the US) EST/CST/MST/PST while MySQL5 gives "Eastern Standard Time"/"Central..."

But it seems like the programmer can just do this once like you say and put the value in config -- like you said.

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 7:37 pm
by volomike
The reason I don't use GMT is that MySQL "CURRENT TIMESTAMP" and NOW() function return the timestamp in the server timezone.
I never use those two things anymore. But I do store my data in the database only in UTC/GMT format.

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 7:47 pm
by volomike
Guys, has anyone checked the scope context of:

date_default_timezone_set()

I mean, does it affect only the current PHP page for the current user session, or across all user sessions?

I looked at the docs and it says only the currently running script. And I think when we change the php.ini settings dynamically with ini_set(), it might have a global server context?

This is something I haven't thought about before. I would think that both ini_set() and date_default_timezone_set() might only affect each given user session uniquely. But only testing will tell us.

Re: Timezone Strategies

Posted: Wed Dec 31, 2008 8:33 pm
by Christopher
date_default_timezone_set() is an alias for ini_set('date.timezone'). Both would be for the current request only.

Here is link to the docs that shows it being used with a DateTime object.

http://us3.php.net/manual/en/function.d ... ne-set.php

Re: Timezone Strategies

Posted: Sat Jan 10, 2009 12:13 am
by allspiritseve
arborint wrote:I will be getting a book report! ;) It should be interesting to hear what they said.
Here's a quick overview of the classes they implemented:
  • DateAndTime - single time point
    Property - any portion of a time point - year, month, week, day, hour, second
    Fields - strategy objects that Property uses to handle calculations specific to a particular portion of a time point - weeks, for example.
    Period - time span without specific start/end
    Interval - distance between two time points
They are all implemented as Value Objects, and any methods that look like mutators really just return a new class. Also, none of the examples account for timezones, apparently to keep the examples short enough to fit in the book. It's more to illustrate SRP than it is to illustrate a working solution to dates and times.

Re: Timezone Strategies

Posted: Wed Jan 21, 2009 3:25 am
by Popcorn
man, i started out all optimistic then it got horrible.

first was to basically get the offset by JavaScript, show the user in that offset, and store UTC in the Db:
fetch current offset in javascript; bypass any zone setting in PHP; use PHP fns to provide UTC and current offset datetimes (using a timzone object created from the current offset); set the MySQL connection to use the UTC "time_zone"; use PHP fns to construct the SQL segments to perform the conversion for insertion and extraction in the database (using MySQL's "CONVERT_TZ" fn) between UTC in the database and the user's offset.

however.... DST is satanic.

one day a user posts at "1pm +1" (1 pm in a UTC+1 timezone), the next day DST is in effect, so +2, then another post at 2pm.
both posts are at noon UTC.
but if the user looks at the posts now, if i only use the current offset, the posts are both at 2pm (noon UTC +2).
but of course the user knows yesterday the first was at 1pm.
same kind of thing if the time goes from DST-> normal.
same kind of thing if i use the normal UTC offset without DST (a normal UTC offset of +1, means both posts will show as 1pm).
either way, from the user point of view the times are wrong.
also, if i search for all posts in the last day for example, from 2pm today means noon UTC back to noon UTC yesterday, which will include the 1pm post. but for the user this is wrong.

as far as i can tell, the only way is to always discover the timezone (the "Asia/Hong_Kong" style, which includes knowledge for any given datetime, if DST applies) applicable for datetimes and rely on the date operations in PHP's DateTime to be aware of the historical DST settings for the user's zone. MySQL also needs the zone tables to be installed but i assume they work similarly, so you can still probably choose to do you date calculations in PHP or MySQL.

while you can still get the offset from JavaScript, and code posted earlier can tell you if DST was in effect for the year, you really need to know where you are to know the changeover dates for all of history. i can only imagine that without user choice, you'd need some IP location business to help.

generally: ug.

Re: Timezone Strategies

Posted: Wed Mar 11, 2009 1:39 pm
by kaisellgren
Is there a reason why not to use public timezone servers? They are highly reliable and you may have a list of different servers in case someone happens to be down.