ALTERNATE UNIVERSE DEV

CodingBlocks

102. Why Date-ing is Hard

We take a deep dive into understanding why all Date-s are not created equal while learning that Joe is not a fan of months, King Kong has nothing on Allen, and Michael still uses GETDATE. Oops.

If you’re reading these show notes via your podcast player, you can find this episode’s full show notes and join in the conversation by heading to https://www.codingblocks.net/episode102.

Sponsors

  • Stellares.ai – The AI-powered talent agent that does all the work and screening for you to find your next great opportunity outside of your network. Find the job that’s just right for you.
  • Clubhouse – The first project management platform for software development that brings everyone on every team together to build better products. Sign up for two free months of Clubhouse by visiting clubhouse.io/codingblocks.
  • Datadog.com/codingblocks – Sign up today for a free 14 day trial and get a free Datadog t-shirt after creating your first dashboard.

Survey Says

Anonymous Vote
Sign in with Wordpress
How many conferences do you go to per year?
  • I usually average 1-3 conferences a year.
  • Somewhere in the range of 4-6.
  • More than 6 but nothing crazy.
  • I go to all of them. That I can afford. Or my company can afford.
  • I travel all over speaking at conferences, so I go to too many.
  • Zero. I don't like them, they're too expensive, they take too long, they're too far away, or some other similar reason.
vote

News

  • As is tradition, we take a moment to thank everyone that left us a new review:
    • iTunes: mikejg101, Javi Kroonenburg, Grunf, SeanFinallyLeftAReview, Hippie MX, krauseling, KipWinger777, jamalzada
    • Stitcher: Ominor, JoeIsClearlyTheBest, buggerminator, somethingvague, SGTMcClain, autex, wanda_vi, coderrata, ErichO, Brantley, pbandjamstack, Morten, sofaKingPythonic
    • Disqus: Tomas
  • The entire CB crew will be at Orlando Code Camp, March 30 2019. Be sure to stop by, grab some swag, and chase Joe as you attempt to kick him in the shins.
  • Are full stack developers a myth? Calling all full stack haters.

All about Date-ing

  • Why are we even talking about this? Let’s paint a picture …
    • Something is logged in an application on February 28th 2016 at 10pm in California, PST. (2016 happened to be a leap year)
    • People from different parts of the world use this application
    • How should this show up for someone on the Eastern coast of the US?
      • Was it 1 AM on February 29th?
      • How do you store the information?
      • How do you know how to display it back to the users of your application?
      • Do you show it in the original time zone?
      • Should it show in the time zone of the person looking at it?
  • Knowing your application needs is very important.
    • Consider a security application – if it’s important knowing if things happened during the standard hours of business, it might be important to make sure that the original date/time data is shown in the timezone or offset where the event happened.

ISO 8601 (Wikipedia)

  • An international standard that covers the exchange of date and time related data.
    • Published in 1988.
  • Date and time values are ordered from the largest to smallest unit of time in the following order: year, month (or week), day, hour, minute, second, and fraction of second, such as 2019-03-17T20:11:40+0000,
  • Each value has a fixed number of digits that must be padded with leading zeros.
  • There are two supported formats to represent these date and time values: a basic format with a minimal number of separators or an extended format with separators added to enhance readability.
    • The extended format is preferred and the basic format should be avoided.
    • A hyphen is used as the separator between the date values while a colon is used between the time values.
  • You can reduce the accuracy of any representation by dropping values in the order of least to most significant value.
    • Meaning that “2019-03” is a valid date per ISO 8601.
      • In this case, the value represents March 2019. In this format, the “03” would never represent the day nor anything less.
  • The standard also supports the use of a decimal fraction to the smallest time value in the value being represented.
    • For example, the time portion “12:00.5” would be the same as “12:00:30”.

Years

  • To avoid the Y2K problem, a minimum four-digit year is prescribed.
  • The standard permits expanding the years to provide support for years less before 0000 or after 9999 by using an agreed-upon extra number of digits, prefixed with a + or -, instead of the common AD/BC notation.

Week Dates

  • Instead of using YEAR-MONTH-DAY, the standard also allows for YEAR-WEEKNUMBER-DAY.
  • In this case, the format would look like YYYY-Www where the week number is prefixed with a W to signify that it doesn’t represent the month, such as 2019-W11.
  • When the day is used in this format, it represents the weekday number, 1 (Monday) through 7 (Sunday).
    • For example, in the format YYYY-Www-D, 2019-W11-7 would represent the 7th day (Sunday) of the 11th week of the year 2019.
  • Week 01 has several mutually equivalent and compatible descriptions:
    • It could be the first week that contains the year’s first Thursday. *This is the formal ISO 8601 definition.*
      • Note that the Wikipedia article for ISO 8601 makes a point to call out the additional description of “the week starting with the Monday in the period 29 December – 4 January”, which would make the Thursday in that scenario January 1st, which is already covered by this formal definition.
    • It could be the first week that has January 4th in it.
    • It could be the first week with four or more days in January.
  • This means that if the 1st of January is on a Monday, Tuesday, Wednesday, or Thursday, the week is week 01.
    • Otherwise, if the 1st of January is on a Friday, Saturday, or Sunday, it is the last week of the previous year as December 28th is always included in the last week of its year.

Ordinal Dates

  • This format is used to support the day of the year without the month in the format of YYYY-DDD where DDD is the day of that year ranging from 001 through 365, or 366 for leap years.
  • For example, 2019-076 would represent March 17th, 2019.
  • Use this format when there is a need for dates but full calendar calculations may have a significant performance cost.

Times

  • In the basic format, the time is represented without a separator like [hh][mm][ss] while in the extended format, it is represented with colons as the separator like [hh]:[mm]:[ss].
    • All numbers are zero padded.
    • The [hh] refers to the hour ranging from 00 to 24.
  • Midnight can be represented as either 00:00 or 24:00, depending on whether it is intended to convey the beginning of the day of the date portion or the end.
    • For example, 2019-03-16T00:00:00+0000 would represent the start of the day for March 16th, 2019, while 2019-03-16T24:00:00+0000 would represent the end of the day for March 16th, 2019.
  • Any one of the time elements can include a fraction, provided it is the smallest unit of time being represented. Meaning that the time portion “12:30.5: is valid, but “12:30.5:23” is not.
    • The fractional separator can be either a dot or comma, however, ISO 8601 prefers the comma.
    • There is no limit to the number of decimal places.
Time Zone Designators
  • Per ISO 8601, time zones are either:
    • Assumed to be local time when unspecified,
    • Expressed as UTC,
    • Or expressed as an offset from UTC.
  • While there are use cases where it’s safe to assume local time when communicating within the same time zone, some local times could still differ within the time zone due to regions that observe daylight saving time when compared to those that don’t.
UTC Time, aka Coordinated Universal Time, and Time Offsets from UTC
  • Add a Z directly after the time portion, without a space, to represent a time in UTC, such as “12:00:00Z”.
  • To represent a time offset relative to UTC, add the offset directly after the time portion, without a space, such as “12:00:00-0500” to represent a time that is 5 hours behind UTC.
    • The allowed time offset formats are:
      • ±hh:mm
      • ±hhmm
      • ±hh

Combined Date and Time

  • Date and time information can be represented together by concatenating the date portion with the time portion, using a T as a delimiter like YYYY-MM-DDTHH:MM:SS. For example, 2019-03-17T12:30 would represent 12:30 PM on March 17th, 2019.
  • Basic or extended formats may be used.
  • The date portion may be in calendar, week , or ordinal formats, but must be complete when combined with the time portion since the time portion provides greater accuracy.

Additional Reading

The ISO 8601 standard also supports durations, time intervals, and repeating time intervals.

How SQL Server Date-s

  • DATETIME values are stored as two integers for a total of 8 bytes.
    • The first integer is the date portion, ranging from January 1, 1753 through December 31, 9999.
      • A 0 represents January 1, 1900.
      • Positive integers represent the number of days after January 1, 1900.
      • Negative integers represent the number of days before January 1, 1900.
    • The second integer is the time portion, ranging from 00:00:00 through 23:59:59.997.
      • This integer represents the number of 0.003 seconds after midnight.
      • Values are rounded to the nearest 0.000, 0.003, or 0.007.
    • Note that neither the time zone nor offset are part of this type.
  • Both functions, GETDATE and GETUTCDATE, return a DATETIME type.
  • DATETIME2 has a larger date range as well as a larger default fractional precision or, optionally, a user-specified precision.
    • The date portion can range from 0001-01-01 through 9999-12-31.
    • The time portion can range from 00:00:00 through 23:59:59.9999999.
    • Depending on the time precision, the amount of storage used can vary.
    • Note that neither the time zone nor offset are part of this type.
  • Both functions, SYSDATETIME and SYSUTCDATETIME, return a DATETIME2 type.
  • DATETIMEOFFSET is essentially a DATETIME2 that includes the time offset.
    • The time offset can range from -14:00 through +14:00.
    • Note that the time zone is not part of this type.
  • The SYSDATETIMEOFFSET function returns a DATETIMEOFFSET type.

Closing Reminder

With a time zone, you can determine the time offset. However, with a time offset, you cannot determine the time zone.

Resources We Like

Tip of the Week

  • Make Edge browser read aloud eBook, PDF or Web page in Windows 10 (The Windows Club)
  • While shopping online and you are offered an opportunity to chat with a representative, DO IT. You might get a discount out of the conversation.
  • Use virtual environments (venv) for your Python development. (docs.python.org)

Episode source