[tmpuzer]

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?

[Pat Phelan]

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

[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, such as:

date_created_utc
utcdate_modified
utc_time_start

or similar?

[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?

[Pat Phelan]

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.

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

[tmpuzer]

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.