Timing is Everything

  • craig.nixon


    Points: 1

    So what happens when you get a new client that doesn't use your company's version of the time standard?

    I think it's better to use the international UTC time standard and convert to local as necessary. The aviation industry is a prime example where this works. There's is no confusion when UTC is specified.:-)

  • Clive Chinery


    Points: 2563

    craig.nixon (2/28/2010)

    I think it's better to use the international UTC time standard and convert to local as necessary.

    This is much the best way of handling multiple time zones.

  • stevet-1034619

    SSC Veteran

    Points: 289

    Clive Chinery (2/27/2010)

    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

    Yes, I was being facetious...adding a little levity to the conversation. Didn't think anyone would think otherwise...

  • Robert.Smith-1001156

    Mr or Mrs. 500

    Points: 502

    How you store the date is less important than how you retrieve it.

    I inherited an application in which every SQL query had a WHERE clause based on <event time field> + <client timezone offset> being greater than or equal to <desired start time>. Putting the offset calculation in the WHERE clause forced a full table scan for every query despite the fact that <event time field> was indexed.

    Calculating <desired start time> - <client timezone offset> prior to composing the SQL query let the index do it's job and resulted in huge performance gains. (OK it actually eliminated a boneheaded performance loss, but that's not how I describe it on my resume. :-D)


  • Lance Roberts

    SSC Veteran

    Points: 200

    Half of the programming work for the applications I support is all around DST.

    I don't think those people who think DST makes money, realize the cost to industry of having to work around it.

    We use UTC as much as we can in the database.

  • richardd

    Hall of Fame

    Points: 3899

    Jeff Moden (2/26/2010)

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

    It's already been done - the Simpsons, the French and the Chinese have all tried decimal time. 🙂

  • peterhe


    Points: 11363

    When we talk about the time format (UTC or local), we need to think about it in the scope of whole application, not just storage. UTC is clean for storage but may be a problem or burden for the application. Even we use UTC, it does not mean client side need to translate all datetime to local when display it.

    E.g. the Office time is 8:00AM to 4:00PM in Toronto. You don't want your client in west coast see it as 5:00AM to 1:00PM.

    Whether to make the conversion and where to make the conversion depend on the real meaning of the data and how the data is collected, used, and interpreted.

    Now I save data as local time because they are all collected locally. The data will be used globally but the time is the server time.

    For DST, since their definition can be changed, I save all data for the fallback period in seperate DST tables.

  • TomThomson

    SSC Guru

    Points: 104773

    I think a lot of the earlier comments have been oversimplistic - it's not just a matter of saying "UTC is best, stick time localisation in the presentation or application layer" and assuming that will always give you the best solution. What time zone is best to use in the database will depend on your system and on the circumstances in which you are building it.

    If your system is inherently local (for example an in-room entertainment system designed to support a single hotel) then local time is going to be best. You may need a table showing when "summer time" started and ended in various years if you have to export MIS data to an HQ in a different zone but usually I would consider any such need to be a consequence of really stupid design of the MIS system (in the example suggested above I won't want to know what the hotel's customers were billed for access to VoD, MoD, Office Apps, Pay TV, Internet etcetera in a specific 24 hour period defined by my headquarters clock, be that UTC or not, what I need to know is what they were charged in a period for which I will bill the hotel which is of course defined by the local clock at the hotel).

    If your system is inherently global, and not inherently calendar-day oriented, then time should be stored in UTC and the data layer should not provide any zone conversion - the application layer can do that. Probably an inherently global system should adopt that approach even if it is calendar day oriented, but I think there may be exceptions - it needs to be looked at for each particular case.

    If your system is initially local but may grow to spread across time zones, you have a trade-off between a delay in deployment (and extra start-up costs) to do it with UTC and zone conversion above the data layer, and doing it with local time but planning to change over in future before (not when) you need to go global (which is likely to be more expensive in the long run, but that won't matter if doing it is the only way you will see the long run because the extra initial costs and deployment delay of doing it right first time would put you out of business - this really is a case when doing it wrong first may be better, and it has to be looked at very carefully).

    So don't let assumptions about what is "obviously" best make you fail to do a proper analysis of what is best for the system you are building in the circumstances in which you are building it.


  • staffkits


    Points: 4

    Yes of course time is everything.If you have good management ethics than you can get really good future for your family.I also do the same thing whenever i need it.

Viewing 9 posts - 61 through 69 (of 69 total)

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