Printable View
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
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
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.Quote:
Originally Posted by jjjin
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.
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)Quote:
Originally Posted by rgarrison
I think that using UTC is a best-practice. The translation of UTC to whatever should be up to the calling code.
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.
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.
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.Quote:
Originally Posted by clay_lenhart
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.