StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
15233922
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-03-05T20:54:21.767
FavoriteCount
0
LastActivityDate
2013-03-05T20:54:21.767
LastEditDate
LastEditorUserId
0
OwnerUserId
623041
ParentId
5768380
PostTypeId
2
Score
4
ViewCount
0
LastEditorDisplayName
text
Body
<ul> <li><blockquote> How to store the times in the database from a PHP <code>DateTime</code> object </blockquote> The <a href="http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt" rel="nofollow">SQL-92 standard</a> specified that temporal literals should be passed in SQL using a suitable data-type keyword (e.g. <code>TIMESTAMP</code> for date/time values) followed by a string representation of the value (containing an optional timezone offset if non-default). Sadly, MySQL is not compliant with this part of the SQL standard. As documented under <a href="http://dev.mysql.com/doc/en/date-and-time-literals.html" rel="nofollow">Date and Time Literals</a>: <blockquote> Standard SQL permits temporal literals to be specified using a type keyword and a string. <pre>[ deletia ]</pre> MySQL recognizes those constructions and also the corresponding ODBC syntax: <pre>[ deletia ]</pre> However, MySQL ignores the type keyword and each of the preceding constructions produces the string value <code>'str'</code>, with a type of <a href="http://dev.mysql.com/doc/en/char.html" rel="nofollow"><code>VARCHAR</code></a>. </blockquote> The documentation goes on to describe the literal formats which MySQL supports and, notably, explicit timezone offsets are absent. There is a <a href="http://bugs.mysql.com/bug.php?id=14031" rel="nofollow">feature request</a> to fix this that is now over seven years old and which does not look likely to be introduced any time soon. Instead, one must set the session's <a href="http://dev.mysql.com/doc/en/server-system-variables.html#sysvar_time_zone" rel="nofollow"><code>time_zone</code></a> variable prior to exchanging date/time values between server and client. Therefore, using <a href="http://php.net/manual/en/book.pdo.php" rel="nofollow">PDO</a>: <ol> <li>Connect to MySQL: <pre class="lang-php prettyprint-override"><code>$dbh = new PDO("mysql:dbname=$dbname", $username, $password); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); </code></pre></li> <li>Set the session <code>time_zone</code> to that of the <code>DateTime</code> object: <pre class="lang-php prettyprint-override"><code>$qry = $dbh->prepare('SET SESSION time_zone = ?'); $qry->execute([$datetime->format('P')]); </code></pre></li> <li>Produce a suitable literal from the <code>DateTime</code> object and pass to MySQL as normal (i.e. as a parameter to a prepared statement). As described in the documentation, there are a number of possible literal formats that one can use. However, I'd suggest using a string in <code>'YYYY-MM-DD hh:mm:ss.ffffff'</code> format (note that fractional seconds will be ignored in versions of MySQL prior to 5.6), as it is the closest to the SQL standard; indeed one could prefix the literal with the <code>TIMESTAMP</code> keyword to ensure that one's SQL is portable: <pre class="lang-php prettyprint-override"><code>$qry = $dbh->prepare(' UPDATE my_table SET the_time = TIMESTAMP ? WHERE ... '); $qry->execute([$datetime->format('Y-m-d H:i:s.u')]); </code></pre></li> </ol></li> <li><blockquote> Should they be stored in <code>DATETIME</code> or <code>TIMESTAMP</code>? What are the benefits or caveats for each? </blockquote> PHP <code>DateTime</code> objects should always be stored in <code>TIMESTAMP</code> type columns. The most fundamental difference is that <code>TIMESTAMP</code> stores timezone information (by storing the value in UTC and converting to/from as required by the <code>time_zone</code> variable above), whereas <code>DATETIME</code> does not. Thus <code>TIMESTAMP</code> is useful for representing a specific moment in time (analogous to PHP <code>DateTime</code> objects), whereas <code>DATETIME</code> is useful for representing the time that is seen on a calendar/clock (as in a photo). As documented under <a href="http://dev.mysql.com/doc/en/datetime.html" rel="nofollow">The <code>DATE</code>, <code>DATETIME</code>, and <code>TIMESTAMP</code> Types</a>: <blockquote> The <code>DATETIME</code> type is used for values that contain both date and time parts. MySQL retrieves and displays <code>DATETIME</code> values in <code>'YYYY-MM-DD HH:MM:SS'</code> format. The supported range is <code>'1000-01-01 00:00:00'</code> to <code>'9999-12-31 23:59:59'</code>. The <code>TIMESTAMP</code> data type is used for values that contain both date and time parts. <code>TIMESTAMP</code> has a range of <code>'1970-01-01 00:00:01'</code> UTC to <code>'2038-01-19 03:14:07'</code> UTC. MySQL converts <code>TIMESTAMP</code> values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as <code>DATETIME</code>.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a <code>TIMESTAMP</code> value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the <a href="http://dev.mysql.com/doc/en/server-system-variables.html#sysvar_time_zone" rel="nofollow"><code>time_zone</code></a> system variable. For more information, see <a href="http://dev.mysql.com/doc/en/time-zone-support.html" rel="nofollow">Section 10.6, “MySQL Server Time Zone Support”</a>. The <code>TIMESTAMP</code> data type offers automatic initialization and updating to the current date and time. For more information, see <a href="http://dev.mysql.com/doc/en/timestamp-initialization.html" rel="nofollow">Section 11.3.5, “Automatic Initialization and Updating for <code>TIMESTAMP</code>”</a>. </blockquote> Note the final paragraph, which often catches out newcomers to MySQL. It may also be worth adding that, as documented under <a href="http://dev.mysql.com/doc/en/storage-requirements.html" rel="nofollow">Data Type Storage Requirements</a>, <code>DATETIME</code> values require 8 bytes for storage whereas <code>TIMESTAMP</code> values only require 4 bytes (the underlying data storage format can be found in <a href="http://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html" rel="nofollow">Date and Time Data Type Representation</a>).</li> <li><blockquote> Do we ever need to worry about time zones with MySQL <code>DATE</code>? </blockquote> It is only meaningful for a time to be sensitive to timezone. By definition, a date alone is universally the same irrespective of one's timezone and therefore there is no need to "worry about time zones" when using MySQL's <code>DATE</code> data type. The corollary to this is that, if one has a value that is sensitive to timezone, one must also store its time e.g. in a <code>TIMESTAMP</code> column: using a <code>DATE</code> column causes irreversible loss of significant information.</li> <li><blockquote> How to insert values using <code>NOW()</code>. Do these need to be converted somehow either before or after the insert? </blockquote> As documented under <a href="https://dev.mysql.com/doc/en/date-and-time-functions.html#function_now" rel="nofollow"><code>NOW()</code></a>: <blockquote> Returns the current date and time as a value in <code>'YYYY-MM-DD HH:MM:SS'</code> or <code>YYYYMMDDHHMMSS.uuuuuu</code> format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. </blockquote> Since "the value is expressed in the current time zone" and that same "current time zone" will be used in evaluating date/time values, one does not have to worry about time zone when using MySQL's <code>NOW()</code> function (or any of its aliases). Therefore, to insert a record: <pre class="lang-sql prettyprint-override"><code>INSERT INTO my_table (the_time) VALUES (NOW()); </code></pre> Note that, as mentioned above, MySQL's automatic initialisation of <code>TIMESTAMP</code> columns makes redundant most attempts to use <code>NOW()</code> during record insertion/update.</li> <li><blockquote> Is it necessary to set the time zone used by MySQL? If so, how? Should it be done persistently or upon every HTTP request? Does it have to be set to UTC or can it be anything else? Or is the server's time sufficient? </blockquote> This is already addressed above. One can set MySQL's <code>time_zone</code> variable globally, if so desired and thus avoid having to set it upon every connection. See <a href="http://dev.mysql.com/doc/en/time-zone-support.html" rel="nofollow">MySQL Server Time Zone Support</a> for more information.</li> <li><blockquote> How to retrieve values from MySQL and convert them to a <code>DateTime</code> object. Will putting it straight into <code>DateTime::__construct()</code> suffice or do we need to use <code>DateTime::createFromFormat()</code>? </blockquote> As documented under <a href="http://www.php.net/manual/en/datetime.formats.compound.php" rel="nofollow">Compound Formats</a>, one of the date/time formats recognised by the parser that PHP uses in <code>DateTime::__construct()</code> is MySQL's output format. However, since the MySQL output format does not include the timezone, one must be sure to furnish the <code>DateTime</code> constructor with that information through its optional second argument: <pre class="lang-php prettyprint-override"><code>$qry = $dbh->prepare('SET SESSION time_zone = ?'); $qry->execute([$timezone->getName()]); $qry = $dbh->query('SELECT the_time FROM my_table'); $datetime = new DateTime($qry->fetchColumn(), $timezone); </code></pre> Alternatively, one can have MySQL convert the time to a UNIX timestamp and construct the <code>DateTime</code> object from that: <pre class="lang-php prettyprint-override"><code>$qry = $dbh->query('SELECT UNIX_TIMESTAMP(the_time) FROM my_table'); $datetime = new DateTime($qry->fetchColumn()); </code></pre></li> <li><blockquote> When to convert to local time and why. Is there ever a time that we would want to convert it before it is echoed back to the user (e.g. to compare to another DateTime object or a static value)? </blockquote> I'm not sure what you mean by "local time" (local to whom? the RDBMS? the webserver? the webclient?), but comparisons between <code>DateTime</code> objects will handle timezone conversions as necessary (PHP stores the values internally in UTC and only converts for output).</li> <li><blockquote> Is there ever a time we need to worry about Daylight Savings Time (DST)? Why or why not? </blockquote> Generally speaking, if you follow the methodology given above, the only concern for DST is when ensuring that values are rendered to the user in the timezone that they expect.</li> <li><blockquote> 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? </blockquote> As mentioned above, use of <code>NOW()</code> should never cause problems. If literal values have been inserted into a <code>TIMESTAMP</code> column whilst the session's <code>time_zone</code> variable was set to an incorrect value, one will need to update those values accordingly. MySQL's <a href="https://dev.mysql.com/doc/en/date-and-time-functions.html#function_convert-tz" rel="nofollow"><code>CONVERT_TZ()</code></a> function may prove helpful: <pre class="lang-sql prettyprint-override"><code>UPDATE my_table SET the_time = CONVERT_TZ(the_time, '+00:00', '+10:00'); </code></pre></li> </ul>
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. This table or related slice is empty.
UserOwnerUserId
1. USeggyal
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
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
 VTBountyClose
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. This table or related slice is empty.

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.