This answer has been updated to accomodate the bounty. The original, unedited answer is below the line.
Almost all of the question points added by the bounty owner are in relation to how MySQL and PHP datetimes should interact, in the context of timezones.
MySQL still has pathetic timezone support, which means that the intelligence has to be PHP-side.
- Set your MySQL connection timezone to UTC as documented in the link above. This will cause all datetimes handled by MySQL, including
NOW(), to be handled sanely.
- Always use
DATETIME, never use TIMESTAMP unless you very expressly require the special behavior in a TIMESTAMP. This is less painful than it used to be.
- It's ok to store the Unix epoch time as an integer if you have to, such as for legacy purposes. The epoch is UTC.
- MySQL's preferred datetime format is created using the PHP date format string
Y-m-d H:i:s
- Convert all PHP datetimes to UTC when storing them in MySQL, which is a trivial thing as outlined below
- Datetimes returned from MySQL can be handed safely to the PHP DateTime constructor. Be sure to pass in a UTC timezone as well!
- Convert the PHP DateTime to the user's local timezone on echo, no sooner. Thankfully DateTime comparison and math against other DateTimes will take into account the timezone that each is in.
- You're still up to the whims of the DST database provided with PHP. Keep your PHP and OS patches up to date! Keep MySQL in the blissful state of UTC to remove one potential DST annoyance.
That addresses most of the points.
The last thing is a doozy:
- What should someone do if they have previously inserted data (e.g. using
NOW()) without worrying about the time zone to make sure everything stays consistent?
This is a real annoyance. One of the other answers pointed out MySQL's CONVERT_TZ, though I'd personally have done it by hopping between server-native and UTC timezones during selects and updates, 'cause I'm hardcore like that.
the app should also be able to SET/Choose the DST accordingly itself for each user.
You don't need to and should not do this in the modern era.
Modern versions of PHP have the DateTimeZone class, which includes the ability to list named timezones. Named timezones allow the user to select their actual location, and have the system automatically determine their DST rules based on that location.
You can combine DateTimeZone with DateTime for some simple but powerful functionality. You can simply store and use all of your timestamps in UTC by default, and convert them to the user's timezone on display.
// UTC default
date_default_timezone_set('UTC');
// Note the lack of time zone specified with this timestamp.
$nowish = new DateTime('2011-04-23 21:44:00');
echo $nowish->format('Y-m-d H:i:s'); // 2011-04-23 21:44:00
// Let's pretend we're on the US west coast.
// This will be PDT right now, UTC-7
$la = new DateTimeZone('America/Los_Angeles');
// Update the DateTime's timezone...
$nowish->setTimeZone($la);
// and show the result
echo $nowish->format('Y-m-d H:i:s'); // 2011-04-23 14:44:00
By using this technique, the system will automatically select the correct DST settings for the user, without asking the user whether or not they're currently in DST.
You can use a similar method to render the select menu. You can continually reassign the time zone for the single DateTime object. For example, this code will list the zones and their current times, at this moment:
$dt = new DateTime('now', new DateTimeZone('UTC'));
foreach(DateTimeZone::listIdentifiers() as $tz) {
$dt->setTimeZone(new DateTimeZone($tz));
echo $tz, ': ', $dt->format('Y-m-d H:i:s'), "
";
}
You can greatly simplify the selection process by using some client-side magic. Javascript has a spotty but functional Date class, with a standard method to get the UTC offset in minutes. You can use this to help narrow down the list of likely timezones, under the blind assumption that the user's clock is right.
Let's compare this method to doing it yourself. You'd need to actually perform date math every single time you manipulate a datetime, in addition to pushing a choice off on the user that they aren't going to really care about. This isn't just sub-optimal, it's bat-guano insane. Forcing users to signify when they want DST support is asking for trouble and confusion.
Further, if you wanted to use the modern PHP DateTime and DateTimeZone framework for this, you'd need to use deprecated Etc/GMT... timezone strings instead of named timezones. These zone names may be removed from future PHP versions, so it'd be unwise to do that. I say all of this from experience.
tl;dr: Use the modern toolset, spare yourself the horrors of date math. Present the user with a list of named time zones. Store your dates in UTC, which won't be impacted by DST in any way. Convert datetimes to the user's selected named time zone on display, not earlier.
As requested, here's a loop over the available time zones displaying their GMT offset in minutes. I selected minutes here to demonstrate an unfortunate fact: not all offsets are in whole hours! Some actually switch half an hour ahead during DST instead of a whole hour. The resulting offset in minutes should match that of Javascript's Date.getTimezoneOffset.
$utc = new DateTimeZone('UTC');
$dt = new DateTime('now', $utc);
foreach(DateTimeZone::listIdentifiers() as $tz) {
$local = new DateTimeZone($tz);
$dt->setTimeZone($local);
$offset = $local->getOffset($dt); // Yeah, really.
echo $tz, ': ',
$dt->format('Y-m-d H:i:s'),
', offset = ',
($offset / 60),
" minutes
";
}
