Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


getdate() accuracy


getdate() accuracy

Author
Message
John Beggs
John Beggs
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 663
First, to observe the rounding that occurs when working with DATETIME values, run the following:

SET NOCOUNT ON

DECLARE @Tb TABLE (Dt DATETIME)
DECLARE @DT DATETIME, @Cnt INT, @I INT

SET @Cnt = 1 --Change this value and observe...
SET @Dt = '2008-05-15 17:11:06.390'

SET @I = 0
WHILE @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



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:

SELECT CAST('2008-05-15 17:11:06.390' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.391' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.392' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.393' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.394' AS DATETIME) UNION ALL
SELECT CAST('2008-05-15 17:11:06.402' AS DATETIME)



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:

SET NOCOUNT ON

SELECT GETDATE()
DECLARE @Tb TABLE (Dt DATETIME)

DECLARE @I INT

SET @I = 1
WHILE @I < 10000
BEGIN
   INSERT @Tb
   SELECT GETDATE()

SET @I = @I + 1
END

SELECT GETDATE()
SELECT Dt, COUNT(Dt) FROM @Tb GROUP BY Dt



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.Smile



JacekO
JacekO
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 606
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. Smile

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44990 Visits: 39874
Very cool, JackO... that's very good to know. Thanks for posting your findings.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JacekO
JacekO
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 606
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 later 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. Wink

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44990 Visits: 39874
JacekO (5/16/2008)

PS.
Jeff,
It is Jacek not Jack. Wink


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.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
SQLServerLifer (5/15/2008)
The GETDATE() function returns a DATETIME type. Therefore it will inherit the same rounding limitation.


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 larger than 3.33ms. If fact, it may be 13 or 14ms.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
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* Clock resolution, and NOT the resolution of the datatype.

*(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)).

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7639 Visits: 18060
rbarryyoung (5/16/2008)
...(even Matt got her before me!)...


Hey now! where the heck did THAT come from??? Hehew00t

Sheesh - travel home to see the wife, and get smacked around on the boards...Smile

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
John Beggs
John Beggs
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 663
Well, that's better than the other way around, Matt! BigGrin



RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
Matt Miller (5/16/2008)
rbarryyoung (5/16/2008)
...(even Matt got here before me!)...


Hey now! where the heck did THAT come from??? Hehew00t

Sheesh - travel home to see the wife, and get smacked around on the boards...Smile

From you & Jack, mostly BigGrin ...

here: http://www.sqlservercentral.com/Forums/FindPost468098.aspx

and here: http://www.sqlservercentral.com/Forums/FindPost468108.aspx

Hey, I don't make this stuff up! I make other stuff up, but not this stuff! Smile

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search