February 26, 2010 at 5:25 am
I agree that GMT is the only sensible way to store a date and time in the database. If it is supposed to represent a specific point in time, GMT is the only non-ambiguous way to store it. If it doesn't represent a specific point in time, there are more appropriate types to use.
And if you really want to wind up the French, insist on referring to "UTC" as "UK Time Clock"! 😉
February 26, 2010 at 5:37 am
This is an old pet peeve of mine. I believe that time should always be expressed as “now” and distance from now. Distance is perceived by the end user (client) and interpreted. Server storage should always be “now.” UTC gets my vote – it’s the best time on this planet.
February 26, 2010 at 6:08 am
The decision on using UTC dates should be resolved in the design stage of any system. IF! the application in ONLY ever going to use local datetimes, then local time are ok. This would probably be the case for an isolated internal application
However, in any good design one should architect a solution that will handle changes in scope. In today's world the chances of an application not needing to service users in different time zones and not receive data from different timezones is highly unlikely.
If you design an app with local datetimes, and then have to "translate" later on to a different timezone, you will have much bigger issues than if you design for UTC from the start.
My team developed a web based app in 2001 which stored UTC and used the web app's built in capability to translate to local(users) timezone for display. Did you know India was UTC+5:30, you probably know Australia is a day ahead. try explaining to an Aussie, why when he enters something on Tuesday, the application tells him he entered it on Monday!
February 26, 2010 at 6:50 am
amurray-1090291 (2/26/2010)
We store everything in UTC along with a "UTC Offset" in hours, calculated at the time the record was inserted. When running a query months later when DST has switched, the historical local times can still be calculated.While most conversion is done in our code, we also have a few calculated fields for Local Time with indexes on them for quick searching.
I agree.
There are some questions or requirements that could not be met if both are not retrievable in some way.
fiscal reports tend to be based upon a single location
ordering communications requires a single indexed value
sales might be analysed as to at what time of their day do people order
a user wants to know when they did something in their time frame
You cannot workout both unless you can calculate both that means storing one time and one other piece of data to be able to calculate the other type of time. This other piece of data maybe an offset as amurray uses or another time field or a different way of identifying this.
Rarely are we isolated enough to be able to ignore timezones especially if we are dealing with communications.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 26, 2010 at 6:52 am
If it were a perfect world, I'd store the data as UTC (as we say, GMT), with 24 hour times, and have a wicked good assembly for applications to translate the dates and times to the local times.
On a related note about dates and times in SQL Server, why isn't there a default style for CONVERT to show all four year digits without a time?
Examples:
dd/mm/yyyy instead of dd/mm/yy
mm/dd/yyyy instead of mm/dd/yy
February 26, 2010 at 6:53 am
I REALLY HATE time zones.
I really hate Daylight Saving Time.
Even if you store job schedules in UTC (which we do), you still need to handle changes related to DST. That job that runs at 6:00 AM CST, will fire at 7:00 AM CDT.
Plus, every year some country is changing their DST rules.
Scott
February 26, 2010 at 6:58 am
UTC get my vote.
Each time I've worked on a project something around date and time happened and UTC saves part of the day.
- A localized application went centralized amongst several time zones.
- Local reports were now requested by several person amongst several time zones.
- Head office had to be able to restore databases into different time zones which data contains datetime data and should be independent of the time zones
- And on top of it USA changes the daylight saving period which would invalidate date time stored as localized. (So when will we get the 3 days weekend now? 🙂 )
So in the end I'm ready to take additional disk space for this safety net.
February 26, 2010 at 7:17 am
TimJL (2/26/2010)
For a start we should stop pandering to the French and go back to calling it good old GMT. ....
UTC is not simply 'pandering to the French'
The history of GMT is a hodgepodge of different standards. Orignally it was solar mean time at noon at Greenwich, and went through several iterations.
http://www.ucolick.org/~sla/leapsecs/timescales.html
Finally, once we got over trying to use the motion of the earth to calibrate time and move to the atomic standard, it was time for a truly new designation. UTC was born. Actually UTC is only ONE of the associated time standards
http://www.fact-index.com/t/ti/time_scale.html#Some%20time%20standards
...
-- FORTRAN manual for Xerox Computers --
February 26, 2010 at 7:20 am
One thing to keep in mind is the date storage for the system you are working in. I worked on a very large enterprise level health insurance system where the original designers stored all date values in the local timezone. At one point we got a much larger team and one of the developers made the decision that all date values from then on would be stored in UTC. We spent several days trying to track how some timestamps were so far off of what we knew they should be. The lesson there is that sometimes it is better to stick with a previous poor decision than try to make changes "going forward".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 26, 2010 at 7:45 am
I Googled UTC and the first link that came up was University of Tennessee at Chattanooga.
There's got to be a better choice. 😛
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 26, 2010 at 7:48 am
UTC is not simply 'pandering to the French'
It is so! Well the initials at least - ' The International Telecommunication Union wanted Coordinated Universal Time to have a single abbreviation for all languages. English speakers and French speakers each wanted the initials of their respective language's terms to be used internationally: "CUT" for "coordinated universal time" and "TUC" for "temps universel coordonné". This resulted in the final compromise of using "UTC".'
They are just still bitter about the Paris Meridian.
February 26, 2010 at 7:49 am
This is actually an example of where a compromise is just worse for everybody.
February 26, 2010 at 7:58 am
I'm a big believer of UTC and use GetUTCDate as my standard default. Having done it the other way a few times and suffered the pain, it wasn't hard to see the value in UTC.
February 26, 2010 at 8:27 am
Definitely UTC.
With internet activity, trade partners and corporate divisions around the world it is the only thing that makes sense.
Personally I am for the whole world converting to UTC, all at the same time.
And trash the "savings time" insanity.
If I'm chasing an issue with a transaction that took place at 13:43:40 UTC, and everyone in the world is using UTC, it is clear to everyone exactly when it happened.
Local time is too much like operating in POM mode...
February 26, 2010 at 8:29 am
My thought is that I would rather have the time stored as Local time, with the local time zone indicated, so that if you wanted or needed to convert to UTC then you could. In most smaller shops, you only have the Headquarters, where most of the data center is retained. If you stored all dates as UTC, everything would have to be converted ALL the time. If you take my approach, you can convert to UTC if desired.
Todd
Viewing 15 posts - 16 through 30 (of 68 total)
You must be logged in to reply to this topic. Login to reply