dcsimg
Results 1 to 11 of 11

Thread: DateTime Columns and UTC

  1. #1
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Question DateTime Columns and UTC

    This thread is related to the second article in the SqlCredit series: Part 2: Creating the Database, Tables, CRUD Procedures.

    Are you storing datetime in local time or UTC?

    What do you see as benefits and drawbacks?

    If you use UTC, do you “tag” the columns somehow so that it is obvious that the time is UTC and not local?

    Rob
    Last edited by rgarrison; 02-23-2007 at 01:04 PM. Reason: Updated after article published.

  2. #2
    Join Date
    Feb 2007
    Posts
    2

    Yes to UTC

    I work in a Technical Support group and often I have to review error/activity logs written to database tables. Our clients have sites all over the world and it gets confusing to constantly convert time entries between my local time and the client sites' local times (which can span multiple time zones for a single implementation). UTC time (also called Zulu time) gives us a uniform way to compare log entries across multiple time zones.
    Another nice benefit is that you don't have to worry about accounting for Daylight Savings Time changes (which vary from state to state, country to country, and from legislative whims).
    Another big question, is whether to run the servers on the GMT time zone so that Sql error logs, Event logs, etc. are stamped UTC time.
    Here is a useful Wikipedia link on UTC time:
    http://en.wikipedia.org/wiki/Coordinated_Universal_Time

  3. #3
    Join Date
    Feb 2007
    Posts
    3
    I totally understand the point of using UTC as kchristensen mentioned. However, for this SQLCredit Design and Testing demonstration it is not necessary for now. This may become a refractor opportunity later.

  4. #4
    Join Date
    Mar 2007
    Posts
    4
    Quote Originally Posted by jjjin
    I totally understand the point of using UTC as kchristensen mentioned. However, for this SQLCredit Design and Testing demonstration it is not necessary for now. This may become a refractor opportunity later.
    I think that assuming that refactoring to utcdatetime at a later date is a mistake. This is a question that should be resolved upfront. This is an application expected to be used by medium and large banks; medium andlarge banks have widely distributed operations, including, but not limited to offshore customer service.

  5. #5
    Join Date
    Feb 2007
    Posts
    3
    From design process point of view, get it right in the first place is very important, I agree. For this project, from the requirement statement (indicated in the part 1), it did not mention:
    1. Offshore customer support
    2. Distributed databases
    As I remembered, it specified to use Non-Unicode character type also. If globalization is important, UTC and Unicode type should go together.

    I see the benefit of using UTC. But it may increase some coding for translating to local time.

    If the scope of the project and final software installations involves multiple tiers across multiple time zones as the initial requirement, UTC is the right choice.

  6. #6
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64
    The question is this: Is the use of UTC an only-if-required feature or is it a best-practice like using clustered indexes?

  7. #7
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Lightbulb

    Quote Originally Posted by rgarrison
    The question is this: Is the use of UTC an only-if-required feature or is it a best-practice like using clustered indexes?
    (Answering my own question)

    I think that using UTC is a best-practice. The translation of UTC to whatever should be up to the calling code.

  8. #8
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Comments from Scott Hanselman

    I asked Scott Hanselman about this. His reply:

    I’ve always, personally, preferred and promoted using UTC for all dates that are stored anywhere … database or XML. Zulu Time all the way, all the time.

  9. #9
    Join Date
    Jun 2007
    Location
    San Diego, CA
    Posts
    1

    UTC Isn't a Refactoring Opportunity

    I completely agree with rgarrison. UTC isn't a refactoring opportunity, at least ideally. UTC is an up-front choice to make for a system - unless it's guaranteed to stay within a single time zone forever. Uh, yeah.... :-) Just moving my former sole proprietorship from Chicago to San Diego - without initially choosing UTC for mail, databases, logs etcetera - was enough to teach me that lesson; and working with business applications that span at least a couple (US) time zones (something to expect of "large" banks) has only reinforced it.

  10. #10
    Join Date
    Jun 2007
    Posts
    1
    I'm a UTC fanboy, but I'd like to describe some of the downsides here since the comments seem to be mostly positive. UTC will cause some additional work up front that you wouldn't have to deal with otherwise.

    You can get caught up in "What is a day?" type questions which generally happen when aggregating data. Let's say there are two users in the system, one person does some action, say sells a product, and one user runs reports on the sales. If you want to see the number of sales per day, who's day are you talking about? The person who made the sale, or the person who is running the report? Or is the time zone of the report based on the store the sale was made? These are extra questions you have to answer that take up time.

    Looking for trends based on the hour (for instance, "Are people buying more during their lunch hour?") is more work, since you'll have to accurately compare noon in the summer with noon in the winter.

    UTC conversions also has more potential for bugs, since it can be easy to forget to convert the data.

    I wouldn't call UTC a best practice for all applications. It is best practice for applications that work with multiple time zones, where you have to deal with the above issues anyways.
    Last edited by clay_lenhart; 06-18-2007 at 05:04 PM.

  11. #11
    Join Date
    Aug 2007
    Posts
    3

    UTC time

    Quote Originally Posted by clay_lenhart
    I'm a UTC fanboy, but I'd like to describe some of the downsides here since the comments seem to be mostly positive. UTC will cause some additional work up front that you wouldn't have to deal with otherwise.

    You can get caught up in "What is a day?" type questions which generally happen when aggregating data. Let's say there are two users in the system, one person does some action, say sells a product, and one user runs reports on the sales. If you want to see the number of sales per day, who's day are you talking about? The person who made the sale, or the person who is running the report? Or is the time zone of the report based on the store the sale was made? These are extra questions you have to answer that take up time.

    Looking for trends based on the hour (for instance, "Are people buying more during their lunch hour?") is more work, since you'll have to accurately compare noon in the summer with noon in the winter.

    UTC conversions also has more potential for bugs, since it can be easy to forget to convert the data.

    I wouldn't call UTC a best practice for all applications. It is best practice for applications that work with multiple time zones, where you have to deal with the above issues anyways.
    I have never been in a situation where I have used UTC time, but I have had applications where I wish I had used UTC time. It was an emissions monitoring system and managing DST became a nightmare because the server automatically made DST changes.

    As far as clay's comment on what is a day, you could store the time-difference as well and use that when determining a day.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •