T-SQL

  • If someone says the different results

      2009-11-17 13:27:21.190 Tuesday

      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.



    See, understand, learn, try, use efficient
    © Dr.Plch

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



    See, understand, learn, try, use efficient
    © Dr.Plch

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

  • 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?



    See, understand, learn, try, use efficient
    © Dr.Plch

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

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

  • 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:

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

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

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

  • 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

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

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

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

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



    See, understand, learn, try, use efficient
    © Dr.Plch

Viewing 15 posts - 151 through 165 (of 203 total)

You must be logged in to reply to this topic. Login to reply