Re: GMT VS LOCAL TIME PROBLEM

Hi Larry,
This is an often very underestimated problem you're digging in. I've been responsible for an airport application, that was (bad) designed to display flight information to passengers and ground crew. Let me start telling you that in my opinion writing the data into the database in GMT 9I guess you mean actually UTC, at least I hope so) is the right decision. And believe me, the company I headed those day got a contract of over 1,5 man-year to clean up 500K lines of messy C-code, that was trying to handle this correctly. We got the contract when the original provider went broke, for obvious reaqsons ;-).

The main problem of a well-designed solution is that DST is a political issue. This has the consequence that the starting and ending day of DST theoretically may vary, and cannot be implemented using a simple algorithm. Actually, some years ago the European continent (at least a large part of it) shifted the ending date from September to October.

The solution we choose roughly had the following components: - a table (TZ), containing the start and end date and time (in UTC) of the last and upcoming 10 years and the difference in seconds for evry interval. If you work in different time-zondes with the results, you need an entry per year per TZ in the table. - a package with conversion functions. We had four formats: Human readable (much like your string I guess: format yyyymmddhh24miss) in both UTC and Local Time and 'Unix notation': the number of seconds since 01-01-1970 00:00:00, also in both UTC and LT. The conversion procedure had 4 in-out parameters, accepted one value (no matter which) and calculated the other three.
- All date fields got 3 copies, one for every format, in the tables. This was because it was a display system, and data had a update once, read many characteristic. This approach saved a huge amount of on the fly conversions and also enabled us indexing LT-values for query-purposes (This was the before-functionbased indexes era). The copies got filled using triggers, in their turn using thy conversion package.

We needed the TZ-table because Unix TZ environment variables do not have the year-component. Every program read the table during initialization. When a dat had to be converted, the program did a lookup in the internal table to find out what year the date was in. If this was another year than the current TZ-environment variable represented, the new TZ-value was pushed in the environment of the program before calling unix date-functions. Of course the global variable holding the 'current TZ-year' got updated as well.

Special treatment is needed for the hours around the DST-change. On the the start-date LT jumps an hour forward, so you actually miss one hour. At the end it is the other way around: you get the same hour twice in LT. This is one of the main reasons to store your data in UTC! UTC is the only representation that continues the right way.

I don't know whether all new feautures of Oracle have made our work obsolete, I haven't looked into the subject with 9i and 10g. However, getting a clear understanding of the subject is quite hard in my experience, newbies to the subject always tend to underestimate the complexity of it.

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok) ===

On Mon, 2004-09-13 at 23:28, Wolfson Larry - lwolfs wrote:

> I'm trying to help one of our DBAs.
> He posed this timestamp problem and I'm not sure how to resolve it.
>
> "Our external gauges are writing their values into the database
> information, along with the time the reading was taken, the time is being
> written into the database as a character string and is written as a GMT
> date.
> Other systems reading this information need to convert the string to
> a date and to their local time zone thus need to be aware of the time-zone
> they're operating in and take into account whether DST was/is in operation
> at the time of the reading"
>
> We're collecting real-time machine statistics from many
> manufacturing plants across the country. Roughly 3K/hour from 20 plants or
> every time a gauge changes. It's a purchased app and we've been
> unsuccessful getting an application change. This data is forwarded to a SAP
> DB that wants the local time. He's thinking about writing a C program as a
> function to do the time conversion. The biggest problem is adjusting the
> time as it goes to DST and back.
> The sources are Oracle DBs on INTEL machines at each plant. These
> are fed data from the collecting application.
>
> Anyone doing this already?
>
> Thanks
> Larry
>
>
>
>
> **********************************************************************
> The information contained in this communication is
> confidential, is intended only for the use of the recipient
> named above, and may be legally privileged.
> If the reader of this message is not the intended
> recipient, you are hereby notified that any dissemination,
> distribution, or copying of this communication is strictly
> prohibited.
> If you have received this communication in error,
> please re-send this communication to the sender and
> delete the original message or any copy of it from your
> computer system. Thank You.
>
> --
> To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe
> To search the archives - http://www.freelists.org/archives/oracle-l/

--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/