Timing is Everything

  • jay-h


    Points: 18816

    Steve Jones - Editor (2/26/2010)

    Clive Chinery (2/26/2010)

    I agree that GMT is more understandable than UTC!

    I agree here. It's still GMT to me, but I was worried everyone would correct me to be UTC!

    Now if we had space ships with 5 year missions to go where none of us has gone before, then UTC makes sense!

    Actually no. There you would use TCB (Barycentric Coordinate Time) which is relativistically corrected to the center of mass of the solar system.


    -- FORTRAN manual for Xerox Computers --

  • chrisn-585491


    Points: 15866

    As former miltary and amateur radio operator, UTC is second nature. But try telling that to civilians and users!


  • BrandonChampion

    SSC Journeyman

    Points: 84

    What he said, including the freak part:

    GSquared (2/25/2010)

    UTC would be better, in my opinion, than local time.

    First, it makes calculating duration easier to calculate, because you can ignore "Spring ahead"/"Fall back" issues.

    Second, it's easy to make the mistake of having your DR center in a different time zone than your primary server, and leaving the DR server set to its local time zone. Don't have to worry about it if they're both storing UTC.

    Third, rules can change on things like time zones (the edges move), Daylight Savings Time (changed just a little while ago in the US), and so on. UTC is easier to keep track of.

    Calculations involving time zone offset are easy enough to do at runtime. I think they should be part of the presentation layer, not the data layer.

    But I'm also one of those freaks who wants the US to switch to metric measures and 24-hour clocks and should get rid of daylight savings time. I think my work schedule shouldn't be 8 AM to 5 PM EST/EDT, it should be 13:00 UTC to 22:00 UTC.

  • KevinC.


    Points: 1570

    I think the important thing is that the time standard be the same across the company, users, clients, etc. It could be any timezone (assuming you drop the DST), as long as it is the same one used across the board and that it is understood.

    We could create a new standard: CMT (California Mean Time)!:-P

    (just kidding).


    Kevin C.

  • Douglas Elwood

    SSC Eights!

    Points: 857

    My vote is for UTC with a caveat.

    I supported an application that used UTC to store all date values and then had a ton of logic converting to and from time zones inside the database. I ran a trace and did some stats against the number of function calls. Over 80% of the calls in the database were just converting time zones. (actually, close to 90%)

    This logic should have been at the presentation layer, but it was placed inside the database instead. The complaints by the customer base about performance issues were not too terribly surprising.

    Fixing this after the fact proved to be a daunting challenge, one that I believe remains to be met.

    One other question that comes to mind is the display of times prior to the alteration of the DST start and end. Do we code logic to check for the old and new settings? (for some of us the database layer and the presentation layer are our concern)

    How about this date/time:

    2006-03-23 15:00:00.000 (in local time zone)

    This is prior to the change in 2007, but it would be rather complicated to perform DST logic that accounted for the old and new setting.

    Most conversion logic would actually make that into:

    2006-03-23 14:00:00.000

    Why? That date falls in the current start and end dates for DST values after 2007. Should programmers have to write code updates when the administration-de-jour decides changing this will have a positive impact?

    One more reason to hate DST...

    Hmmm, I have a good QOD related to UTC now. Steve, want to reconsider your rule about no time questions... :o)

  • Steve Holle


    Points: 466

    KevinC. (2/26/2010)

    I think the important thing is that the time standard be the same across the company, users, clients, etc. It could be any timezone (assuming you drop the DST), as long as it is the same one used across the board and that it is understood.

    We could create a new standard: CMT (California Mean Time)!:-P

    (just kidding).


    Kevin C.

    Now, if it's mean time, and not Median time, what do you base the actual time on? Average Lat\Lon? Population? Instead of time zones I think we should have Continuous Standard Time. When the sun is at the highest point where I am, it's 12 noon. I am completly into me being the standard where I am. 😛

  • stevet-1034619

    SSC Veteran

    Points: 289

    Man, I think I am one of two with a dissenting view...I say if your application is local, use local time.

    Where I work now, all of our customers are in the U.S. with 98% on the West Coast. We save and display local time PST. I understand the UTC argument and for different applications, I see the benefit of it. But, from a programming standpoint on the presentation layer, you have to display local time because users will not get UTC, GMT or otherwise. So that just means extra work to comply with a hypothetical requirement.

    I think that the answer here really depends on individual perspectives...from a DBA standpoint, one universal time throughout all databases is tidy and predictable. From a programming standpoint, the local time is what users are going to want/need which means either you're programming around a standard or working with a format that works best for the application.

    just my .02

  • stevet-1034619

    SSC Veteran

    Points: 289

    ...and why does it have to be Mean time? Can't it be Happy time? Can't we all just get along?


  • gary.rumble


    Points: 1739

    UTC. Our servers used to be on the west coast of the US but when the company was bought they were all migrated to the east coast. On the day everything switched over, we ended up with a 3 hour gap in the log events. Our client wanted to know why there was no activity on the site for 3 hours.

    Our primary application is web based and all of our IIS logs are UTC. So when I try to compare events in the web logs against events in the db logs I have to do the math and add an extra hour if it's DST.

    Now if we could just remember that our DB servers are using US date/time format and the rest of the world isn't and scrub all the dates that come in. .NET is very helpful about automatically converting the format to the user's locale.

  • Sean Lange

    SSC Guru

    Points: 286515

    stevet-1034619 (2/26/2010)

    But, from a programming standpoint on the presentation layer, you have to display local time because users will not get UTC, GMT or otherwise. So that just means extra work to comply with a hypothetical requirement.

    Displaying date information for the user in their specific time is the reason the DateTime object in .net has the ToLocalTime method.

    This works in both Windows and Web apps without a hitch. You store all your dates in UTC and then to display it is one extra method.

    instead of myDate.ToString() it is just myDate.ToLocalTime().ToString().

    This handles all of the DST conversions and such based on the users DateTime settings in the OS. It adjusts the date correctly and the user never even knows the date is any timezone other than the one the user is currently in.

    I totally understand what you mean about being a "local" application but it is so easy to make it more flexible.

    Instead of GetDate() as the default on your SysAddDate column change it to GetUTCDate(). This is one of those topics that does not have a right or wrong answer. This is a standard that is implemented in a system. As we all know there is no rule that works in all cases.


    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Ron McCullough

    SSC Guru

    Points: 63877

    From Steve's editorial:

    We recently had a question of the day that asked if times were equivalent, given different numerical times across time zones, and there was quite a debate as to whether the times given were actually equivalent or equal. That was a frustrating debate for me to referee and I am very hesitant to allow other questions dealing with time zones for the time being.

    Here was the QOD:

    Will all these statements when executed as a batch return an equivalent date, hour, minute and second assuming they all complete within the same second)?

    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

    If you want to read the foaming / ranting turmoil this caused here is the link.


    A portion of the explanation was:

    These will return equivalent times, however the values returned will differ for getdate() based on your timezone

    As the author of the QOD to me it was obvious to me that the question was not about anything other than "a point in time" no matter how measured, but alas that was lost. To me it was surprising how many who should be familiar with such subjects were woefully ignorant of the concept of "a point in time". Oh well now you can understand Steve's reluctance to entertain any addition questions concerning time and its various means of recording/specifying.

    If everything seems to be going well, you have obviously overlooked something.


    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Jeff Moden

    SSC Guru

    Points: 995652

    I hate all times as they are. It's odd that some folks want to move to the metric system and are still perfectly happy with the insanity of a time system that uses a combination of base 24, base 60, and base 10 (milliseconds, etc). Let's shift to something similar to the date serial numbers behind DATETIME values of SQL Server and several other systems. It's kind of like "star dates".

    Then, let's change the system that's based on 365.25 days, base 12, and base 7 and call it a day. C'mon... it'll be fun! 😀 At the very least, it'll make it interesting to see if the folks predicting the next doomsday can actually figure out the date. 😛

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Clive Chinery


    Points: 2563

    stevet-1034619 (2/26/2010)

    ...and why does it have to be Mean time? Can't it be Happy time? Can't we all just get along?


    The M in GMT is not Mean as in nasty and stingy but as in Average. As the earth's orbit is not circular, the time measured by noon at Greenwich changes by a few minutes throughout the year. Please see http://en.wikipedia.org/wiki/GMT

  • Daniel Hallam

    Mr or Mrs. 500

    Points: 556

    UTC Time is generally the way to go if you have multiple timezones. If your application evolved from a certain timezone and everything is stored as that timezone, its not really a big deal either. The only risk is where you have multiple timezones and are not handling it at all.

    However daylight savings time is the killer here in Australia.

    If daylight savings could at least start at a consistent time of the year across all our states, then the calculation would be simple, but that is far too much to ask apparently.

    The nightmare every year of the effect of MS Daylight Savings patches where servers vary by an hour from the time server dispite configurations suggesting otherwise. Email alerts being sent an hour later than expected (or 23 hours later in some cases), SLA tracking thrown off, security sessions expiring prematurely.

  • Russell Lees


    Points: 12

    As a developer I agree that a standard time system to simplify storage and calculation is preferable, but be aware that applications need to be local-time-aware. We used to have some minor problems with our company spread out across 3 time zones in Australia and 2 more in Asia. Now our main business system is combined into a corporate system in USA. I download sales data out of this into a local SQL server database to feed my Cognos BI system. The downloads are done at midnight our time so the our sales reporting system can update before people start at 7am. Our problem is the business system does its end-of-day processing at midnight USA time which is 10am our time. Any invoices we process before that time are included into yesterday's sales data, modifying the the information already reported and distributed. As our systems become more global we must be more aware of time zones. My pet hate is the use of local seasons particularly the USA use of Fall. I was once given a project target as Fall of 2000, which I took to mean the end of 2000. I later found out that Fall is the US word for Autumn, which was still a useless target because Autumn in the southern hemisphere is 6 months earlier than in the north.

Viewing 15 posts - 46 through 60 (of 69 total)

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