Page 1 of 1

Time Zones for different users

Posted: Wed Mar 24, 2010 6:39 pm
by Mr Tech
I am creating a script that allows the user to choose their own timezone...

If I am to make this work, how do I store dates in the database so that every timezone will read it, and show the correct date in their timezone?

Do I store the date as GMT and then when a user with the timezone GMT +10 selected views the item within my script, I show that date in GMT +10 time?

Is there a better way to do this?

Examples would be great :)

Re: Time Zones for different users

Posted: Thu Mar 25, 2010 4:15 pm
by Christopher
Store it in whatever the server uses -- especially the database. That is often GMT, but may not be.

Re: Time Zones for different users

Posted: Thu Mar 25, 2010 5:17 pm
by pickle
I'd suggest always storing as GMT/UTC - not the server time (though, if the server is sitting on the Prime Meridian - go ahead). That way you're safe in case your server moves timezones - then the formula for converting from DB time to user time will never change.

Re: Time Zones for different users

Posted: Thu Mar 25, 2010 5:53 pm
by batfastad
I was working on a small intranet project when first had to learn about handling timezones.

Basically what I do is store all dates/times as unix timestamps throughout my DB and these are all UTC/GMT
Then I store the user's actual timezone (not offset) eg: Europe/London in their database record
Here's the list of all supported timezones... http://uk3.php.net/timezones

Then when a user logs on to our intranet I query the database to get their user details (incl. their timezone) and use the date_default_timezone_set() function:

Code: Select all

$user_timezone = 'America/Boise';date_default_timezone_set($user_timezone); //php5 only
This sets the user's timezone for the duration of the script. Then when our server's timezone changes and daylight savings starts/ends I don't actually have to do anything!

There are various pages in our intranet where I'll need to display a date/time relative to that user's timezone... eg: a user in Australia would want to see the modification time of a contact record in their local time, not in UTC/GMT.
If I was in a non-GMT timezone, I would want to see that someone edited a record at 04:30 my time, rather than 13:30 GMT and have to work it out for myself. In these cases I use the regular date() and mktime() functions.

However there are other places where I want an absolute date, eg all sales between a certain date range or the date of an invoice stored in our database (because all dates/times are stored in UTC in our DB).
So for those I'll use the gm functions... gmdate() and gmmktime()
http://uk.php.net/manual/en/ref.datetime.php

Using the built-in timezones and functions just seemed a much better option than dealing with timezone offsets and various daylight savings times in different places throughout the world.

Please note I am no expert... but after much reading and getting it wrong I found this to be the easiest way for managing timezones in every project I've worked on. If anyone thinks I'm giving bad advice on this one then please let me know. I always want to learn the best practice methods.
It makes me want to cry when I see timezone selection boxes with GMT+1, GMT+2 etc 8O

Hope this helps

Cheers, B

Re: Time Zones for different users

Posted: Thu Mar 25, 2010 7:47 pm
by Christopher
pickle wrote:I'd suggest always storing as GMT/UTC - not the server time (though, if the server is sitting on the Prime Meridian - go ahead). That way you're safe in case your server moves timezones - then the formula for converting from DB time to user time will never change.
The formula never change whatever the reference timezone is -- it is still the difference. My point was that if you use database functions like NOW() and they are not GMT then you are doing double work converting.

Re: Time Zones for different users

Posted: Thu Mar 25, 2010 8:41 pm
by Weirdan
Christopher wrote:The formula never change whatever the reference timezone is -- it is still the difference.
But the difference is not fixed - it could change from season to season, country to country and year to year. Thus storing just the offset (x in GMT+x) in user's profile is the worst solution after not handling timezones at all.
batfastad wrote:If anyone thinks I'm giving bad advice on this one then please let me know. I always want to learn the best practice methods.
It's good advice indeed. Also you could use mysql per-connection timezone support: if the column is timestamp it gets autoconverted to/from connection time zone. In this way your users would be able to query by date ranges in their own timezone instead of fixed one, and you would not need to do a conversion yourself.

Re: Time Zones for different users

Posted: Thu Mar 25, 2010 8:50 pm
by Eran
The formula never change whatever the reference timezone is -- it is still the difference. My point was that if you use database functions like NOW() and they are not GMT then you are doing double work converting.
This depends on the type you use for storing date/time. The TIMESTAMP type is always stored as UTC internally, so there would be no double conversion.

The pattern that I follow for different timezones is to keep all relevant date/time columns in TIMESTAMP format, retrieve that as a unix timestamp (using UNIX_TIMESTAMP() ) and pass it to PHP date functions which are timezone aware. All that's left is to set the user timezone somewhere in the initialization of the application.

In my experience, trying to do timezone conversions manually by calculating zone differences is very tricky. It's best to take advantage of the timezone awareness of PHP functions.

Re: Time Zones for different users

Posted: Mon Mar 29, 2010 1:27 am
by Mr Tech
Awesome! Great advice!

So if I was to display a list of timezones in a select box on my website, how should I do this?

Should I show every single timezone possible in a massive list?

Or should I have two select boxes. One to select the country and then use ajax to show all the different timezones within that country?

Or is there a better way?

Re: Time Zones for different users

Posted: Mon Mar 29, 2010 3:03 am
by batfastad
On our intranet I do exactly what you're suggesting.
I have86 AJAX button, one for each continent from here http://php.net/manual/en/timezones.php missing out Antarctica, Arctic and Others. Then that AJAX button just loads up a select with all the countries/cities in that continent section

Take a look at this function http://uk3.php.net/manual/en/function.t ... s-list.php to get all the timezones in one big array. Then you could avoid using AJAX by using chained selects in Javascript or just have one big list.

Hope this helps

Cheers, B

Re: Time Zones for different users

Posted: Mon Mar 29, 2010 7:50 am
by Eran
There are not many timezones in practice. I use the generic form (GMT+- offset) and list a few of the main countries/cities belonging to that timezone.

For example:

GMT - London, Lisbon
GMT+1 - Brussels, Copenhagen, Madrid, Paris

etc

Re: Time Zones for different users

Posted: Mon Mar 29, 2010 7:57 am
by batfastad
But what happens if London and Lisbon have different daylight savings settings from each other?
That for me was always the advantage of using timezone identifiers rather than offsets. Often locations in the same offsets have very different daylight savings periods.

Cheers, B

Re: Time Zones for different users

Posted: Mon Mar 29, 2010 8:04 am
by Eran
That's true. It depends on how accurate you need to be. This forum for example, uses the approach I just mentioned and it works pretty well.

Re: Time Zones for different users

Posted: Mon Mar 29, 2010 10:06 am
by Weirdan
pytrin wrote:This forum for example, uses the approach I just mentioned and it works pretty well.
That's right, but only because for the most part people don't care about when something happened here. It doesn't matter if a post timestamp is several hours off, as long as posts are in chronological order.

Re: Time Zones for different users

Posted: Mon Mar 29, 2010 12:29 pm
by Christopher
You can also use Javascript to have the browser send you the user's time and timezone offset.

Re: Time Zones for different users

Posted: Mon Mar 29, 2010 6:12 pm
by Mr Tech
Awesome thanks! Yea, in my case I need to support DST so using the batfastad's approach is the way I will go :) Thanks alot for your help!