THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search
Home Blogs Forums Roller Downloads   Opml View as RSS news feed in XML

Denis Gobo

SQL Server 2008 Has Nanosecond Precision?

It looks like SQL Server 2008 has nanosecond precision for the time datatype

[edit]I just looked at BOL and yes nanoseconds = ns, microsecond = mcs when used in dateadd[/edit]

If you run the following

DECLARE @t time

SELECT @t ='0:0'

SELECT @t AS Time1,DATEADD(ms,1,@t) AS TimeMilli,

DATEADD(ns,10000,@t) AS TimeNano1,DATEADD(ns,100,@t) AS TimeNano2

 

The output is this

Time1 00:00:00.0000000

TimeMilli 00:00:00.0010000

TimeNano1 00:00:00.0000100

TimeNano2 00:00:00.0000001

 

Another interesting thing is that you can't use 0,'0' or ' ' to assign a value

These 3 will all fail

DECLARE @t time

SELECT @t =' '

 

DECLARE @t time

SELECT @t ='0'

 

DECLARE @t time

SELECT @t =0

 

But this one will succeed

DECLARE @t time

SELECT @t =''
Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Published Wednesday, August 01, 2007 12:42 PM by Denis Gobo
Filed under: , ,

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

 

Dave Markle said:

Wow, fantastic.

Now, do they have a better way of separating the date part of a datetime from the time part of a datetime without using CONVERT()?

August 1, 2007 12:12 PM
 

Denis Gobo said:

They have a new datatype called time

so you can do this

declare @t time

select @t = getdate()

select @t

@t will contain only the time portion

Denis

August 1, 2007 12:15 PM
 

Isaac Kunen said:

One nitpick: It's actually 100ns precision, not 1ns precision.

Cheers,

-Isaac [MS SQL Server]

August 1, 2007 12:55 PM
 

Denis Gobo said:

Isaac, you are right, I failed to mention it, however I did use 100ns myself in the example

DATEADD(ns,100,@t) AS TimeNano2

if you change that to

DATEADD(ns,1,@t) AS TimeNano2, the value stays the same

So when are you adding picoseconds ?<g>

August 1, 2007 1:24 PM
 

Isaac Kunen said:

I guess we'll add picoseconds when there's enough screaming for it.  :)  

It's a valid question to ask why we chose 100ns.  There's a real tradeoff between the precision we offer and the storage overhead for anyone who doesn't need it.  I'm not the PM for this feature, but I'm sure that the exact balance was given quite a bit of thought.

BTW, the datetime2 datatype also has 100ns precision.

Cheers,

-Isaac [MS SQL Server]

August 1, 2007 1:32 PM
 

Denis Gobo said:

Thanks,

I won't even ask why datetimeoffset, date and datetime2 datatypes start from 0001-01-01 instead of 0000-01-01

August 1, 2007 1:39 PM
 

Isaac Kunen said:

:)  

Is there a year zero or not?  I always learned not, although ISO seems to disagree with me.  Since most people don't use dates that far back, it guess it seems best to dodge the question.

(We'll hear from the archeology lobby, I'm sure.)

Cheers,

-Isaac [MS SQL Server]

August 1, 2007 2:44 PM
 

Linchi Shea said:

Having a function or a parameter for nanoseconds is one thing, and having access to nanosecond-precision clock is another thing. I don't think Windows expose anything accurate to a nanosecond. I might be wrong, but Windows granularity of time is milliseconds. Processor counters provide micro-second time granularity. So if no system provides nanosecond precision in its time facility, being able to do nanosecond math doesn't seem to be that useful.

August 1, 2007 10:19 PM
 

Linchi Shea said:

Having a function or a parameter for nanoseconds is one thing, and having access to nanosecond-precision clock is another thing. I don't think Windows expose anything accurate to a nanosecond. I might be wrong, but Windows granularity of time is milliseconds. Processor counters provide micro-second time granularity. So if no system provides nanosecond precision in its time facility, being able to do nanosecond math doesn't seem to be that useful.

August 1, 2007 10:19 PM
 

Denis Gobo said:

maybe a specialized machine does have nanosecond precision and it generates a file which you can import. Also just because they can't do it now doesn't mean they won't be able to do it in 5 years or so

We will see what will happen, either way we are prepared for it  ;-)

August 2, 2007 5:15 AM
 

Dave Markle said:

Still completely unacceptable, they're totally behind.  VHDL has had femtosecond precision for years ;-)

http://archive.chipcenter.com/circuitcellar/february02/c0202ts11.htm

August 2, 2007 9:17 AM
 

Linchi Shea said:

Wow fentosecond! Not sure how to even imagine what that means.

Back to the reality a bit, on a typical PC processor such as Intel Xeon, the intruction pipelines are pretty deep. Even though on a 3GHz processor you have three cycles per nanosecond, it's a safe bet that most instructions take more than three cycles. And if you write code in a high-level language to measure time, you are probably off by many nanoseconds before you even start.

August 2, 2007 10:45 AM
 

Aaron West said:

Does datetime2 still take 64 bits? 86,400 seconds * 10,000,000 = 864 billion, so about 40 bits for the time portion, leaving 24 bits for the date portion, which is enough for 16 million days or almost 46,000 years, so I guess that's enough.

August 6, 2007 12:35 AM
 

Pat Callahan said:

Exercises for SQL Class?

Calculate the age of the universe to the nearest nanosecond.

Calculate your age in gigaseconds.

August 6, 2007 7:12 AM
 

Denis Gobo said:

Gigaseconds???

a day is 86400 seconds so 11574.07407407 days is a Gigasecond

And so a Gigasecond is about 31 years

August 6, 2007 7:49 AM
 

Gordon Rayburn said:

What I'm having a hard time understanding is why you have nanosecond precision, yet the SQL timer only resolves down to 3.33 miliseconds.  We still have the problem of the base timer only offering up milisecond values of 0, 3 or 7.

What good does it do to have a nanosecond precision when the timer only offers up  3.33 millisecond differences?

August 6, 2007 5:07 PM
 

Adam Machanic said:

Gordon,

We might want to store and query some data that originated elsewhere.  Output from a nanosecond-precision scientific instrument, for example.  Just because SQL Server can't generate the data doesn't mean that it's not available :)

August 7, 2007 1:01 PM
 

Gordon Rayburn said:

Thanks Adam, that makes perfect sense, I wasn't looking at it from an import of external data from sources with more accurate precision....and I should have remembered that - after working with Informix unloads to SQL 6.5 all those years ago.  :)

Guess I'll go cruft up some dummy textfiles and see if it retains the accuracy on import...

August 7, 2007 1:32 PM
 

TiborKaraszi said:

Isaac posed the question "Is there a year zero or not?"

Not according to ANSI SQL. In my latest SQL:2006 draft, I find in section 6.1 that "Valid values of datetime fields" for the YEAR part is "0001 to 9999".

ISO 8601 doesn't seem to be available online, and I don't feel like paying just to possibly find out this little bit... :-)

August 10, 2007 3:26 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication
©2006-2008 SQLblog.comTM
Brought to you by Peter DeBetta & Adam Machanic
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement