Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Storing time: UTC or local?

    I'm creating an application that needs to store start and stop times of user events. The application will be used by users in different states, but currently they are all in the same time zone.

    How should I store the datetimes in the database, using Local time or UTC time? What are the tradeoffs?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm 100% behind using UTC for storage. You may choose to display local time, a common timezone time, or any other time that suits you. The really important thing to remember is ALWAYS RECORD UTC AT THE MOMENT IT IS OBSERVED, once a value is stored any other way, anomolies can occur (such as the dates when DST is observed, the annual "perterbations" needed to support DST, etc). Catch UTC "in the moment", and you are "good to go" for eternity, anywhere on earth.

    -PatP

  3. #3
    Join Date
    Mar 2008
    Posts
    52
    Thanks. So do feel anytime you are adding a datetime column it will 99% of the time be UTC? Also, do you name your columns in any special way to indicate that it is a UTC date, such as:

    date_created_utc
    utcdate_modified
    utc_time_start

    or similar?

  4. #4
    Join Date
    Mar 2008
    Posts
    52
    Also, suppose I am storing a user entered date such as the date a fax was received. Currently I am storing the date only and making sure the time part is zero. How do you handle UTC with these kinds of dates? If I store UTC then the date in the system could be off by a day from the actual date, right?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by tmpuzer
    Thanks. So do feel anytime you are adding a datetime column it will 99% of the time be UTC? Also, do you name your columns in any special way to indicate that it is a UTC date
    Given my choice, all times in the database will be stored as UTC. Other than a dimension table in one rather Kimball-esque data warehouse, I've never seen the need to name columns in any way to indicate what time zone they used.
    Quote Originally Posted by tmpuzer
    Also, suppose I am storing a user entered date such as the date a fax was received. Currently I am storing the date only and making sure the time part is zero. How do you handle UTC with these kinds of dates? If I store UTC then the date in the system could be off by a day from the actual date, right?
    You are basically tweaking the column in ways that don't make good sense to me... I leave data as accurate as possible within the database. I will butcher the data as necessary for presentation purposes. Since a day is a day is a day is a day, you can have the boundary anywhere you like... UTC works just as well as local time, so there is no "day error" as long as you are consistant.

    In your particular example, I'd store the exact UTC time the user entered for when the fax was received. I'd make the decision of how to interpret that when I needed to make that decision, at the time of presentation. Note that this leads to all kinds of issues that your management team will need to think about, especially when comparing against deadlines (are their deadlines local or absolute?).

    I've got a myriad of opinions, all completely logical to me, but none of which are impaired by the need to meet the expectations of your particular business. You'll need to apply the impairments that suit you!

    -PatP

  6. #6
    Join Date
    Mar 2008
    Posts
    52
    Quote Originally Posted by Pat Phelan
    Since a day is a day is a day is a day, you can have the boundary anywhere you like... UTC works just as well as local time, so there is no "day error" as long as you are consistant.

    In your particular example, I'd store the exact UTC time the user entered for when the fax was received.
    I read an article about dates and times in SQL server and it suggested that if you're truly storing a date only and not a date and time that you should make sure the time component is zeroed out which is what I'm doing now.

    Let's take another example, say I want to store the birth date (and as I type this it's starting to sound obvious, but I want to confirm). In this case, do I just store the actual birth date as opposed to a UTC converted birth date?

    Do I not worry about UTC for all "date-only" type fields? It's a little blurry for me how to handle these types of fields when there is only a date component.

Posting Permissions

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