February 28, 2010 at 4:57 pm
So what happens when you get a new client that doesn't use your company's version of the time standard?
I think it's better to use the international UTC time standard and convert to local as necessary. The aviation industry is a prime example where this works. There's is no confusion when UTC is specified.:-)
March 1, 2010 at 7:01 am
craig.nixon (2/28/2010)
I think it's better to use the international UTC time standard and convert to local as necessary.
This is much the best way of handling multiple time zones.
March 1, 2010 at 9:01 am
Clive Chinery (2/27/2010)
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
Yes, I was being facetious...adding a little levity to the conversation. Didn't think anyone would think otherwise...
March 1, 2010 at 9:35 am
How you store the date is less important than how you retrieve it.
I inherited an application in which every SQL query had a WHERE clause based on <event time field> + <client timezone offset> being greater than or equal to <desired start time>. Putting the offset calculation in the WHERE clause forced a full table scan for every query despite the fact that <event time field> was indexed.
Calculating <desired start time> - <client timezone offset> prior to composing the SQL query let the index do it's job and resulted in huge performance gains. (OK it actually eliminated a boneheaded performance loss, but that's not how I describe it on my resume. :-D)
Robb
March 1, 2010 at 10:58 am
Half of the programming work for the applications I support is all around DST.
I don't think those people who think DST makes money, realize the cost to industry of having to work around it.
We use UTC as much as we can in the database.
March 2, 2010 at 6:45 am
Jeff Moden (2/26/2010)
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".
It's already been done - the Simpsons, the French and the Chinese have all tried decimal time. 🙂
March 2, 2010 at 9:45 am
When we talk about the time format (UTC or local), we need to think about it in the scope of whole application, not just storage. UTC is clean for storage but may be a problem or burden for the application. Even we use UTC, it does not mean client side need to translate all datetime to local when display it.
E.g. the Office time is 8:00AM to 4:00PM in Toronto. You don't want your client in west coast see it as 5:00AM to 1:00PM.
Whether to make the conversion and where to make the conversion depend on the real meaning of the data and how the data is collected, used, and interpreted.
Now I save data as local time because they are all collected locally. The data will be used globally but the time is the server time.
For DST, since their definition can be changed, I save all data for the fallback period in seperate DST tables.
March 2, 2010 at 11:23 am
I think a lot of the earlier comments have been oversimplistic - it's not just a matter of saying "UTC is best, stick time localisation in the presentation or application layer" and assuming that will always give you the best solution. What time zone is best to use in the database will depend on your system and on the circumstances in which you are building it.
If your system is inherently local (for example an in-room entertainment system designed to support a single hotel) then local time is going to be best. You may need a table showing when "summer time" started and ended in various years if you have to export MIS data to an HQ in a different zone but usually I would consider any such need to be a consequence of really stupid design of the MIS system (in the example suggested above I won't want to know what the hotel's customers were billed for access to VoD, MoD, Office Apps, Pay TV, Internet etcetera in a specific 24 hour period defined by my headquarters clock, be that UTC or not, what I need to know is what they were charged in a period for which I will bill the hotel which is of course defined by the local clock at the hotel).
If your system is inherently global, and not inherently calendar-day oriented, then time should be stored in UTC and the data layer should not provide any zone conversion - the application layer can do that. Probably an inherently global system should adopt that approach even if it is calendar day oriented, but I think there may be exceptions - it needs to be looked at for each particular case.
If your system is initially local but may grow to spread across time zones, you have a trade-off between a delay in deployment (and extra start-up costs) to do it with UTC and zone conversion above the data layer, and doing it with local time but planning to change over in future before (not when) you need to go global (which is likely to be more expensive in the long run, but that won't matter if doing it is the only way you will see the long run because the extra initial costs and deployment delay of doing it right first time would put you out of business - this really is a case when doing it wrong first may be better, and it has to be looked at very carefully).
So don't let assumptions about what is "obviously" best make you fail to do a proper analysis of what is best for the system you are building in the circumstances in which you are building it.
Tom
March 19, 2010 at 12:55 pm
Yes of course time is everything.If you have good management ethics than you can get really good future for your family.I also do the same thing whenever i need it.
Viewing 9 posts - 61 through 68 (of 68 total)
You must be logged in to reply to this topic. Login to reply