StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
5768749
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
22
CommunityOwnedDate
CreationDate
2011-04-24T05:04:57.450
FavoriteCount
0
LastActivityDate
2013-03-11T21:27:38.030
LastEditDate
2013-03-11T21:27:38.030
LastEditorUserId
168868
OwnerUserId
168868
ParentId
5768380
PostTypeId
2
Score
27
ViewCount
0
LastEditorDisplayName
text
Body
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. <a href="http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html">MySQL still has pathetic timezone support</a>, which means that the intelligence has to be PHP-side. <ul> <li>Set your MySQL connection timezone to UTC as documented in the link above. This will cause all datetimes handled by MySQL, including <code>NOW()</code>, to be handled sanely.</li> <li><a href="https://dev.mysql.com/doc/refman/5.0/en/datetime.html">Always use <code>DATETIME</code>, never use <code>TIMESTAMP</code></a> unless you very expressly require the special behavior in a <code>TIMESTAMP</code>. This is less painful than it used to be. <ul> <li>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.</li> <li>MySQL's preferred datetime format is created using the PHP date format string <code>Y-m-d H:i:s</code></li> </ul></li> <li>Convert all PHP datetimes to UTC when storing them in MySQL, which is a trivial thing as outlined below</li> <li>Datetimes returned from MySQL can be handed safely to the PHP DateTime constructor. Be sure to pass in a UTC timezone as well!</li> <li>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.</li> <li>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.</li> </ul> That addresses most of the points. The last thing is a doozy: <blockquote> <ul> <li>What should someone do if they have previously inserted data (e.g. using <code>NOW()</code>) without worrying about the time zone to make sure everything stays consistent?</li> </ul> </blockquote> This is a real annoyance. One of the other answers pointed out MySQL's <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz"><code>CONVERT_TZ</code></a>, 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. <hr> <blockquote> the app should also be able to SET/Choose the DST accordingly itself for each user. </blockquote> You don't need to and should not do this in the modern era. Modern versions of PHP have the <a href="http://us2.php.net/manual/en/class.datetimezone.php">DateTimeZone</a> class, which includes the ability to <a href="http://us2.php.net/manual/en/datetimezone.listidentifiers.php">list named timezones</a>. 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 <a href="http://us2.php.net/manual/en/class.datetime.php">DateTime</a> for some simple but powerful functionality. You can simply store and use all of your timestamps in UTC <a href="http://us2.php.net/manual/en/function.date-default-timezone-set.php">by default</a>, and convert them to the user's timezone on display. <pre><code>// 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 </code></pre> 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: <pre><code>$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'), "\n"; } </code></pre> You can greatly simplify the selection process by using some client-side magic. Javascript has a <a href="https://developer.mozilla.org/en/JavaScript/Reference/Global_Objects/Date">spotty but functional</a> Date class, with a standard method to <a href="https://developer.mozilla.org/en/JavaScript/Reference/Global_Objects/Date/getTimezoneOffset">get the UTC offset in minutes</a>. 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 <a href="http://us2.php.net/manual/en/timezones.others.php">use deprecated <code>Etc/GMT...</code> timezone strings</a> 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. <hr> 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 <code>Date.getTimezoneOffset</code>. <pre><code>$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\n"; } </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POPHP, MySQL and Time Zones
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USCharles
UserOwnerUserId
1. USCharles
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POPHP, MySQL and Time Zones
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.