Timing is Everything

Question

Timing is Everything

Viewing 15 posts - 31 through 45 (of 68 total)

You must be logged in to reply to this topic. Login to reply

hester84 Mr or Mrs. 500 Points: 575 More actions · Answer 1

If you have to work with extensions to a 3rd party application, you need to follow their pattern. I worked on a large health-care system (same as Sean's?), that stored everything in local time. The only way to maintain consistency in our code extension and log tables was to use local time as well -- though we had a big debate in the team.

I argued for storing both local and UTC time, but was voted down as "too much overhead".

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

Why not call it ZULU Time ?

International aviation and the military (NATO included) do with out much ado (and have for quite a number of decades already !)

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

jay-h SSCoach Points: 18816 More actions · Answer 3

tbredehoft (2/26/2010)
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.

Headquarters have been known to move. Sometimes they merge with other organizations with different headquarters. Then all your historic data becomes shaky.

Does headquarters observe daylight saving time? If so, was it in effect when historic record X was created? When did DST go in on the year that record X was created?

UTC is stable, and understood everywhere without ambiguity.

...

-- FORTRAN manual for Xerox Computers --

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

hester84 (2/26/2010)
If you have to work with extensions to a 3rd party application, you need to follow their pattern. I worked on a large health-care system (same as Sean's?), that stored everything in local time. The only way to maintain consistency in our code extension and log tables was to use local time as well -- though we had a big debate in the team.
I argued for storing both local and UTC time, but was voted down as "too much overhead".

That does sound very familiar. By chance was it in Louisville? 😛

_______________________________________________________________

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/

Steve Holle SSC-Addicted Points: 466 More actions · Answer 5

UTC - why anything else? It stores all the information you need, local time doesn't unless you store extra information.

hester84 Mr or Mrs. 500 Points: 575 More actions · Answer 6

hester84

Mr or Mrs. 500

Points: 575

February 26, 2010 at 9:58 am

#1125697

Nope, Honolulu and Urbana

Steve Holle SSC-Addicted Points: 466 More actions · Answer 7

I second, third, whatever that emotion. DST is a wast of time. You cannot "save" daylight unless you have an electrical or thermal storage method and they don't care what time it is. Is the sun shining or not?:-D

Scott Arendt (2/26/2010)
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

Steve Holle SSC-Addicted Points: 466 More actions · Answer 8

All that would have to happen is to open a b<ranch> across one time zone and you have a headache. Or you sell you operation to some big conglomerate...

tbredehoft (2/26/2010)
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.

westmorj Newbie Points: 1 More actions · Answer 9

This question has been asked and re-asked since long distance networking began. It is especially meaningful when dealing with real-time systems which have historization. This occurs in military systems, and manufacturing systems (as well as others). Even more problematic are mobile systems (such as that on aircraft or ships, which can move in and out of time-zones while providing said raw information to a central location(s) for historical purposes. Most of this information is used by analyst types later in order to recreate simulation environments, or to provide feedback on enemy capabilities, etc. In Manufacturing (where the locations are static) historization is used to correlate data between plants (often in different time zones). Statistical analysis is performed on recorded process data which may be at high resolutions (down to the second or less for certain processes). Therefore, my overwhelming experience is that data should use a single reference time zone when put into the DB - and UTC happens to be an accepted standard, so use it. It is a simple task to convert between time zones (I understand that the technical aspects can get tricky - especially at millisecond resolutions) and have all clients store data in UTC. The key isn't UTC, it is standardizing the interface between the storage system and any retrieval systems. Without said standards you cannot correlate the data - and attempting to store TZ settings in a DB to allow for inserting data from any time zone just makes the data stored bigger. In my opinion, using a single reference TZ is the cleanest and easiest way to avoid problems, why not UTC?

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 10

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!

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 11

philnewell (2/26/2010)
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.

This is the important thing. I've designed things to be only in one location, but then the company grows, or more importantly, we host in a remote facility.

I think storing always as GMT/UTC, and then having the client, or even the ODBC/OLEDB/ADO drivers handling the offset, is the way to go.

Steve Holle SSC-Addicted Points: 466 More actions · Answer 12

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!

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 13

Scott Arendt (2/26/2010)
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

I like DST, but I don't like the fact that it varies by country. It certainly gets me into trouble with conference calls across the pond for a few weeks every year!

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 14

I once had a coworker correct me in a large meeting when I used GMT in a discussion. I asked the whole group if anyone did not understand what I meant when I said GMT. No one spoke up.

While not technically correct, GMT is acceptable to most people.

Scott

Steve Holle SSC-Addicted Points: 466 More actions · Answer 15

Steve Holle (2/26/2010)
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!

Wouldn't it be arrogant to assume we are the standard for "Universal" time?:-D