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
The way I'm doing this at the moment is to store all dates in the database as UTC (GMT), and converting the timezone in the SQL query when selecting dates.
Because my server (and therefore MySQL) are not in UTC I avoid using the MySQL NOW() function, and use PHP to produce the UTC string instead.
//Storing a date
$now = gmdate('Y-m-d H:i:s');
$query = "INSERT INTO `mytable` (date_column) VALUES ('$now');
//Selecting a date
$user_offset = '+10:00';
$query = "SELECT CONVERT_TZ(date_column, '+00:00', '$user_offset') AS date_column FROM `mytable`";
You'd want some reusable functions to easily call throughout your code, so you don't have to specify the PHP date formatting string all the time, and I also use one to produce that CONVERT_TZ part of the query.
With this approach you store the UTC offset for your users. It's simpler to implement than asking for their location, and working it out, but the down side is that it won't cater for your users observing DST. For example in Sydney they'd need to change their setting from +10:00 to +11:00 when in DST.
I think PHP5 provides some new time and date management classes, that could make it easier to do the conversions in PHP, but I haven't looked into them, so wouldn't want to advise.
Storing their timezone as an offset won't work very well due to annoying things like daylight savings. A better way is to store the persons location, and then to use PHP's locale features to show the correct time.
See DateTime::format which shows how the displayed time changes depending on the timezone that is set.
Note that when changing timezones, the timestamp remains the same, in GMT time. This makes it much easier for you to work with times from different zones with less confusion.
I found out that using the date_default_timezone_set would be a better way to go instead over converting manually because I do want to allow DST.
So if I was to display a list of timezones in a select box on my website for the user to choose from, 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?