SQLServerCentral / SQL Server 2005 / T-SQL (SS2K5) / getdate() accuracy / Latest Posts

RE: getdate() accuracy

JacekO — Thu, 22 May 2008 07:24:05 GMT

The order was supposed to be maintained by the IDENTITY field. Unfortunatelly the DB was not fast enough so we had to log into a file.

RE: getdate() accuracy

john.arnott — Mon, 19 May 2008 15:53:32 GMT

Jacek,Somewhere in the middle of the avatar wars (which did entertain, JM and RB) there may an answer to this, but I'll ask here anyway. Did you you intend the datetime fields to provide order to your logging? If so, have you switched strategies to perhaps keep the event time, but use some other mechanism to record event order accurately?

RE: getdate() accuracy

RBarryYoung — Mon, 19 May 2008 08:59:19 GMT

Thanks Ryan, that did it!

RE: getdate() accuracy

RyanRandall — Mon, 19 May 2008 04:50:24 GMT

[quote][b]rbarryyoung (5/18/2008)[/b][hr]yeah, I can't figure out why though. I've got the right address in the IFcode, but somehow its coming out with this rogue pointer.Here's what I put in:[font="Times New Roman"][b][i]-- RBarryYoung[/i][/b][/font], [font="Times New Roman"][size="1"] (302)375-0451[/size][/font][url=www.sqlserverservices.com][u][font="Arial Black"][i][color="green"]Proactive [size="1"]Performance Solutions, Inc.[/size][/color][/i][/font][/u][/url][font="Verdana"][size="1"] "Performance is our middle name."[/size][/font]You should be able to see it raw if you Quote my post...[/quote]Try removing the quotes... [url=www.sqlserverservices.com] rather than [url="www.sqlserverservices.com"] :)

RE: getdate() accuracy

RBarryYoung — Sun, 18 May 2008 11:02:44 GMT

yeah, I can't figure out why though. I've got the right address in the IFcode, but somehow its coming out with this rogue pointer.Here's what I put in:[font="Times New Roman"][b][i]-- RBarryYoung[/i][/b][/font], [font="Times New Roman"][size="1"] (302)375-0451[/size][/font][url="www.sqlserverservices.com"][u][font="Arial Black"][i][color="green"]Proactive [size="1"]Performance Solutions, Inc.[/size][/color][/i][/font][/u][/url][font="Verdana"][size="1"] "Performance is our middle name."[/size][/font]You should be able to see it raw if you Quote my post...

RE: getdate() accuracy

Jeff Moden — Sun, 18 May 2008 10:47:05 GMT

Shifting gears a bit, Barry... do you realize the link in your signature line is broken?

RE: getdate() accuracy

Jeff Moden — Sun, 18 May 2008 10:44:28 GMT

Way too funny! :) Heh, I guess THAT's why Steve limits the size of the Avatars :hehe:

RE: getdate() accuracy

RBarryYoung — Sat, 17 May 2008 21:35:09 GMT

Yeah? Well let's jst see what happens when we extract your avatar and uncover the hidden right side:[img]http://www.sqlservercentral.com/Forums/Attachment752.aspx[/img]Ha! I knew it!

RE: getdate() accuracy

Jeff Moden — Sat, 17 May 2008 19:44:35 GMT

Heh... LMAO!!! That's funny... RBARry Young... hadn't thought of that. :hehe:

RE: getdate() accuracy

RBarryYoung — Sat, 17 May 2008 18:47:51 GMT

[quote][b]Jeff Moden (5/16/2008)[/b][hr]Heh... what yellow dots?[img]http://www.sqlservercentral.com/Forums/Attachment750.aspx[/img][/quote]OK, now that's just wrong! I'm keeping my eye(s) on you mister...And don't think that I haven't noticed that your "No RBAR" avatar is really just crossing out the first 4 characters of my name. Coincidence? I think not...

RE: getdate() accuracy

Jeff Moden — Fri, 16 May 2008 21:37:19 GMT

Heh... what yellow dots?[img]http://www.sqlservercentral.com/Forums/Attachment750.aspx[/img]

RE: getdate() accuracy

RBarryYoung — Fri, 16 May 2008 18:36:44 GMT

:crazy: Blimey! Whats with all of the smiley faces? :unsure: We've got more yellow dots here than a teenage fanboy with a chocolate addiction! :w00t:;)

RE: getdate() accuracy

Matt Miller (#4) — Fri, 16 May 2008 17:23:28 GMT

[quote][b]rbarryyoung (5/16/2008)[/b][hr][quote][b]Matt Miller (5/16/2008)[/b][hr][quote][b]rbarryyoung (5/16/2008)[/b][hr]...(even Matt got here before me!)...[/quote]Hey now! where the heck did THAT come from??? :hehe::w00t:Sheesh - travel home to see the wife, and get smacked around on the boards...:)[/quote]From you & Jack, mostly :D ...here: [url]http://www.sqlservercentral.com/Forums/FindPost468098.aspx[/url]and here: [url]http://www.sqlservercentral.com/Forums/FindPost468108.aspx[/url]Hey, I don't make this stuff up! I make [b][i]other[/i][/b] stuff up, but not [b]this[/b] stuff! :)[/quote]No issue - just wanted to know what I was getting smacked with:) Just about all is fair in pretty much every aspect on a Friday afternoon!

RE: getdate() accuracy

Matt Miller (#4) — Fri, 16 May 2008 17:19:53 GMT

[quote][b]John Beggs (5/16/2008)[/b][hr]Well, that's better than the other way around, Matt! :D[/quote]No arguing with ya there:)

RE: getdate() accuracy

RBarryYoung — Fri, 16 May 2008 16:46:30 GMT

[quote][b]Matt Miller (5/16/2008)[/b][hr][quote][b]rbarryyoung (5/16/2008)[/b][hr]...(even Matt got here before me!)...[/quote]Hey now! where the heck did THAT come from??? :hehe::w00t:Sheesh - travel home to see the wife, and get smacked around on the boards...:)[/quote]From you & Jack, mostly :D ...here: [url]http://www.sqlservercentral.com/Forums/FindPost468098.aspx[/url]and here: [url]http://www.sqlservercentral.com/Forums/FindPost468108.aspx[/url]Hey, I don't make this stuff up! I make [b][i]other[/i][/b] stuff up, but not [b]this[/b] stuff! :)

RE: getdate() accuracy

John Beggs — Fri, 16 May 2008 15:55:53 GMT

Well, that's better than the other way around, Matt! :D

RE: getdate() accuracy

Matt Miller (#4) — Fri, 16 May 2008 15:31:35 GMT

[quote][b]rbarryyoung (5/16/2008)[/b][hr]...(even Matt got her before me!)...[/quote]Hey now! where the heck did THAT come from??? :hehe::w00t:Sheesh - travel home to see the wife, and get smacked around on the boards...:)

RE: getdate() accuracy

RBarryYoung — Fri, 16 May 2008 15:21:12 GMT

Oops, apparently, I was a little late to this party (even Matt got here before me!), but I believe that it is now apparent what I was saying above: this is an issues with the SQL Server* [i][b]Clock [/b][/i] resolution, and NOT the resolution of the [b][i]datatype[/i][/b].*(note that an application or sw server's clock is not necessairly the same as the system's clock, but it's resolution is normaly always a multiple (1 or more) of the system clock's resolution, never less (without special hardware)).

RE: getdate() accuracy

RBarryYoung — Fri, 16 May 2008 15:07:12 GMT

[quote][b]SQLServerLifer (5/15/2008)[/b][hr]The GETDATE() function returns a DATETIME type. Therefore it will inherit the same rounding limitation.[/quote]Yes, but it might add "Limitations" of its own. In particular (and what this discussion is really about), the internal speed or increment of the "clock" that SQL Server is using may be [i]larger[/i] than 3.33ms. If fact, it may be 13 or 14ms.

RE: getdate() accuracy

Jeff Moden — Fri, 16 May 2008 08:04:46 GMT

[quote][b]JacekO (5/16/2008)[/b][hr]PS.Jeff, It is Jacek not Jack. ;)[/quote]Dang... I'm so sorry... I'm usually the one who get's ticked when someone mispells "Jeff" (believe it or not, it happens). Old eyes playing tricks on me. Thank you for the correction.

RE: getdate() accuracy

JacekO — Fri, 16 May 2008 07:36:20 GMT

There is another twist to this story. The value that the system timer presents to the callers is sometimes interpreted differently by the callers. I run some tests where I read the system time in a .NET app, called a SP to store it in the DB where one of the fields had a default of getdate(). The explicitly entered time value received from the .NET was almost always different by 3 ms then the time generated by the getdate(). The funny part is that the .NET time value was 3ms [b]later[/b] then the getdate() value. It looked like the SQL read the time before I decided to insert the record.PS.Jeff, It is Jacek not Jack. ;)

RE: getdate() accuracy

Jeff Moden — Fri, 16 May 2008 07:28:40 GMT

Very cool, JackO... that's very good to know. Thanks for posting your findings.

RE: getdate() accuracy

JacekO — Fri, 16 May 2008 07:20:37 GMT

OK. I did more testing and more investigating and would like to clarify to all what is going on.First of all the 15 or so ms gaps are not a result of the CPU attending to other tasks. The task switching is much faster then this interval. I run some tests and with multiple threads storing the data the records are intermixed between sources but the time gap remains.The issue is the Windows system timer. It is being updated 64 times per second and this generates the 15.6 (or so) ms intervals. I read somewhere that sometimes this can be changed by some processes but did not find the time to dive into this yet. Some other sources claim that on some systems the timer is updated every 10 ms. So my instinct that this is the getdate() function's internal workings that generate the gaps was correct. Since it uses the system clock and the system clock is quite primitive we can not expect to get the 3 or so accuracy the DATETIME data type supports because of the limitation of the system timer. I run my tests on my WinXP box. I did not have a chance to run it on a Win 2003 server so I don't know if it works the same way or not. So Steve , if you want another question of the day you may explore this issue. :)

RE: getdate() accuracy

John Beggs — Thu, 15 May 2008 18:59:30 GMT

First, to observe the rounding that occurs when working with DATETIME values, run the following:[code]SET NOCOUNT ONDECLARE @Tb TABLE (Dt DATETIME)DECLARE @DT DATETIME, @Cnt INT, @I INTSET @Cnt = 1 --Change this value and observe...SET @Dt = '2008-05-15 17:11:06.390'SET @I = 0WHILE @I < 10 --Change this value and observe as well...BEGIN INSERT @Tb SELECT @Dt SET @I = @I + 1 SET @Dt = DATEADD(ms, @Cnt, @Dt)END SELECT (@Cnt * @I) as Dif_Expected, DATEDIFF(ms, MIN(Dt), MAX(Dt)) as Dif_Actual FROM @Tb[/code]Notice that the DATETIME value never changes if we are just adding 1ms at a time. Think about this over 10,000 iterations and the difference between actual and expected becomes significant.Looking at GETDATE(), it is very clearly returns a DATETIME value and therefore will be rounded. Consider the results of this:[code]SELECT CAST('2008-05-15 17:11:06.390' AS DATETIME) UNION ALLSELECT CAST('2008-05-15 17:11:06.391' AS DATETIME) UNION ALLSELECT CAST('2008-05-15 17:11:06.392' AS DATETIME) UNION ALLSELECT CAST('2008-05-15 17:11:06.393' AS DATETIME) UNION ALLSELECT CAST('2008-05-15 17:11:06.394' AS DATETIME) UNION ALLSELECT CAST('2008-05-15 17:11:06.402' AS DATETIME)[/code]Making sense?Now, looking at the GETDATE() as a part of the inserts, let's consider the loop above. Using it, if we are to do 10,000 iterations with an addition of 1ms each, we would expect 10,000ms or 10sec. Would you be happy if it took SQL 10sec to insert 10,000 dates? Having tried the INSERT loops in previous posts on several SQL servers, the worst time I saw was about 400ms total execution. MUCH better than 10sec. Now consider if there was a 3ms gap on each one, waiting for 30sec! To see my point, run this:[code]SET NOCOUNT ONSELECT GETDATE()DECLARE @Tb TABLE (Dt DATETIME)DECLARE @I INTSET @I = 1WHILE @I < 10000BEGIN INSERT @Tb SELECT GETDATE() SET @I = @I + 1END SELECT GETDATE()SELECT Dt, COUNT(Dt) FROM @Tb GROUP BY Dt[/code]I am sure you will see that while there are "gaps" in the GETDATE() values, they are not there from processing a single record, but from processing several hundred.Hopefully all this is helping me get to my point, which is that we can't expect to see a perfect interval on DATETIME values while inserting. As has been already said, other things are happening on the server. SQL is coordinating events behind the scene maintaining performance and integrity for us. Like Steve said above, SQL isn't for real time capture. For that matter, Windows isn't a real time OS.Hopefully all this makes sense and it isn't too badly formed as I am now running out the door! I'll look to edit any major mistakes later.:)

RE: getdate() accuracy

Jeff Moden — Thu, 15 May 2008 17:36:21 GMT

[quote][b]SQLServerLifer (5/15/2008)[/b][hr]The GETDATE() function returns a DATETIME type. Therefore it will inherit the same rounding limitation.DAB[/quote]Cool! Show us the code that proves it because non of the rest of us can get anything less that about 13 ms between skips.

RE: getdate() accuracy

Matt Miller (#4) — Thu, 15 May 2008 09:21:26 GMT

I hate to say this - but doing a straight select like the initial loop you have, and....I get 10,000 selects, all with the SAME date value.... The only way I get any kind of variation is to do 10,000 1-row inserts, and at that point, there are bunches of duplicate times and the jumps in time are a little all over the place (from 13ms to 33ms).

RE: getdate() accuracy

Steve Jones - SSC Editor — Thu, 15 May 2008 09:01:47 GMT

It is the same precision, but can you get 7:00:00.0007:00:00.0007:00:00.0037:00:00.0037:00:00.0077:00:00.007On my desktop (dual core, 2GHz), I get: .200 (repeated over 100 times).310 (repeated, difference of 110ms).373 (repeated, difference of 63ms).467 (repeated, difference of 94ms)On our SQL 2K server, 2x2.39GHz.653.843.030 (next second).187I think this is something to do with the client and batching rather than precision. If I do this server side, I get:.247.263.280.293.310.327.340multiples of each, alternating between 13 and 17ms. About in counts of 20-25 for each value. Code: create table logger( mydate datetime)goDECLARE @I INTSET @I = 1WHILE @I < 10000BEGIN insert logger select getdate() SET @I = @I + 1END goselect * from loggerMy guess is that in a tight loop like this one, there's still delays as the CPU switches over, the IO catches up (think two sets of writing (log + data) and maybe other efficiencies.SQL Server isn't designed as a real time data capture. It will log to the thousandth of the second (within the 0, 3, 7 values), but it can't necessarily log every ms. You need a real-time capture device to get this and then insert into SQL Server. Even if you send stuff that fast, it won't necessarily insert and be committed in that speed.

RE: getdate() accuracy

Scalability Doug — Thu, 15 May 2008 08:11:38 GMT

The GETDATE() function returns a DATETIME type. Therefore it will inherit the same rounding limitation.DAB

RE: getdate() accuracy

Jeff Moden — Thu, 15 May 2008 08:07:29 GMT

That's good info... but like JackO said, that's for DATETIME precision... not GETDATE precision. We're trying to figure out why a high speed loop seems to indicate that GETDATE won't return that precision.

RE: getdate() accuracy

Scalability Doug — Thu, 15 May 2008 08:00:36 GMT

Look at BOL: "datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table."If you need that extra precision, look into upgrading to SQL 2008 and using the DATETIME2 and TIME data types.DAB

RE: getdate() accuracy

JacekO — Thu, 15 May 2008 07:43:44 GMT

I need this for logging in a multiprocess, multithreaded environment. I understand that the CPU does other things but even when I have multiple callers call the logging SP (I have one field in the DB with a default getdate() ) I can not break the 14 or so ms barrier.The second example does not show the getdate() granuality but the DATETIME granuality. I am wondering if the gatdate() inherently can not handle the full spectrum of DATETIME values.Do you have access to a fast PC that you could run this test and see if on a faster machine you get better then the 14 ms? The one I am using right now is a single core 2.13GHz

RE: getdate() accuracy

Jeff Moden — Wed, 14 May 2008 22:48:08 GMT

The following shows the accuracy you're talking about...[code] SELECT 1,CAST('2000-01-01 23:59:59.990' AS DATETIME) UNION ALL SELECT 2,CAST('2000-01-01 23:59:59.991' AS DATETIME) UNION ALL SELECT 3,CAST('2000-01-01 23:59:59.992' AS DATETIME) UNION ALL SELECT 4,CAST('2000-01-01 23:59:59.993' AS DATETIME) UNION ALL SELECT 5,CAST('2000-01-01 23:59:59.994' AS DATETIME) UNION ALL SELECT 6,CAST('2000-01-01 23:59:59.995' AS DATETIME) UNION ALL SELECT 7,CAST('2000-01-01 23:59:59.996' AS DATETIME) UNION ALL SELECT 8,CAST('2000-01-01 23:59:59.997' AS DATETIME) UNION ALL SELECT 9,CAST('2000-01-01 23:59:59.998' AS DATETIME) UNION ALL SELECT 10,CAST('2000-01-01 23:59:59.999' AS DATETIME) [/code][code]Results:----------- ------------------------------------------------------ 1 2000-01-01 23:59:59.9902 2000-01-01 23:59:59.9903 2000-01-01 23:59:59.9934 2000-01-01 23:59:59.9935 2000-01-01 23:59:59.9936 2000-01-01 23:59:59.9977 2000-01-01 23:59:59.9978 2000-01-01 23:59:59.9979 2000-01-01 23:59:59.99710 2000-01-02 00:00:00.000(10 row(s) affected)[/code]

RE: getdate() accuracy

Jeff Moden — Wed, 14 May 2008 22:41:27 GMT

The CPU does other things... it's not dedicated to running your code.

getdate() accuracy

JacekO — Wed, 14 May 2008 20:05:14 GMT

DECLARE @I INTSET @I = 1WHILE @I < 10000BEGIN SELECT getdate() SET @I = @I + 1END Run the above in query analyzer.I was expecting to get a ton of records that will increment the time by 3 or 4 ms,but what I see is a set of records that increment on average about 14 ms. (not every record gets the incremented time of course, there are tens of records with one time and then another set of records with the incremented time)Funny part is if I output to a file instead the increment jumps to about 30 ms.What is wrong in my assumption about the 3 or 4 ms?