vogt@iuvax.cs.indiana.edu (Michael Vogt) (04/05/91)
Hi, I'm hoping you can help us with a design problem we have come across. What date/time format do people use when dealing with data that crosses date and time zones? How are time changes like daylight savings time and standard time dealt with? How do distributed databases handle date/time if the databases are located in different zones? How do flight/navigation/distribution systems handle different date/time zones? Our specific problem is this: We receive laboratory specimens from across the world. We record the date and time of the specimen draw (as recorded at the location of the draw). We also record our time of receipt (as specified by our location). We need to be able to calculate the number of hours between the draw and our receipt so that we will know if the specimen's stability has expired. Now the constraints: We don't want to ask for zone, (indeed we don't know if all locations have unique identifiers for time zones (i.e. EST)). We will know the location of the specimen draw. ANY help/insight is appreciated! Thanks -Mike My preferred address is vogt@fermat.mayo.edu
dmark@acsu.buffalo.edu (David Mark) (04/06/91)
In article <1991Apr5.103756.18982@news.cs.indiana.edu> vogt@iuvax.cs.indiana.edu (Michael Vogt) writes: >Hi, > >I'm hoping you can help us with a design problem we have come across. > >What date/time format do people use when dealing with data >that crosses date and time zones? How are time changes like daylight >savings time and standard time dealt with? > It seems that the way email and news handles this would be a good way. This seems not completely consistent, however. Most of the email and news I received today has dates and times in GMT (Greenwich Mean Time): Date: 5 Apr 91 21:08:15 GMT Date: 5 Apr 91 06:18:40 GMT Date: 5 Apr 91 10:21:27 GMT etc. But one item I got today had a local time/date expressed thusly: Date: Wed, 3 Apr 91 09:19:03 -0800 (that is Pacific Standard Time). If the database and the transactions are all in GMT, or in offsets from GMT, then all you need is a good lookup table for local time, whether daylight savings time is in effect, etc. > How do flight/navigation/distribution systems handle different > date/time zones? Occasionally, they don't handle it perfectly. About three years ago, we went to a conference in Phoenix, Arizona, and the dates straddled the start of datlight savings time in the USA. The itinerary from the airline or travel agent had the time one-hour wrong (and one of us almost missed his flight) because the system did not "know" that Arizona does NOT go on daylight savings time! David Mark dmark@acsu.buffalo.edu
dptom@endor.corp.sgi.com (Tom Arnold) (04/06/91)
In article <1991Apr5.103756.18982@news.cs.indiana.edu>, vogt@iuvax.cs.indiana.edu (Michael Vogt) writes: |> Hi, |> |> I'm hoping you can help us with a design problem we have come |> across. |> |> What date/time format do people use when dealing with data |> that crosses date and time zones? How are time changes like |> daylight |> savings time and standard time dealt with? We encountered a similar problem putting together a worldwide marketing information database. Sales transactions occur through out the world. For instance, we are in Mountain View, CA on the west coast. Our factory in Kawasaki, Japan would commit transactions a day ahead of us. This works great until the end of a fiscal period. Then What? Our approach was to use GMT at the database. In the interface, the GMT value stored in the database is converted to display in the local time zone. This allows us to manage and recognize the exact sequence of transactions throughout our worldwide network.
stachour@sctc.com (Paul Stachour) (04/06/91)
The MIT/GE/ATT/Honeywell Multics system has, for almost 20 years,
kept everything concerning time in a GMT-reference format of 72 bits.
It is converted on input (by convert_date_to_binary_) and displayed
on output in the user's choice of time zones and languages.
This happens automatically in all programs, since each loged in
user has a characteristic "time zone" and "language" set by their
systems administrator and changeable by the user on a per-login basis.
It's suprising what one can do when one haws ONE, dynmically-linked
(so never have multiple versions in 100 different applictions) reusable
routine. And the variety of input forms and languages has grown over
the years, as have the variety of output forms and languages.
I don't know if you can get the PL/I source, but you might ask
a friendly Multician.
...Paul
--
Paul Stachour SCTC, 1210 W. County Rd E, Suite 100
stachour@sctc.com Arden Hills, MN 55112
[1]-(612) 482-7467jas@ingres.com (Jim Shankland) (04/07/91)
Think of time zone information as a display format for an absolute time, somewhat analogous to a floating point display format. The database itself must store absolute time (in practice, Greenwich Mean Time), while the client chooses a display format (i.e., timezone) in which to display that time, probably on a per-session basis. (Unless you really believe that noon in San Francisco comes before 1 p.m. in New York -- which would be a curious model of time.) Note that daylight savings time enormously complicates things. If I want times displayed in San Francisco local time, it is insufficient to know that San Francisco is 8 time zones west of Greenwich. I must also apply a 1-hour daylight savings time correction *for time values representing times at which daylight savings time was in effect*. It is insufficient to be able to correctly display the current time! Just because daylight savings time is currently in effect does not mean I should apply the DST correction to a value retrieved from the database representing some time during last December. Worse, I need to take the legislative history of the region into account, so that, e.g., I can know to apply DST correction to a time in February 1974, when DST was in effect year-round because of the energy crisis. I need to distinguish between Arizona time, which is in the Mountain time zone, but does not "do" DST, and Colorado time, which is in the Mountain time zone, but *does* have DST. Accurately representing future times requires complete knowledge of any DST laws that might be passed in the future. As far as I know, no currently available software does this correctly. Arthur David Olson of NIH (I think) has written a timezone library in C that does as much of this stuff as possible. It includes tables encoding the whims of dozens of state and national legislatures. You should be able to pick this up from your comp.sources.unix archives, or FTP it from somewhere. Ask around. jas
jean@beno.CSS.GOV (Jean Anderson) (04/07/91)
In article <1991Apr5.103756.18982@news.cs.indiana.edu> vogt@iuvax.cs.indiana.edu (Michael Vogt) writes: > >What date/time format do people use when dealing with data >that crosses date and time zones? How are time changes like daylight >savings time and standard time dealt with? > We work with world wide seismic data. We store all times in GMT "epoch" time--the number of seconds since midnight January 1, 1970 (I suppose immediately tagging this as the UNIX-based system it is). By default we also display GMT rather than convert to localtime. That avoids having to know time change schedules and hour offset. For example, the USA goes on Daylight Savings this Sunday, but Norway already switched last weekend. "Let's see here....this data came from Norway so subtract 9 hours to get my PST. Oops--today subtract 10 because they already switched to Daylight Savings and we haven't yet." Three months from now it would be even worse for somebody looking at old data. Who ever remembers the exact day that each country switches from Standard to Daylight? Such confusion could result in significant errors. Epoch time is stored as a double precision float in the database, but we have library routines that convert it to a variety of human readable date/time formats. And for interactive ORACLE users, we have SQL*Plus defines specified up in the global startup file (glogin.sql) to likewise convert to human readable date/times. It is much easier to deal in GMT. +----------------------------------------------------------------------------+ | Jean Anderson, DBA email: jean@seismo.css.gov | | SAIC Geophysics Division, Mailstop 12 or: jean@esosun.css.gov | | 10210 Campus Point Drive phone: (619)458-2727 | | San Diego, CA 92121 fax: (619)458-4993 | +----------------------------------------------------------------------------+ | Any opinions are mine, not my employer's. | +----------------------------------------------------------------------------+
kathy@cfctech.cfc.com (Kathy Nash) (04/08/91)
We store the time in gmt and convert to local time by exporting the TZ variable before doing the date conversion. This eliminates the need to know when daylight savings occcurred, etc.
mark@drd.com (Mark Lawrence) (04/09/91)
In article <1991Apr6.182727.16147@ingres.Ingres.COM> jas@Ingres.COM (Jim Shankland) writes: >Think of time zone information as a display format for an absolute >time, somewhat analogous to a floating point display format. The >database itself must store absolute time (in practice, Greenwich Mean >Time), while the client chooses a display format (i.e., timezone) >in which to display that time, probably on a per-session basis. >(Unless you really believe that noon in San Francisco comes before >1 p.m. in New York -- which would be a curious model of time.) Excellent suggestion. >Arthur David Olson of NIH (I think) has written a timezone library in >C that does as much of this stuff as possible. It includes tables >encoding the whims of dozens of state and national legislatures. >You should be able to pick this up from your comp.sources.unix >archives, or FTP it from somewhere. Ask around. I'm using it now (in fact) to retrofit better handling of time/date into our application. Mike Wescott at NCR (thanks Mike!) gave me the pointer I needed to find it. He wrote: > Look at getdate(3) which comes as a part of the News sources. It does > the picking and the conversion to standard unix format. > > Also look at the routines that came as part of the v18i111-7 posting in > comp.sources.unix: "Table-driven ctime/time/localtime/date package" > for ideas. There is a struct tm to time_t conversion routine in there, > using a binary search to do the conversion. found mine at uunet v18 archives. You can find yours at finer anon ftp sites everywhere. -- mark@drd.com mark@jnoc.go.jp