November 19, 2009 at 10:34 am
bitbucket-25253 (11/19/2009)
honza.mf
???? ????
- Russian - in english Foucault (given the name of its discoverer who was a physicist (1819-1868):
"Is a closed electrical eddy currents in massive conductor, which arise when changing the magnetic flux penetrating it." Not at all difficult to translate or understand.
Not difficult? And now try it without Google and/or automatic translators.
First time I've seen this was around 1985.
?????????? ????????
November 19, 2009 at 12:35 pm
Dear Bit-bucket,
The question was badly worded/phrased/whatever.
How about:
What does this query output?
select datediff( second, getdate(), getutcdate())
Answers
a -46800
b -41000
... etc
z difference in seconds between my time and utc time.
Although difficult to do on a multi-guess without giving away the answer as I've almost done, I've questioned the same subject matter and the question I believe is not open to interpretation like yours is.
or for you question:
Will all these statements when executed as a batch refer to approximately the same point in time, say to nearest second or two?
Answer: True - which is the result you wanted.
You confused it by attempting to be overly exact and asking for hour minute and second, and they are not.
Cheers
David
November 19, 2009 at 12:53 pm
Lynn,
>>
Forget the question for a bit. If the columns or variables are properly named and/or documented to allow the developer to know what data is stored in them (local or UTC), then yes they are equivalent as they represent the same point in time. As how they are displayed, that is up to the developer in UI to ensure that occurs.
<<
Then you agree with my point (and the point of most comments): The question is very badly worded.
The part of the question saying equivalent hour, minute, second is just wrong!
Cheers
David
November 19, 2009 at 1:29 pm
Not saying that at all. I'm focusing on what everyone else is focusing on, which is not about the wording of the question.
November 19, 2009 at 1:39 pm
Lynn Pettis (11/19/2009)
Not saying that at all. I'm focusing on what everyone else is focusing on, which is not about the wording of the question.
Lynn,
I read the comments quite differently to you.
Did you see this post?
Karin.Nooteboom-1039448 (11/19/2009)
However these kind of tricky question are ridiculous. They assume that all members of the site speak perfect English and understand the subtle nuances in the phrasing of a question. You would think the essence is knowledge on SQL Server and not on English.Getting tired of word plays.
What do you think of my revised question(s)?
Cheers
David
November 19, 2009 at 2:27 pm
David Todd-242471
You confused it by attempting to be overly exact and asking for hour minute and second, and they are not.
Now now ... the answer to you is that depends .... depends as to when you start the batch and of course on the speed of your processor, and what additional work load is on the server.
My mistake is that I under estimated the manner in which so very many understand time. That is they do not understand / think that now is a single point in time, yet the same people if given a distance between two points on a flat plane, one in Kilometers, the other in equivelent miles, would properly grasp the principle that the measurements are equal, just expressed in different units. What I am trying to say is that some fixated on the numbers, not the concept of a single point in time measured in different units, unfortunately the units (Hours, minutes, seconds bear the same name.)
November 19, 2009 at 2:44 pm
honza.mf
Oops sorry forgot to mention that I was a engineering graduate, class of 1954. Prior to college my high school physics course was taught in German (instructor spoke German, text book was in formal german or high deutch as we called it), even though the high school was located in New York City, U.S.A
I believe that the effect of the varying magnetic field was discovered in the mid 18 hundreds. If you have time research Foucault's pendulum, which it really perplexing, although interesting about as far away from T-SQL and databases as can be had on this earth of ours.
November 19, 2009 at 2:54 pm
bitbucket-25253 (11/19/2009)
David Todd-242471
You confused it by attempting to be overly exact and asking for hour minute and second, and they are not.
Now now ... the answer to you is that depends .... depends as to when you start the batch and of course on the speed of your processor, and what additional work load is on the server.
What I'm saying is that in mentioning hour, minute, second, you are focusing the readers attention on those values, and away from what you've later clarified as the intent of the question, which was the point in time.
depends as to when you start the batch and of course on the speed of your processor, and what additional work load is on the server.
Given that select getdate() consumes almost no resources - cpu or disk or memory - I'd be worried if two adjacent queries in the one batch were very far apart in time!
Cheers
David
PS What do you think of the alternative question and wording I posted?
November 19, 2009 at 3:14 pm
David Todd-242471
What does this query output?
select datediff( second, getdate(), getutcdate())
Answers
a -46800
b -41000
... etc
z difference in seconds between my time and utc time.
To be honest with you.... I would have picked z. For that is what the DATEDIFF function does ... returns the difference in two DATETIME values in the designated measurement which in your example is seconds.
From BOL
Returns the number of date and time boundaries crossed between two specified dates.
November 19, 2009 at 3:57 pm
David Todd-242471
What I'm saying is that in mentioning hour, minute, second, you are focusing the readers attention on those values, and away from what you've later clarified as the intent of the question, which was the point in time.
Agreed it did focus the readers attention to minutiae. In my experience with many individuals using SQL Server and T-SQL too much, way too much attention is paid to minutiae. For example numbers say, -9, -10... now if these represent the percentage lowering of a companies profits, believe you me I would not be concerned about wether it is - 9 or -10, but rather the meaning of these values and how it would affect me, would I have a job tomorrow, next week or next month. For data itself is just a mass of values and keeping them accurate is important, but more important is what these values represent.
Put it another way I would want the measurement of my bad cholesterol to be correct, but more importantly what effect does the value have on my life expectancy.
November 19, 2009 at 4:58 pm
Lynn Pettis (11/19/2009)
Forget the question for a bit. If the columns or variables are properly named and/or documented to allow the developer to know what data is stored in them (local or UTC), then yes they are equivalent as they represent the same point in time. As how they are displayed, that is up to the developer in UI to ensure that occurs.
Lynn,
I totally agree with you but the point is that it is very easy to get different second, minute, hour etc even when running the query in the GMT zone. I had a post earlier (on page 11 of this discussion) showing that it is easily possible even with your query which combined the 4 statements into one to get the second, minute etc numbers different. I thought that the post made sense, but nobody commented on it so I guess it did not. The idea is very simple. The tick in SQL Server is 3 milliseconds, and therefore, there are 3 possible numbers of the rightmost digit of the millisecond part, namely 0, 3, and 7. Thus, if your statement happen to run just before the second ends (at .997) then getDate() calls might show hh:mm:ss:997 and the calls to getUtcDate() might show hh:mm:ss+1:000, so the second part is different. Similarly, if to run the query just before the minute ends at hh:mm:59.997 then then getDate() calls might show hh:mm:59:997 and the calls to getUtcDate() might show hh:mm+1:00:000, so both second and minute time will be different etc. This should automatically mean that even within GMT zone when there is no ambiguity between what results show and what they represent, the second, minute, hour etc parts might be different though they would be the same most times. Thus, the answer to the question should be true most of the time and false otherwise, and there is no guarantee that they are going to be the same as they will be different if the query is executed just before the second / minute / hour etc ends. I believe that true most of the time does not really means true, and therefore it means false instead. Here is your query forced to run just before the second ends. It typically takes about 2 seconds to produce different results on the box set to run in GMT zone.
declare @start int;
set @start = 0;
while @start < 997
set @start = datepart(millisecond, getDate());
go
SELECT DATEADD(day, -1, GETDATE()),DATENAME (dw ,DATEADD(day, -1, GETDATE()) ),
GETDATE() - 1, DATENAME (dw ,GETDATE() - 1),
GETUTCDATE() - 1, DATENAME (dw,GETUTCDATE() - 1),
DATEADD(day, -1, GETUTCDATE()), DATENAME (dw,DATEADD(day, -1, GETUTCDATE()));
go
Oleg
November 19, 2009 at 5:19 pm
Oleg,
I think I addressed this by moving the values to variables. Is that not correct?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 19, 2009 at 5:23 pm
OK, I see both sides of this argument, and I'm not sure either side is correct. The question is confusing, IF you do not see the point of the question. To examine time zone differences around the globe. That was apparent to me, but I was biased in that I saw the question, answer, and explanation all at once.
So, here is what I will do tomorrow. I'll award back all points (again) and then set the answer choice so that everyone from here on out gets points.
However, I will add a note to the question and the first post in this thread to let people know there is a debate and discussion about the original question. There is something to be learned from this debate, probably, and I'd like to keep it up here. If I whack the question, then it goes away.
I would like to see someone attempt to better phrase a question that might test this knowledge. There are plenty of cross zone systems that do need to manage time in an absolute sense. It's not just movie releases, but things like organ recipients. The absolute time in which someone is registered matters.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 19, 2009 at 8:00 pm
Steve Jones - Editor (11/19/2009)
Oleg,I think I addressed this by moving the values to variables. Is that not correct?
Steve,
This is absolutely correct. It is clearly my bad, I did not even check that the question was reworded and the batch restated. As a matter of fact the way the new version of the question is worded makes the correct answer undeniably correct. This in turn makes my argument useless because the question clearly states the assumption about executing the batch within the boundaries of a single second.
Please accept my apologies.
Oleg
P.S. This happened to be one of the best questions after all as it triggered a very long and interesting discussion.
November 20, 2009 at 12:10 am
>>Note that in the discussion, there are a number of comments on the question prior to it being edited. Please understand the idea of the question is to understand time zone issues.<<
This is ***. Question was clear: will date, hour, minutes be the same? As two functions give different results in different timezones results of the queries will differ too. Except UK but we should not expect identical results as this answer suggests.
It seems that author of this question don't understand timezone issues.
--
ptr
Viewing 15 posts - 181 through 195 (of 203 total)
You must be logged in to reply to this topic. Login to reply