Timing is Everything

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    Comments posted to this topic are about the item Timing is Everything

  • GSquared

    SSC Guru

    Points: 260824

    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.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    I could live with the 24 clock, but I like DST, and I like my pounds, inches, and miles!

  • WayneS

    SSC Guru

    Points: 95342

    GSquared (2/25/2010)


    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.

    You mean there's someone else in the US that thinks the way I do on this? Hello, fellow freak! 😀

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • athurgar

    SSCrazy

    Points: 2816

    I agree with Gsquared - UTC is the way to go.

    If you have ever had to move servers across the world or dealt with daylight savings then a common time zone becomes a no brainer.

    Currently I have to deal with data from two providers, one on the East Coast of the US, the other is in Colorado, our servers are in UTC. We then have to do a number of date/time calcs to reconcile the three systems. One time zone used by all would eliminate this issue.

  • Randolph West

    Default port

    Points: 1458

    UTC gets my vote as well. It's simpler to compare times at the database level if necessary, and of course the comments about moving servers, DST apply too.

  • ej.s

    Valued Member

    Points: 67

    As even the smallest business or organisation and even individuals can so easily have a global web presence, it makes good business as well as technical sense to adopt UTC as a common global standard for all.

  • TimJL

    SSC Veteran

    Points: 219

    For a start we should stop pandering to the French and go back to calling it good old GMT. Then we should abandon the abomination of Time zones and (worse) DAYLIGHT SAVING altogether.

    I HATE time zones.

    (Mainly because I still don't automatically understand if '5 hours ahead' means that they get to a given time 5 hours before we do or that for a given time they were there five hours ago.)

    It's even more fun where you have a server in one zone, supporting an application for users in a second, for customers in a third, with reporting in a fourth. And users inability to understand why time zones affect daily and monthly reporting.

    I REALLY HATE time zones.

  • P Jones

    SSChampion

    Points: 12323

    Unless it is done properly and the application is used internationally then I say stick to local time.

    I've fallen foul of this thanks to that heap known as ACT! by Sage.

    The business year is 1 April to 31 Mar and users couldn't understand why certain jobs weren't appearing on a report when they were dated 1 Apr at time 00:00. In fact ACT! had stored them as 31 Mar 23:00 so they were in the previous year!

    Being part of the government, we only work in one country, (well 4 if you are pedantic but it's the UK) so one time zone, but I haven't found a simple, quick-to-execute function for calculating local time and knowing when the clocks are reset, so I ended up adding a fudge to the SSIS and SSRS reports queries to say if the time was 23... add an hour :rolleyes:

  • bill.sugden

    Old Hand

    Points: 339

    Even for our local setup here in the UK it makes sense for most of our systems to use UTC (and I STILL think of it as GMT!). A lot of our usage is logging of operational data, daylight savings really screws up calculations on time differences, for example. With my developer hat on it is a doddle if all times are at a single reference, and all the other developers agree.

    Even our business, not by any means a large enterprise, shares data with a lot of areas throughout Europe, and has a lot of interaction with various UK and EU government bodies. Most of these also specify a single time reference standard. Even when that 'standard' is not UTC it makes conversion easier if your system is single reference.

  • ziangij

    SSCertifiable

    Points: 6979

    UTC is the best option, we have people accessing our application from all time-zones. all updates are done in utc format.

    the only thing people in different time zones need to do is to change the application time setting and they see their local time!

  • amurray-1090291

    SSC Rookie

    Points: 36

    We store everything in UTC along with a "UTC Offset" in hours, calculated at the time the record was inserted. When running a query months later when DST has switched, the historical local times can still be calculated.

    While most conversion is done in our code, we also have a few calculated fields for Local Time with indexes on them for quick searching.

  • Mark Dalley

    SSCrazy

    Points: 2602

    I like summer time. It caught on because it was a good idea (though not for calculating durations!)

    The worst thing is not knowing the calculation rules for a particular time field. Is it GMT, I mean, UTC, or some other time zone, and if so which one? Is daylight saving involved? Is it synchronised with anything, or just adjusted at sporadic intervals and left to drift for the rest of the time? Introducing a fixed standard can only simplify the situation, and synchronized UTC, which is unaffected by the vagaries of daylight saving, is the obvious one.

    I agree with GSquared that date calculations should be part of the presentation layer. Who actually cares how a date is stored, as long as it is displayed in a consistent and expected way? A nice user interface might display a time in the local format (with summer time and all the trimmings) but show the UTC equivalent when you hover your mouse over it. The key thing is that people are sure how a field is to be interpreted, otherwise it is just data, conveying no information.

    Mark Dalley

    P.S. My spelling checker recognizes GMT but throws out UTC - yay!

  • Clive Chinery

    SSCrazy

    Points: 2503

    TimJL (2/26/2010)


    For a start we should stop pandering to the French and go back to calling it good old GMT. Then we should abandon the abomination of Time zones and (worse) DAYLIGHT SAVING altogether.

    It's even more fun where you have a server in one zone, supporting an application for users in a second, for customers in a third, with reporting in a fourth. And users inability to understand why time zones affect daily and monthly reporting.

    I agree that GMT is more understandable than UTC!

    The solution to the time point issue is to specify on all reports not just the data but the time and time zone as well.

  • Clive Chinery

    SSCrazy

    Points: 2503

    GSquared (2/25/2010)


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

    As I understand it, the Dot Net development team now wish that that had stored all times as UTC/GMT and only only converted to a local time for display, but given the complexity of time zones have left it "As Is".

Viewing 15 posts - 1 through 15 (of 69 total)

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