473,320 Members | 1,910 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

getdate() problem: where is the time taken from ?

Hi,

I have a funny situation.
Within: MSSQL 2000 SP3, everything below described is running on same
PC.

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

Now, two other applications, are getting this information at the same
moment.
FIRST of this applications, updates local time of the computer with
the time recieved.
SECOND application, writes a protocol to file, with timestamp read at
moment of writing from operating system.
Until now, all times are equal (the differences are not biger that
ms).

Now, the SECOND application, after writing a log into file (with
proper timestamp), calls SP in database.
It passes as input prm. the time recieved from very first program,
which is the same time as the current system time, which is the same
time the SECOND application writes to the log file.
This SP (besides other things) at the very beginning writes a log into
table, where two times are logged:
- getdate() to first column,
- timestamp recieved as input parameter.

Now the funny thing.
I would expect, the times are equal.
getdate() = '2007.04.25 10:00:00.000'
prm_recieved = '2007.04.25 10:00:00.000'

I would expect, that the time from getdate() will be shifted with
miliseconds (because of call etc).
getdate() = '2007.04.25 10:00:00.123'
prm_recieved = '2007.04.25 10:00:00.000'

I would even expect, that the time is shifted 5 seconds ahead:
getdate() = '2007.04.25 10:00:05.000'
prm_recieved = '2007.04.25 10:00:00.000'

or, 5 seconds and some miliseconds:
getdate() = '2007.04.25 10:00:05.123'
prm_recieved = '2007.04.25 10:00:00.000'

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

It seams to me, the getdate is getting somehow the PERVIOUS local
system time, which was acctualy already upgraded ! Becasue all other
app's are having the proper value.

All other apps are writen in C++ and are very simple.
I was trying to set the SQLServer running property higher - with no
result.
I need to mention, there is SQLServer Agent running, and one procedure
with endless loop, with waitfor delay equal 2 seconds.
But non of them (changing the waitfor delay to other value, disabling
SQLAgent) fixes the problem.

Can somebody then tell me, where from is the time taken, or what is
the root problem of this issue?
Or what can it be?

Best regards,

Matik

Apr 25 '07 #1
6 6370
Matik (ma****@sauron.xo.pl) writes:
there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.
So you have an application that modifies the computer clock every
second, and now you are asking why:
What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'
getdate() does not always reflect you recently updated system time.

I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.

One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 25 '07 #2
First, thanks Erland for the answer!
So you have an application that modifies the computer clock every
second, and now you are asking why:
:) No ... of course, I'm not confused about the time changes :)
I'm expecting them ...
Confused for me, was this:
getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

getdate() does not always reflect you recently updated system time.

I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.

One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.
Ok.
So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.
That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

Thank's Erland again for help.

Matik

Apr 26 '07 #3
Matik (ma****@sauron.xo.pl) writes:
So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.
I like to stress that is my own speculation of how the internals work.
That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.
If my theory is correct, yes, this appears to be a correct conclusion.
Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())
I would expect that CURRENT_TIMESTAMP to be just a synonym fot getdate(). It
would be funny if two equivalent functions are implemented in different
ways.

I also like to point out that this kind of behaviour that could be different
in different versions of SQL Server, or even in different service packs.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 26 '07 #4
The getdate() is not taking the current system time, only the buffered
SQLServer system time.

I like to stress that is my own speculation of how the internals work.
That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

If my theory is correct, yes, this appears to be a correct conclusion.
I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Matik

Apr 27 '07 #5
Matik (ma****@sauron.xo.pl) writes:
I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?
Since all this is about behaviour that is strictly internal to SQL Server,
the likelyhood that there is a interface, documented or undocumented,
to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

If you are on SQL 2005, you could write a CLR function which retrieves
the system time from Windows, with the regular 100 ns precision.

If you are on SQL 2000, you would have to write an extended stored
procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

But getdate() seems dead in the water when you are living in the fast lane
like you do.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 27 '07 #6
to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.
I wont :) Have other things to do as well :)
If you are on SQL 2005, you could write a CLR function which retrieves
I'm on 2000 ... right now ....
If you are on SQL 2000, you would have to write an extended stored
This is what I was thinking of...
procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)
This is what I was afraid of :(
But getdate() seems dead in the water when you are living in the fast lane
like you do.
I just need to change aproach probably, and try to solve it in
different way.
Probably the solution is what I did right now ...
Just, a the beginning, I'm sending procedure to sleep (waitfor delay).
It is not nice, and slows everything down, but maybe ... will be fast
enougth...
Otherwise ... try to do smth. else.

Thanks Erland again for your help and patience.

Best regards

Matik

Apr 27 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Spare Brain | last post by:
Hi, I am observing a strange error with the time portion of the java.sql.Date() when using resultSet.getDate() - it always seems to show 12:00:00. The following code prints 2004-10-27...
5
by: Rajesh Garg | last post by:
Hi, I have written a stored proc with some temporary tables and also used a getdate() in my stored proc. When i try to call the sproc the error is that we can only use extended sprocs or function...
2
by: Konstantin Zakharenko | last post by:
Hello, Our QA team have running a lot of test scripts (for automated regression testing), they run them on the different databases (Oracle/MS SQL). Several of those tests are dependent on the...
10
by: Bill Edwards | last post by:
I'm trying to produce an array of working days but want to force Saturday and Sunday to return the date of the previous Friday, e.g. Sunday 9/12/04 should return Friday 9/10/04 and similarly...
3
by: Yann Laviolette | last post by:
Hi! I look for a function like the Windows function getdate(&d); and gettime(&t); to have the date and the time. Also I look for a function like spawnl to call another program. These function...
4
by: jim_geissman | last post by:
According to MS, GetLocalTime() (in C++) is only accurate to approx a second, even though it reports milliseconds, and calling it twice and computing the interval can on occasion lead to a...
6
by: FFMG | last post by:
Hi, My timezone is GMT +2 So when I do a $date = getdate( 0 ); I get: $date = {
3
by: Yas | last post by:
Hi, I am creating creating a table with a Date column dd-mm-yyyy. But I cant seem to find a SQL function that just returns today's date. getDate() returns the time as well so I cant use it. ...
7
by: Bassem | last post by:
I'm trying to select all records inserted in a certain day, so comparing date only and ignore the time. I searched for a function but didn't found. I found this: WHERE day() = day(GetDate()) AND...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.