StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<hr> <h2>Summary of answers and other data: (please add yours)</h2> <p><strong>Do:</strong></p> <ul> <li>Whenever you are referring to an exact moment in time, persist the time according to a unified standard that is not affected by daylight savings. (GMT and UTC are equivalent with this regard, but it is preferred to use the term UTC. Notice that UTC is also known as <em>Zulu</em> or <em>Z</em> time.)</li> <li>If instead you choose to persist a time using a local time value, include the local time offset for this particular time from UTC (this offset may change throughout the year), such that the timestamp can later be interpreted unambiguously. </li> <li>In some cases, you may need to store <em>both</em> the UTC time and the equivalent local time. Often this is done with two separate fields, but some platforms support a <code>datetimeoffset</code> type that can store both in a single field.</li> <li>When storing timestamps as a numeric value, use <a href="http://en.wikipedia.org/wiki/Unix_time" rel="nofollow noreferrer">Unix time</a> - which is the number of whole seconds since <code>1970-01-01T00:00:00Z</code> (excluding leap seconds). If you require higher precision, use milliseconds instead. This value should always be based on UTC, without any time zone adjustment.</li> <li>If you might later need to modify the timestamp, include the original time zone ID so you can determine if the offset may have changed from the original value recorded.</li> <li>When scheduling future events, usually local time is preferred instead of UTC, as it is common for the offset to change. <a href="https://stackoverflow.com/a/19627330/634824">See answer</a>, and <a href="http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html" rel="nofollow noreferrer">blog post</a>.</li> <li>When storing whole dates, such as birthdays and anniversaries, do not convert to UTC or any other time zone. <ul> <li>When possible, store in a date-only data type that does not include a time of day.</li> <li>If such a type is not available, be sure to always ignore the time-of-day when interpreting the value. If you cannot be assured that the time-of-day will be ignored, choose 12:00 Noon, rather than 00:00 Midnight as a more safe representative time on that day.</li> </ul></li> <li>Remember that time zone offsets are not always an integer number of hours (for example, Indian Standard Time is UTC+05:30, and Nepal uses UTC+05:45).</li> <li>If using Java, use <a href="http://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html" rel="nofollow noreferrer">java.time</a> for Java 8, or use <a href="http://www.joda.org/joda-time/" rel="nofollow noreferrer">Joda Time</a> for Java 7 or lower.</li> <li>If using <a href="http://en.wikipedia.org/wiki/.NET_Framework" rel="nofollow noreferrer">.NET</a>, consider using <a href="http://nodatime.org" rel="nofollow noreferrer">Noda Time</a>.</li> <li>If using .NET without Noda Time, consider that <code>DateTimeOffset</code> is often a better choice than <code>DateTime</code>.</li> <li>If using Perl, use <a href="https://metacpan.org/module/DateTime" rel="nofollow noreferrer">DateTime</a>.</li> <li>If using Python, use <a href="http://pytz.sourceforge.net/" rel="nofollow noreferrer">pytz</a> or <a href="https://dateutil.readthedocs.org/en/latest/" rel="nofollow noreferrer">dateutil</a>.</li> <li>If using JavaScript, use <a href="http://momentjs.com/" rel="nofollow noreferrer">moment.js</a> with the <a href="http://momentjs.com/timezone/" rel="nofollow noreferrer">moment-timezone</a> extension.</li> <li>If using PHP > 5.2, use the native time zones conversions provided by <code>DateTime</code>, and <code>DateTimeZone</code> classes. Be careful when using <code>DateTimeZone::listAbbreviations()</code> - <a href="https://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices/21198159#21198159">see answer</a>. To keep PHP with up to date Olson data, install periodically <a href="http://pecl.php.net/package/timezonedb" rel="nofollow noreferrer">the timezonedb</a> PECL package; <a href="https://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices/7338519#7338519">see answer</a>.</li> <li>If using C++, be sure to use a library that uses the properly implements the <a href="http://www.iana.org/time-zones" rel="nofollow noreferrer">IANA timezone database</a>. These include <a href="https://github.com/google/cctz" rel="nofollow noreferrer">cctz</a>, <a href="http://site.icu-project.org/" rel="nofollow noreferrer">ICU</a>, and <a href="http://howardhinnant.github.io/date/tz.html" rel="nofollow noreferrer">Howard Hinnant's "tz" library</a>. <ul> <li>Do not use <a href="http://www.boost.org/" rel="nofollow noreferrer">Boost</a> for time zone conversions. While <a href="http://www.boost.org/doc/libs/1_44_0/doc/html/date_time/local_time.html#date_time.local_time.tz_database" rel="nofollow noreferrer">its API</a> claims to support standard IANA (aka "zoneinfo") identifiers, it <a href="https://github.com/boost-vault/date_time/blob/master/date_time_zonespec.csv" rel="nofollow noreferrer">crudely maps them</a> to POSIX-style data, without considering the rich history of changes each zone may have had. (Also, the file has fallen out of maintenance.)</li> </ul></li> <li>If using Rust, use <a href="https://github.com/chronotope/chrono" rel="nofollow noreferrer">chrono</a>.</li> <li>Most business rules use civil time, rather than UTC or GMT. Therefore, plan to convert UTC timestamps to a local time zone before applying application logic.</li> <li>Remember that time zones and offsets are not fixed and may change. For instance, historically US and UK used the same dates to 'spring forward' and 'fall back'. However, in 2007 the US changed the dates that the clocks get changed on. This now means that for 48 weeks of the year the difference between London time and New York time is 5 hours and for 4 weeks (3 in the spring, 1 in the autumn) it is 4 hours. Be aware of items like this in any calculations that involve multiple zones.</li> <li>Consider the type of time (actual event time, broadcast time, relative time, historical time, recurring time) what elements (timestamp, time zone offset and time zone name) you need to store for correct retrieval - see "Types of Time" in <a href="https://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices/3269325#3269325">this answer</a>.</li> <li>Keep your OS, database and application tzdata files in sync, between themselves and the rest of the world.</li> <li>On servers, set hardware clocks and OS clocks to UTC rather than a local time zone.</li> <li><em>Regardless of the previous bullet point,</em> server-side code, including web sites, should <em>never</em> expect the local time zone of the server to be anything in particular. <a href="https://stackoverflow.com/a/30897258/634824">see answer</a>.</li> <li>Prefer working with time zones on a case-by-case basis in your application code, rather than globally through config file settings or defaults.</li> <li>Use <a href="http://en.wikipedia.org/wiki/Network_Time_Protocol" rel="nofollow noreferrer">NTP</a> services on all servers.</li> <li>If using <a href="http://en.wikipedia.org/wiki/File_Allocation_Table#FAT32" rel="nofollow noreferrer">FAT32</a>, remember that timestamps are stored in local time, not UTC.</li> <li>When dealing with recurring events (weekly TV show, for example), remember that the time changes with DST and will be different across time zones.</li> <li>Always query date-time values as <a href="https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common" rel="nofollow noreferrer">lower-bound inclusive, upper-bound exclusive</a> (<code>>=</code>, <code><</code>).</li> </ul> <p><strong>Don't:</strong></p> <ul> <li>Do not confuse a "time zone", such as <code>America/New_York</code> with a "time zone offset", such as <code>-05:00</code>. They are two different things. See <a href="https://stackoverflow.com/tags/timezone/info">the timezone tag wiki</a>.</li> <li>Do not use JavaScript's <code>Date</code> object to perform date and time calculations in older web browsers, as ECMAScript 5.1 and lower has <a href="https://stackoverflow.com/a/16951442/634824">a design flaw</a> that may use daylight saving time incorrectly. (This was fixed in ECMAScript 6 / 2015).</li> <li>Never trust the client's clock. It may very well be incorrect. </li> <li>Don't tell people to "always use UTC everywhere". This widespread advice is shortsighted of several valid scenarios that are described earlier in this document. Instead, use the appropriate time reference for the data you are working with. (<em>Timestamping</em> can use UTC, but future time scheduling and date-only values should not.)</li> </ul> <p><strong>Testing:</strong></p> <ul> <li>When testing, make sure you test countries in the Western, Eastern, Northern and <a href="http://support.microsoft.com/en-us/kb/913551" rel="nofollow noreferrer">Southern</a> hemispheres (in fact in each quarter of the globe, so 4 regions), with both DST in progress and not (gives 8), and a country that does not use DST (another 4 to cover all regions, making 12 in total). </li> <li>Test transition of DST, i.e. when you are currently in summer time, select a time value from winter.</li> <li>Test boundary cases, such as a timezone that is UTC+12, with DST, making the local time <a href="http://en.wikipedia.org/wiki/UTC%2B13:00" rel="nofollow noreferrer">UTC+13 in summer and even places that are UTC+13 in winter</a></li> <li>Test all third-party libraries and applications and make sure they handle time zone data correctly.</li> <li>Test half-hour time zones, at least.</li> </ul> <p><strong>Reference:</strong></p> <ul> <li><a href="https://stackoverflow.com/tags/timezone/info">The detailed <code>timezone</code> tag wiki page on Stack Overflow</a></li> <li><a href="http://www.iana.org/time-zones" rel="nofollow noreferrer">Olson database, aka Tz_database</a></li> <li><a href="http://tools.ietf.org/html/draft-lear-iana-timezone-database-02.html" rel="nofollow noreferrer">IETF draft procedures for maintaining the Olson database</a></li> <li><a href="http://www.twinsun.com/tz/tz-link.htm" rel="nofollow noreferrer">Sources for Time Zone and DST</a></li> <li><a href="http://en.wikipedia.org/wiki/ISO_8601" rel="nofollow noreferrer">ISO format (ISO 8601)</a></li> <li><a href="http://www.unicode.org/cldr/charts/supplemental/zone_tzid.html" rel="nofollow noreferrer">Mapping between Olson database and Windows Time Zone Ids, from the Unicode Consortium</a></li> <li><a href="http://en.wikipedia.org/wiki/Tz_database" rel="nofollow noreferrer">Time Zone page on Wikipedia</a></li> <li><a href="https://stackoverflow.com/questions/tagged/dst">StackOverflow questions tagged <code>dst</code></a></li> <li><a href="https://stackoverflow.com/questions/tagged/timezone">StackOverflow questions tagged <code>timezone</code></a></li> <li><a href="http://msdn.microsoft.com/en-us/library/ms973825.aspx#datetime_topic6" rel="nofollow noreferrer">Dealing with DST - Microsoft DateTime best practices</a></li> <li><a href="http://en.wikipedia.org/wiki/Network_Time_Protocol" rel="nofollow noreferrer">Network Time Protocol on Wikipedia</a></li> </ul> <p><strong>Other:</strong></p> <ul> <li>Lobby your representative to end the abomination that is DST. We can always hope...</li> <li>Lobby for <a href="http://xkcd.com/1061/" rel="nofollow noreferrer">Earth Standard Time</a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload