THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Standard Point-in-time and time-interval representations

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:

Read more at Date and Time Data Types and Functions (Transact-SQL).

date and time data types transact-sql

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

Published Tuesday, December 29, 2009 12:25 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

For date / time (not the other "special cases" you mention) I thought the ISO standard formats were:

YYYY-MM-DD

YYYY-MM-DDTHH:MM:SS.mmm

The important point regarding SQL Server being: you can't trust YYYY-MM-DD, but instead should use YYYYMMDD for string literals.  The former is prone to misinterpretation in certain locales (e.g. Francais).

December 28, 2009 8:28 PM
 

Siddharth Mehta said:

Do we really need a data-type for Month ? Can we not create a user-defined data-type for that ?

December 29, 2009 5:58 AM
 

jamiet said:

Siddharth,

Sure, you can use a user-defined datatype if you want; the same as prior to SQL Server 2008 we could have just created user-defined data types to do the same as [date], [datetime2] & [datetimeoffset] as well. In fact, why bother creating ANY datatypes? Just stick with [int], [numeric] & [nvarchar](max) and let each dev team build their own user-defined datatypes for [datetime],[money],[smallint],[uniqueidentifier],[char],[varbinary](max) etc... !

Perhaps Henry Ford should have sold wheels, engines, gears, seats and chassis' seperately and then we could all fit them together ourselves in order to make our own car (let's ignore the fact that our local mechanic wouldn't be able to fix it when it gpes wrong)?

OK, I'm being sarcastic and condascending (which is not a commendable trait I know) but I hope the point I'm making is obvious. Having datatypes in the box means that we don't all have to go and "recreate the wheel". Having well-understood datatypes in the box promotes consistency and code reuse and also means that the query optimiser understands them. If I see a useful piece of code on your blog that uses a datatype called [siddarthsmonthdatatype] I don't want to have to go and create the user-defined datatype in order that I can use the code.

User-defined data types are not a solution to a problem, they are a workaround. If you want to read more justifications for a [month] datatype then please feel free to read the Connect submission as I suggested above.

-Jamie

December 29, 2009 6:25 AM
 

TiborKaraszi said:

Aaron,

ANSI/ISO SQL (92, 1999, 2003 etc) is limited to YYYY-MM-DD for date. For timestamp (what we call datetime), the format is yyyy-mm-dd hh:mm:ss. I.e., specifically a space between date and time. No letter T.

The problem is that ISO 8601 (a different ISO standard) has as the most formal format yyyy-mm-ddThh:mm:ss. Now, ISO 8601 allows variations from this. One such is yyyy-mm-ss hh:mm:ss - the one we find in ANSI SQL. Another variation is yyyymmdd hh:mm:ss - a format we SQL Server people are very found of.

December 30, 2009 5:08 AM
 

Siddharth Mehta said:

Jamie,

I was on a holiday and just read your reply to my comment today. I would just say that the language you have used to prove your point does not suit a professional of your level. :)

Indeed you are more knowledgeable than me and I would surrender to your idea of Month data-type, but I raised the point of user-defined data type for month based on the following thoughts:

1) I have worked on Oracle, MySQL long time back along with SQL Server and I had never seen a Month data type

2) Incorporation of any data-type in any enterprise class database should be on the requirement from a mass user-base. Month can also be implemented using numeric data-types which would serve similarly good. I read your connect request, but still as yourself I stand with my opinion.

3) If Henry Ford thought of making cars of each n every combination, sizes and colors possible, what would be result ? :) The point I am trying to make is if we start creating data-types for each and every type of enumerations we use in our business systems, where would we end up, and are we so much paralyzed without month data-type as we were without date datatype ? :)

I would think twice next time I post a comment going forward, so that you don't get annoyed. Appologies for my comment :)

January 3, 2010 9:12 PM
 

jamiet said:

Siddharth,

It seems that my reply offended and if that is the case then I apologise - I'm upset that someone such as yourself, whom I respect greatly, has  been offended by this. It was intended to be colloquial but I guess that didn't come across - always a danger with the written word.

On your points:

"1) I have worked on Oracle, MySQL long time back along with SQL Server and I had never seen a Month data type"

True, but I don't see that as a reason not to provide such a thing. Being the first to provide something new may be considered innovative by some.

" Incorporation of any data-type in any enterprise class database should be on the requirement from a mass user-base"

Yes I agree with that and hence why I have put it on Connect to see how much interest there is for this.

"Month can also be implemented using numeric data-types which would serve similarly good"

Sorry, I disagree with this; some reasons for which I have already outlined.

"The point I am trying to make is if we start creating data-types for each and every type of enumerations we use in our business systems, where would we end up"

Well, hopefully we would have many datatypes to suit all manner of situations. Of course there is a trade-off to be made between features and complexity but I think there is great value in providing more datatypes.

"and are we so much paralyzed without month data-type as we were without date datatype"

No, we are not. But that doesn't mean that we should not provide it.

-Jamie

January 4, 2010 1:55 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement