Timezone Strategies
Moderator: General Moderators
Re: Timezone Strategies
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/
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
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
I'm not sure I understand why this is troubling?
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
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 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.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.
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
Re: Timezone Strategies
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.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Timezone Strategies
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:
I get the sense that you want to have users select records in local time and adjust that to server time when doing queries.
From this code:
Code: Select all
SELECT dt_created, dt_created AT TIME ZONE INTERVAL '-05:00' FROM users(#10850)
Re: Timezone Strategies
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.
Re: Timezone Strategies
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.
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.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Timezone Strategies
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.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.
(#10850)
- volomike
- Forum Regular
- Posts: 633
- Joined: Wed Jan 16, 2008 9:04 am
- Location: Myrtle Beach, South Carolina, USA
Re: Timezone Strategies
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.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.
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.
Re: Timezone Strategies
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.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.
- volomike
- Forum Regular
- Posts: 633
- Joined: Wed Jan 16, 2008 9:04 am
- Location: Myrtle Beach, South Carolina, USA
Re: Timezone Strategies
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?
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?
- volomike
- Forum Regular
- Posts: 633
- Joined: Wed Jan 16, 2008 9:04 am
- Location: Myrtle Beach, South Carolina, USA
Re: Timezone Strategies
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
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.
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;
}Therefore, the date and time difference calculation will always work.
Re: Timezone Strategies
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: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?
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.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Timezone Strategies
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)
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: Timezone Strategies
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.arborint wrote:I like many people have been working on a class for Data and Time. Mine's based on PHP's DataTime class.
Have you used the DateTime classes in any projects? Are they useful, or does better code need to be written?
Re: Timezone Strategies
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.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?
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);
}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.