Timezone Strategies

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Timezone Strategies

Post by matthijs »

Maybe Luke has something to say about this, as he is busy with an iCal project
http://nozavroni.wordpress.com/qcal-an- ... y-for-php/
which obviously has a lot to do with dates and times.

Onion2k mentions in a couple of threads about this to not store dates as timestamp. For example:
viewtopic.php?f=1&t=87287&p=481548&hili ... mp#p481548

Personally I use datetime now, as it makes working with dates and times a bit easier for me. However, I'm not sure yet how that translates to timezone issues

Here's an article about PHP's native datetime class
http://laughingmeme.org/2007/02/27/
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Timezone Strategies

Post by alex.barylski »

I have always used timestamps and never had a problem...

Date comparison using integers must be faster than comparing human readable dates. I imagine that PHP is also faster at calculating offsets than MySQL.

If I wanted ot selected all the dates within a certain timeframe I would calculate the two offsets

Code: Select all

$start = time()-3600; // One hour ago
$finish = time()+3600; // One hour in the future
 
$query = "SELECT * FROM table WHERE time > $start AND time < $finish";
I'm not sure I understand why this is troubling?
1. Timestamps take up more space than dates
2. Timestamps are limited to 1970 - 2038. Dates aren't.
3. By default a timestamp column will have ON UPDATE CURRENT_TIMESTAMP switched on, so if you update the row without taking care to maintain the same data the field will be updated to today's date. That will probably break all your data.
I should have been more clear, but, when I say store the date as timestamp I didn't mean a native MySQL timestamp I meant a 4 byte integer, which is what timestamps are when used internally. I have no idea how big a timestamp is in MySQL but a real timestamp is about as small and compact as you can get.

The limits imposed by a integer are easily circumvented, use 6 bytes instead of 4 if you need to store dates before or after the standard epoch.

None of those issues above have ever affected me by just using a plain INT field, which will hold timestamps nicely, compactly and quite efficiently.

Cheers,
Alex
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Timezone Strategies

Post by matthijs »

Well I don't know the ins and outs, I guess as with many things what's best depends on the specific situation. In my case, when I have a web form and someone wants to save a date and/or time, he will see and use a datepicker in a human readable format, like 2008-12-28. then I can store that value directly in the database with a date or datetime field. And retrieve it in a human readable fashion. Never any need for a conversion.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Timezone Strategies

Post by Christopher »

I am a little confused (as usual) because I am not sure I understand the problem(s) being solved in this thread. It seems to be displaying times in different timezones in some specific way, but it is not clear to me exactly what is desired. And I get the impression you are looking for some very specific results.

From this code:

Code: Select all

SELECT dt_created, dt_created AT TIME ZONE INTERVAL '-05:00' FROM users
I get the sense that you want to have users select records in local time and adjust that to server time when doing queries.
(#10850)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Timezone Strategies

Post by Eran »

The point is showing users from different timezones times that are consistent with their timezone differences. For example, if someone from Sidney, Australia created an item - the item creation date would always show his local time when he uses the site. Another user from Tahiti would see a different time which corresponds to the timezone differences between them - so they both have a relevant time to relate too.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Timezone Strategies

Post by Weirdan »

The fact many programmers don't realize is that timezone is more than simple time offset. Consider the following situation:
I live in Europe/Kiev timezone, we had our last dst adjustment on October, 26th. On October, 25th, noon, I've made a post here. My time offset from UTC was +3 at that moment, so the post was stored with date set to October 25th, 9am UTC. On the following day, I logged in to these forums, and viewed my own post from the previous day. If forums software was blindly using my current time offset (which was +2, since the clocks were adjusted previous night) to adjust all dates, it would show me my post as if it was dated October 25th, 11am. But that is absolute nonsense, because I'm absolutely sure I made it on noon, exactly.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Timezone Strategies

Post by Christopher »

pytrin wrote:The point is showing users from different timezones times that are consistent with their timezone differences. For example, if someone from Sidney, Australia created an item - the item creation date would always show his local time when he uses the site. Another user from Tahiti would see a different time which corresponds to the timezone differences between them - so they both have a relevant time to relate too.
I understand that when you or Weirdan post something that you want to see the post time be the time in your timezone. And I see that I will be shown that you posted at 3:00AM or something because that is when you posted in my timezone. But I get the impression that volomike wants something more than that -- but maybe I am misunderstanding.
(#10850)
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

I am a little confused (as usual) because I am not sure I understand the problem(s) being solved in this thread. It seems to be displaying times in different timezones in some specific way, but it is not clear to me exactly what is desired. And I get the impression you are looking for some very specific results.
The goal of my thread here was to store everything in the database as UTC/GMT, by one standard, you see, and to store it in the best format possible. At first I sort liked the timestamp example, where you store the Unix Epoch number, but it has a human-readable issue. That's when I found out that both PostgreSQL and MySQL can return a date in one's timezone delta (delta = difference, btw). Then, translate those values to one's local time by identifying the end user's local time difference from UTC/GMT. The way I do that is through Javascript to have the browser tell me that difference, and the browser pulls this from the client OS. Most often an end user, if they detect the time is wrong on their PC, will adjust it. (If it's an OS issue, such as the problem of a broken piece of timezone code after a DST change by their local government that was recently changed in their legislature, well, these usually get fixed fairly rapidly by the OS vendor, or the end user can simply change their date and time to what they think it should be.) Next, instead of taking my queries from the database, pulling them into PHP, iterating them and running them through a date filter, it seemed to me that the fastest route was to have the SQL do the translation for me in the database on the fly while doing the select.

In simpler terms, I mean we store in the database as a real date and time by UTC/GMT standards. We then need that in the end user's local time when displaying those values so that it is meaningful in the context of the end user. The way to do that efficiently appears to be getting the value from Javascript, caching that in a cookie so that I can pull it on the server with $_COOKIE when I need it, and then using the feature in PostgreSQL and MySQL that translates the timezone in the query, on the fly, when pulling the records back out. I would merely pass the end user's timezone difference into the SQL query.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Timezone Strategies

Post by Weirdan »

volomike wrote:In simpler terms, I mean we store in the database as a real date and time by UTC/GMT standards. We then need that in the end user's local time when displaying those values so that it is meaningful in the context of the end user. The way to do that efficiently appears to be getting the value from Javascript, caching that in a cookie so that I can pull it on the server with $_COOKIE when I need it, and then using the feature in PostgreSQL and MySQL that translates the timezone in the query, on the fly, when pulling the records back out. I would merely pass the end user's timezone difference into the SQL query.
This would lead to the problem I outlined in my previous post: you can't use current time offset of a user to adjust dates in past or future, because for those dates user's time offset might have been different from what they have now.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

What? If I register a new user on my site, and I have a dt_created field populated with that date, and the user then goes back to a user control panel setting where I dump out the date and time when their account was created -- this is where I would take the UTC/GMT date that's in the database, run the SQL such that I translate to one's timezone, and return the value in their timezone, not UTC/GMT. And the trick to do that is by having the user's Javascript indicate the time difference to me for me to do that computation and feed it to the SQL statement.

I guess I don't get what you're driving at, Weirdan.

EDIT: Looking back, I think what you mean is that I would be writing the date into the database and not factoring in Daylight Savings Time (DST). Well, let's think this thing through. When you store all dates in the database as UTC/GMT time, there is no DST on that -- that clock keeps ticking along and the only thing that happens is what's called a "leap second revision", but that's another discussion for another day. So, if on Friday I create a new user and my time is -5 GMT, but on Monday (after a DST change) I go back to look and I pass a -4 GMT, the Friday creation date would be one hour off from the perspective by the user. But on the server, the date would not be one hour off. Hmmm. So this is what you're driving at. End users should have a control panel setting to let them check off DST or not, and it should just assume after a certain date that they are in DST or not. If they are, then it would switch the -4 GMT that it receives from Javascript into -5 GMT. If not, then it leaves the Javascript calculation alone at -5 GMT. Is that what you're thinking?
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Timezone Strategies

Post by volomike »

Weirdan made a good point, and someone has now solved it. Here's his function, and I follow with an explanation...

SOURCE: http://www.attackwork.com/BlogEntry/3/T ... fault.aspx

Code: Select all

function TimezoneDetect(){
    var dtDate = new Date('1/1/' + (new Date()).getUTCFullYear());
    var intOffset = 100; //set initial offset high so it is adjusted on the first attempt
    var intMonth;
    var intHoursUtc;
    var intHours;
    var intDaysMultiplyBy;
   
    //go through each month to find the lowest offset to account for DST
    for (intMonth=0;intMonth < 12;intMonth++){
        //go to the next month
        dtDate.setUTCMonth(dtDate.getUTCMonth() + 1);
       
        //To ignore daylight saving time look for the lowest offset.
        //Since, during DST, the clock moves forward, it'll be a bigger number.
        if (intOffset > (dtDate.getTimezoneOffset() * (-1))){
            intOffset = (dtDate.getTimezoneOffset() * (-1));
        }
    }
 
    return intOffset;
}
This is a great piece of code! He basically ignores DST altogether. So, even if your browser is experiencing DST, this algorithm will completely ignore it. So, if I'm using the timezone information from your browser to indicate to me what the date transaction, and you live, say, on the east coast of the USA, then it will ALWAYS, 100%, return -5 (aka -05:00).

Therefore, the date and time difference calculation will always work.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Timezone Strategies

Post by Weirdan »

volomike wrote:End users should have a control panel setting to let them check off DST or not, and it should just assume after a certain date that they are in DST or not. If they are, then it would switch the -4 GMT that it receives from Javascript into -5 GMT. If not, then it leaves the Javascript calculation alone at -5 GMT. Is that what you're thinking?
That could be an option, but better yet ask them for the timezone they are in and use php DateTime class to convert dates from UTC. DateTime uses Olson database, which stores historic data about wall clock time changes around the globe and can be easily updated by running 'pecl upgrade timezone' periodically. Another thing to consider is that when you allow users to enter wall clock time you must be absolutely certain what timezone it's related to. Consider this situation:
You're creating a web application similar to Google Calendar, where users can plan they daily schedule ahead of time and it would send reminders by email, one hour before something important should happen (it could be business meeting, a gig or something else important to your users). If I'm planning a meeting at Tokyo and specify that it will happen at 10am, most probably it means the time provided in Tokyo's timezone, regardless of what my current timezone is. If application assumed that users are entering time in their current timezone, it would send the remainder several hours late in this situation.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Timezone Strategies

Post by Christopher »

I like many people have been working on a class for Data and Time. Mine's based on PHP's DataTime class. What kind of methods would be needed to provide support for going back and forth between UTC and the user's timezone easily? I am thinking of something that would take into account the server's and PHP's configuration and do the right thing. How do we make this simple, because it seems like a frequently asked question here. I read strategies here, but who's got some code?
(#10850)
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Timezone Strategies

Post by allspiritseve »

arborint wrote:I like many people have been working on a class for Data and Time. Mine's based on PHP's DataTime class.
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.

Have you used the DateTime classes in any projects? Are they useful, or does better code need to be written?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Timezone Strategies

Post by Eran »

How do we make this simple, because it seems like a frequently asked question here. I read strategies here, but who's got some code?
I posted some code in the thread I referenced earlier. I usually set the timezone in the bootstrap during environment setup, before application code is invoked.

Code: Select all

//$timezone is fetched from a user's settings, often stored in his session
if(is_string($timezone)){   
    date_default_timezone_set($timezone);
}
Otherwise the server's timezone is used (it's important to set it correctly in the PHP configuration file). Since most of PHP time/date functions are timezone aware, once I set the timezone properly I don't have to manually calculate timezone differences (I used to do it with the DateTime object, but this approach is much simpler to use).

My main problem (as I explained in my thread) is dealing with database stored timestamps. Since MySQL native timestamp format is not the unix format (ie, human readable versus an integer), I need to constantly convert it back to integer before it reaches the PHP, since the basic date formatting approach uses two timezone aware functions (date() and strtotime()) causing the timezone difference to be applied twice.

This is actually just a minor annoyance, and overall I think this approach works well.
Post Reply