Standard Point-in-time and time-interval representations

jamiet — Mon, 28 Dec 2009 23:25:16 GMT

One requirement in any database implementation that I have ever worked on is that the notion of a point-in-time has to be represented in some way. The tool that I use on a regular basis, SQL Server, provides numerous datatypes that aid in the representation of a point-in-time and I’m sure that most people reading this will be au fait with them. They are:

I suspect (though stand to be corrected) that the accuracy with which people wish to record a point-in-time is usually to the nearest day or the nearest second however until very recently neither of these accuracies was specifically catered for with a dedicated datatype. Up until the introduction of the date datatype in SQL Server 2008 datetime was the datatype that most would use to record a point-in-time however the accuracy of datetime is neither day nor second, it is 0.00333 seconds; smalldatetime could be used but that also does not have accuracy to the day or second, it is to the minute.

The point I’m trying to make is not that people are using the wrong datatypes to store their data, nor that everyone should just use the most accurate point-in-time datatype (that being datetime2 which is accurate to 100 nanoseconds). No, my point is that there are many different situations which have a need to store a point-in-time and SQL Server does not provide datatypes to cater for all of them. That is what prompted me to raise a Connect submission entitled [T-SQL] Month & Quarter datatype where I asked for datatypes that represent a Month or a Quarter. Simon Sabin commented that he also wanted datatypes to represent a time interval (and Aaron Bertrand pointed out that a Connect submission already exists for that) whilst Rob Farley ran with my idea and suggested that we should have week, hour, semester datatypes as well. There are some good justifications on the submission for why such datatypes are required so if I have failed to convince you here feel free to click through and take a read.

The point of this blog post though is not to drum up support for my Connect submission (though that would be nice), instead it is to make reference to the reply I received from a gentleman on the SQL Server team going by the name ‘Jim’. Jim pointed me to an article on Wikipedia which talks about the ISO 8601 standard that provides an agreed standard for the representation and exchange of date and time related data.

I previously assumed that the only standard date/time formats were something like YYYYMMDD & YYYYMMDDTHH24:MI:SS but not so, ISO 8601 provides representations for centuries, years, months, weeks, ordinal dates, hours, minutes, seconds, fractions of time units, timezones and time intervals (but not quarter, unfortunately). I won’t go into much detail about the standard as you can go and read about it for yourself (and I recommend the wikipedia article as essential reading for any data professional, particularly the General Principles) but for the benefit of both myself and others I have provided below a summary of all the point-in-time representations that the wikipedia article mentions; and fascinating it is too (to me anyway). Take the time to have a read, you may be enlightened:

Valid unambiguous representation	Description
21	21st Century
1981	Year 1981
2004-05 (but not 200405)	5th month of the year 2004
20090106 2009-01-06	6th day of the 1st month of the year 2009
2006W34 2006-W34	34th week of the year 2006
2006W343 2006-W34-3	3rd day of the 34th week of the year 2006
1981095 1981-095	95th day of the year 1981 (better known as 1981-04-05)
21	21st hour of a day
2135 21:35	35th minute of the 21st hour of a day
213507 21:35:07	7th second of the 35th minute of the 21st hour of a day
0000 00:00 2400 24:00	Midnight
1430.5 14:30.5	14 hours, 30 minutes and one half minutes
143050.25 14:30:50.25	14 hours, 30 minutes, 50 seconds and one quarter second
0930Z 09:30Z	9 hours and 30 minutes coordinated universal time (aka UTC time aka Zulu time)
+0100 +01:00	1 hour ahead of UTC
1830Z 22:30+04 1130-0700 15:00-03:30	18 hours and 30 minutes UTC (the point being that these all represent the same point-in-time)
2007-04-05T14:30	30th minute of the 14th hour of the 5th day of the 4th month of the year 2007 (“T” is the standard separator between date and time)
2007-04-05T24:00 2007-04-06T00:00	Midnight inbetween the 5th & 6th days of the 4th month of the year 2007

Some things to note:

Some representations are overloaded (e.g. 21 can mean the 21st century or 21st hour of the day) so context can be a factor.
The week notation is still prone to ambiguities; when does week 1 start? More pertinently, when does week 2 start? Apparently the ISO defines a standard for defining this but it isn’t adhered to as stringently as ISO 8601.
The week notation can also be quite confusing, if the ISO directives are followed to the letter then “2009-W53-7” represents the 3rd day of the first month of 2010 (read the wikipedia article to understand why).

The article also provides some standard representations of durations:

Valid unambiguous representation	Description
P3Y	A period of 3 years
P6M	A period of 6 months
P4D	A period of 4 days
P12H	A period of 12 hours
PT6M	A period of 6 minutes (note the T designation)
P5S	A period of 5 seconds
P3Y6M4DT12H30M5S	A period of three years, six months, four days, twelve hours, thirty minutes, and five seconds
P23DT23H	A period of 23 days and 23 hours

and time intervals:

Valid unambiguous representation	Description
2007-03-01T00:00:00Z/2008-05-11T15:30:00Z	The interval between: midnight (UTC) at the start of the first day of the 3rd month of the year 2007 the 30th minute of the 15th hour (UTC) of the 11th day of the 5th month of the year 2008
2007-03-01T13:00:00Z/P1Y2M10DT2H30M	The interval between: the 13th hour (UTC) of the first day of the 3rd month of the year 2007 the point-in-time exactly 1 year, 2 months, 10 days, 2 hours and 30 minutes later

One last interesting point of trivia, the second edition of ISO 8601 (published in 2000) allowed for 2-digit dates whereas the third (and at the time of writing, current) version (published in 2004) does not. Hmmm… did someone say Y2k?

OK, time to wrap up; why am I writing this? Well, unambiguous representations of data are of paramount importance especially in the distributed heterogeneous XML/JSONified world of data exchange that we live in today so a good understanding of the standards that define those representations should be a pre-requisite to working in that arena. I hope this blog post has highlighted the fact that these standards exist and that you feel compelled to adhere to them in your future work. If you want to understand how damaging it can be to veer from the unambiguous formats then take a read of my recent blog post Unambiguous date formats.

Oh, and if you want to vote for a month datatype in SQL Server, be my guest :)

@Jamiet

Search results matching tags 'T-SQL', 'datetime', and 'date representation'

Standard Point-in-time and time-interval representations