November 18, 2009 at 5:32 am
If someone says the different results
2009-11-17 13:27:21.190 Tuesday
2009-11-17 12:27:21.203 Tuesday
2009-11-17 12:27:21.203 Tuesday
are the same, as the first two times are in local time and the later two in UTC, I don't agree. Neither the printed results nor the variables are not marked, if they are local or not.
Just one more Question of the day with very strange 'correct' answer.
I hope the one QOD I created will be accepted better.
November 18, 2009 at 5:40 am
Steve Jones - Editor (11/17/2009)
I have awarded back all points to people as of now. Apologies for the delay, but this was not the high priority of the day.The question was reworded to use a variable and change "same" to equivalent. I am leaving this as the question as I do think that we are returning equivalent data across time zones, which is the idea. This is not a report, this is T-SQL, and anyone running this query ought to understand that the functions are returning equivalent times across time zones. However you would have to handle the time zone display in your client output (that has been added to the explanation).
You can continue to nitpick, however I believe the intention of the question is good and serves to raise awareness of time zone issues.
Sorry, results are neither same nor equivalent. They are different.
Once more: Even the variables don't have a flag showing time zone or UTC information.
The results shows the same timestamp, but from different points of view.
UNC and IP address are equivalent (the same machine) or not (compared as strings) etc.
November 18, 2009 at 6:40 am
honza.mf (11/18/2009)
Steve Jones - Editor (11/17/2009)
I have awarded back all points to people as of now. Apologies for the delay, but this was not the high priority of the day.The question was reworded to use a variable and change "same" to equivalent. I am leaving this as the question as I do think that we are returning equivalent data across time zones, which is the idea. This is not a report, this is T-SQL, and anyone running this query ought to understand that the functions are returning equivalent times across time zones. However you would have to handle the time zone display in your client output (that has been added to the explanation).
You can continue to nitpick, however I believe the intention of the question is good and serves to raise awareness of time zone issues.
Sorry, results are neither same nor equivalent. They are different.
Once more: Even the variables don't have a flag showing time zone or UTC information.
The results shows the same timestamp, but from different points of view.
UNC and IP address are equivalent (the same machine) or not (compared as strings) etc.
It is a question of the day not a best practices. I would hope that developers/DBA's would use appropriately named variables or column names such that others looking at it would be able to tell if a date/time were local or UTC.
November 18, 2009 at 7:21 am
Lynn Pettis (11/18/2009)
honza.mf (11/18/2009)
Sorry, results are neither same nor equivalent. They are different.Once more: Even the variables don't have a flag showing time zone or UTC information.
The results shows the same timestamp, but from different points of view.
UNC and IP address are equivalent (the same machine) or not (compared as strings) etc.
It is a question of the day not a best practices. I would hope that developers/DBA's would use appropriately named variables or column names such that others looking at it would be able to tell if a date/time were local or UTC.
It's not best practice, it's a QoD. The variable names are @d and @U. They are different!
And what about UNC/IP example? The columns can be named server or unc and ip. Do you feel it?
November 18, 2009 at 8:43 am
Feel what? Variable names @d and @U may work in this example, but in a production environment it would not. There, variables and column names should have names that clearly indicate the data contained, such as CreatedDateTimeUTC (getutcdate()) to indicate that the datetime value is UTC. I would go for CreatedDateTime for storing local time (getdate()).
November 18, 2009 at 9:57 am
Lynn Pettis (11/17/2009)
So, you are saying that if I run the following code:
select getdate(), getutcdate()
that the values are not equivalent? Why? They both represent the same moment in time (within a few milliseconds of each other).
Lynn :-
Would you say that this code returns two values that are equivalent ... ?
select 'John Wayne', 'Marion Mitchell Morrison'
(http://en.wikipedia.org/wiki/John_Wayne)
They both represent the same person ...
November 18, 2009 at 10:11 am
the correct answer is False.
Even though the local an UTC represent the same time, when you compare them just as datetime with no referece (local or UTC) they are different unless you are in UK.:angry:
November 18, 2009 at 10:15 am
archie flockhart (11/18/2009)
Lynn :-
Would you say that this code returns two values that are equivalent ... ?
select 'John Wayne', 'Marion Mitchell Morrison'
(http://en.wikipedia.org/wiki/John_Wayne)
They both represent the same person ...
That is an excellent point. I like that kind of thinking. I still disagree with Lynn and this is the best supporting argument so far.
November 18, 2009 at 12:19 pm
Thank you Bitbucket for the excellent QoD.
Thank you everyone else for your excellent view points and comments.
I have learned much today not just about datetime and the two functions (getdate(), getutcdate()) but I also learned more about batches and timing issues and how small changes can end up causing relatively large differences.
Thanks all.
--
Kevin C.
November 18, 2009 at 12:28 pm
archie flockhart (11/18/2009)
Lynn Pettis (11/17/2009)
So, you are saying that if I run the following code:
select getdate(), getutcdate()
that the values are not equivalent? Why? They both represent the same moment in time (within a few milliseconds of each other).
Lynn :-
Would you say that this code returns two values that are equivalent ... ?
select 'John Wayne', 'Marion Mitchell Morrison'
(http://en.wikipedia.org/wiki/John_Wayne)
They both represent the same person ...
Yes.
November 18, 2009 at 12:31 pm
dstemate (11/18/2009)
the correct answer is False.Even though the local an UTC represent the same time, when you compare them just as datetime with no referece (local or UTC) they are different unless you are in UK.:angry:
We aren't talking about comparing them to each other. They represent the same point in time. If you want to compare a UTC time with MST, you have to include the difference between the two time zones.
8:00 AM MST <> 3:00 PM UTC
8:00 AM MST == 3:00 PM UTC
8:00 AM MST = 3:00 PM UTC - 7 hrs
November 18, 2009 at 12:36 pm
Yes I would also agree. The names may be different but refer to the same person. So they are equivalent. But the output of getdate() and getutcdate() is different. getutcdate doesn't specify if it is GMT/local time while comparing to the output of getdate(). If you say is '1/1/2009' same or equivalent to 'Jan 1 2009' then it is YES. But Is 'Jan 1 2009 00:00:00' same or equivalent to 'Jan 1 2009 05:30:30' then its NO, in my opinion.
November 18, 2009 at 12:56 pm
rparakalla (11/18/2009)
Yes I would also agree. The names may be different but refer to the same person. So they are equivalent. But the output of getdate() and getutcdate() is different. getutcdate doesn't specify if it is GMT/local time while comparing to the output of getdate(). If you say is '1/1/2009' same or equivalent to 'Jan 1 2009' then it is YES. But Is 'Jan 1 2009 00:00:00' same or equivalent to 'Jan 1 2009 05:30:30' then its NO, in my opinion.
If you aren't told that one time is local and the other UTC, I'd agree. Based on the question asked, however, you DO KNOW one is local and one is UTC. In an actual production application, you'd expect that the variable names or column names would also indicate that, and if not, the data dictionary that provides you with the details of each column should provide that detail to you.
November 18, 2009 at 1:02 pm
Lynn Pettis (11/18/2009)
rparakalla (11/18/2009)
Yes I would also agree. The names may be different but refer to the same person. So they are equivalent. But the output of getdate() and getutcdate() is different. getutcdate doesn't specify if it is GMT/local time while comparing to the output of getdate(). If you say is '1/1/2009' same or equivalent to 'Jan 1 2009' then it is YES. But Is 'Jan 1 2009 00:00:00' same or equivalent to 'Jan 1 2009 05:30:30' then its NO, in my opinion.If you aren't told that one time is local and the other UTC, I'd agree. Based on the question asked, however, you DO KNOW one is local and one is UTC. In an actual production application, you'd expect that the variable names or column names would also indicate that, and if not, the data dictionary that provides you with the details of each column should provide that detail to you.
Yes, that is what has caused all the confusion. Just from the query you know that it is getdate() representing local datetime and getutcdate() representing GMT datetime. But not from the output if we run the code:
declare @d datetime, @U datetime
select @d = getdate(), @U = getutcdate()
SELECT DATEADD(day, -1, @d),DATENAME (dw ,DATEADD(day, -1, @d) )
SELECT @d - 1, DATENAME (dw ,@d - 1)
SELECT @U - 1, DATENAME (dw,@u - 1)
SELECT DATEADD(day, -1, @U), DATENAME (dw,DATEADD(day, -1, @U))
GO
The general tendency is to compare the results and not the system function names I guess.
November 18, 2009 at 1:11 pm
archie flockhart (11/18/2009)
Lynn Pettis (11/17/2009)
So, you are saying that if I run the following code:
select getdate(), getutcdate()
that the values are not equivalent? Why? They both represent the same moment in time (within a few milliseconds of each other).
Lynn :-
Would you say that this code returns two values that are equivalent ... ?
select 'John Wayne', 'Marion Mitchell Morrison'
(http://en.wikipedia.org/wiki/John_Wayne)
They both represent the same person ...
Thanks, your example is much more better than my UNC / IP.
Viewing 15 posts - 151 through 165 (of 203 total)
You must be logged in to reply to this topic. Login to reply