February 26, 2010 at 11:15 am
Steve Jones - Editor (2/26/2010)
Clive Chinery (2/26/2010)
I agree that GMT is more understandable than UTC!I agree here. It's still GMT to me, but I was worried everyone would correct me to be UTC!
Now if we had space ships with 5 year missions to go where none of us has gone before, then UTC makes sense!
Actually no. There you would use TCB (Barycentric Coordinate Time) which is relativistically corrected to the center of mass of the solar system.
...
-- FORTRAN manual for Xerox Computers --
February 26, 2010 at 11:19 am
As former miltary and amateur radio operator, UTC is second nature. But try telling that to civilians and users!
😛
February 26, 2010 at 11:33 am
What he said, including the freak part:
GSquared (2/25/2010)
UTC would be better, in my opinion, than local time.First, it makes calculating duration easier to calculate, because you can ignore "Spring ahead"/"Fall back" issues.
Second, it's easy to make the mistake of having your DR center in a different time zone than your primary server, and leaving the DR server set to its local time zone. Don't have to worry about it if they're both storing UTC.
Third, rules can change on things like time zones (the edges move), Daylight Savings Time (changed just a little while ago in the US), and so on. UTC is easier to keep track of.
Calculations involving time zone offset are easy enough to do at runtime. I think they should be part of the presentation layer, not the data layer.
But I'm also one of those freaks who wants the US to switch to metric measures and 24-hour clocks and should get rid of daylight savings time. I think my work schedule shouldn't be 8 AM to 5 PM EST/EDT, it should be 13:00 UTC to 22:00 UTC.
February 26, 2010 at 11:42 am
I think the important thing is that the time standard be the same across the company, users, clients, etc. It could be any timezone (assuming you drop the DST), as long as it is the same one used across the board and that it is understood.
We could create a new standard: CMT (California Mean Time)!:-P
(just kidding).
--
Kevin C.
February 26, 2010 at 11:51 am
My vote is for UTC with a caveat.
I supported an application that used UTC to store all date values and then had a ton of logic converting to and from time zones inside the database. I ran a trace and did some stats against the number of function calls. Over 80% of the calls in the database were just converting time zones. (actually, close to 90%)
This logic should have been at the presentation layer, but it was placed inside the database instead. The complaints by the customer base about performance issues were not too terribly surprising.
Fixing this after the fact proved to be a daunting challenge, one that I believe remains to be met.
One other question that comes to mind is the display of times prior to the alteration of the DST start and end. Do we code logic to check for the old and new settings? (for some of us the database layer and the presentation layer are our concern)
How about this date/time:
2006-03-23 15:00:00.000 (in local time zone)
This is prior to the change in 2007, but it would be rather complicated to perform DST logic that accounted for the old and new setting.
Most conversion logic would actually make that into:
2006-03-23 14:00:00.000
Why? That date falls in the current start and end dates for DST values after 2007. Should programmers have to write code updates when the administration-de-jour decides changing this will have a positive impact?
One more reason to hate DST...
Hmmm, I have a good QOD related to UTC now. Steve, want to reconsider your rule about no time questions... :o)
February 26, 2010 at 11:54 am
KevinC. (2/26/2010)
I think the important thing is that the time standard be the same across the company, users, clients, etc. It could be any timezone (assuming you drop the DST), as long as it is the same one used across the board and that it is understood.We could create a new standard: CMT (California Mean Time)!:-P
(just kidding).
--
Kevin C.
Now, if it's mean time, and not Median time, what do you base the actual time on? Average Lat\Lon? Population? Instead of time zones I think we should have Continuous Standard Time. When the sun is at the highest point where I am, it's 12 noon. I am completly into me being the standard where I am. 😛
February 26, 2010 at 12:03 pm
Man, I think I am one of two with a dissenting view...I say if your application is local, use local time.
Where I work now, all of our customers are in the U.S. with 98% on the West Coast. We save and display local time PST. I understand the UTC argument and for different applications, I see the benefit of it. But, from a programming standpoint on the presentation layer, you have to display local time because users will not get UTC, GMT or otherwise. So that just means extra work to comply with a hypothetical requirement.
I think that the answer here really depends on individual perspectives...from a DBA standpoint, one universal time throughout all databases is tidy and predictable. From a programming standpoint, the local time is what users are going to want/need which means either you're programming around a standard or working with a format that works best for the application.
just my .02
February 26, 2010 at 2:00 pm
...and why does it have to be Mean time? Can't it be Happy time? Can't we all just get along?
😉
February 26, 2010 at 2:19 pm
UTC. Our servers used to be on the west coast of the US but when the company was bought they were all migrated to the east coast. On the day everything switched over, we ended up with a 3 hour gap in the log events. Our client wanted to know why there was no activity on the site for 3 hours.
Our primary application is web based and all of our IIS logs are UTC. So when I try to compare events in the web logs against events in the db logs I have to do the math and add an extra hour if it's DST.
Now if we could just remember that our DB servers are using US date/time format and the rest of the world isn't and scrub all the dates that come in. .NET is very helpful about automatically converting the format to the user's locale.
14090 SW TENNESSEE LN
February 26, 2010 at 2:21 pm
stevet-1034619 (2/26/2010)
But, from a programming standpoint on the presentation layer, you have to display local time because users will not get UTC, GMT or otherwise. So that just means extra work to comply with a hypothetical requirement.
Displaying date information for the user in their specific time is the reason the DateTime object in .net has the ToLocalTime method.
This works in both Windows and Web apps without a hitch. You store all your dates in UTC and then to display it is one extra method.
instead of myDate.ToString() it is just myDate.ToLocalTime().ToString().
This handles all of the DST conversions and such based on the users DateTime settings in the OS. It adjusts the date correctly and the user never even knows the date is any timezone other than the one the user is currently in.
I totally understand what you mean about being a "local" application but it is so easy to make it more flexible.
Instead of GetDate() as the default on your SysAddDate column change it to GetUTCDate(). This is one of those topics that does not have a right or wrong answer. This is a standard that is implemented in a system. As we all know there is no rule that works in all cases.
_______________________________________________________________
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 3:15 pm
From Steve's editorial:
We recently had a question of the day that asked if times were equivalent, given different numerical times across time zones, and there was quite a debate as to whether the times given were actually equivalent or equal. That was a frustrating debate for me to referee and I am very hesitant to allow other questions dealing with time zones for the time being.
Here was the QOD:
Will all these statements when executed as a batch return an equivalent date, hour, minute and second assuming they all complete within the same second)?
declare @d datetime, @U datetime
select @d = getdate(), @U = getutcdate()SELECT DATEADD(day, -1, @d),DATENAME (dw ,DATEADD(day, -1, @d) )
SELECT @d - 1, DATENAME (dw ,@d - 1)SELECT @U - 1, DATENAME (dw,@u - 1)
SELECT DATEADD(day, -1, @U), DATENAME (dw,DATEADD(day, -1, @U))GO
If you want to read the foaming / ranting turmoil this caused here is the link.
http://www.sqlservercentral.com/questions/Performance+Tuning/68951/
A portion of the explanation was:
These will return equivalent times, however the values returned will differ for getdate() based on your timezone
As the author of the QOD to me it was obvious to me that the question was not about anything other than "a point in time" no matter how measured, but alas that was lost. To me it was surprising how many who should be familiar with such subjects were woefully ignorant of the concept of "a point in time". Oh well now you can understand Steve's reluctance to entertain any addition questions concerning time and its various means of recording/specifying.
February 26, 2010 at 8:18 pm
I hate all times as they are. It's odd that some folks want to move to the metric system and are still perfectly happy with the insanity of a time system that uses a combination of base 24, base 60, and base 10 (milliseconds, etc). Let's shift to something similar to the date serial numbers behind DATETIME values of SQL Server and several other systems. It's kind of like "star dates".
Then, let's change the system that's based on 365.25 days, base 12, and base 7 and call it a day. C'mon... it'll be fun! 😀 At the very least, it'll make it interesting to see if the folks predicting the next doomsday can actually figure out the date. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2010 at 12:25 pm
stevet-1034619 (2/26/2010)
...and why does it have to be Mean time? Can't it be Happy time? Can't we all just get along?😉
The M in GMT is not Mean as in nasty and stingy but as in Average. As the earth's orbit is not circular, the time measured by noon at Greenwich changes by a few minutes throughout the year. Please see http://en.wikipedia.org/wiki/GMT
February 28, 2010 at 2:26 pm
UTC Time is generally the way to go if you have multiple timezones. If your application evolved from a certain timezone and everything is stored as that timezone, its not really a big deal either. The only risk is where you have multiple timezones and are not handling it at all.
However daylight savings time is the killer here in Australia.
If daylight savings could at least start at a consistent time of the year across all our states, then the calculation would be simple, but that is far too much to ask apparently.
The nightmare every year of the effect of MS Daylight Savings patches where servers vary by an hour from the time server dispite configurations suggesting otherwise. Email alerts being sent an hour later than expected (or 23 hours later in some cases), SLA tracking thrown off, security sessions expiring prematurely.
February 28, 2010 at 3:01 pm
As a developer I agree that a standard time system to simplify storage and calculation is preferable, but be aware that applications need to be local-time-aware. We used to have some minor problems with our company spread out across 3 time zones in Australia and 2 more in Asia. Now our main business system is combined into a corporate system in USA. I download sales data out of this into a local SQL server database to feed my Cognos BI system. The downloads are done at midnight our time so the our sales reporting system can update before people start at 7am. Our problem is the business system does its end-of-day processing at midnight USA time which is 10am our time. Any invoices we process before that time are included into yesterday's sales data, modifying the the information already reported and distributed. As our systems become more global we must be more aware of time zones. My pet hate is the use of local seasons particularly the USA use of Fall. I was once given a project target as Fall of 2000, which I took to mean the end of 2000. I later found out that Fall is the US word for Autumn, which was still a useless target because Autumn in the southern hemisphere is 6 months earlier than in the north.
Viewing 15 posts - 46 through 60 (of 68 total)
You must be logged in to reply to this topic. Login to reply