Re: Preparing for daylight saving time changes in 2007


"Sarah Bram" <sarahbram@xxxxxxxxxxx> wrote

[Regarding Preparing for daylight saving time changes]
http://support.microsoft.com/gp/dst_topissues#A2
... I do save each appointment's duration with start
and end dates. ...

Sarah,

What about any times that you are storing in your
tables?

In the b.p.database.general forum, I was recently
arguing for the need for database tables to support
storing GMT or Universal Time *plus* the local
offset, when Thorsten Engler pointed out that many
database managers and tables already do support
exactly that. It's in a SQL standard and its called
"TIMESTAMP WITH TIMEZONE". I had no idea that
this existed. A Google turned up this
http://www.lc.leidenuniv.nl/awcourse/oracle/appdev.920/a97269/pc_04dat.htm#27328
in which I found this description (which I copy,
because I suspect that most readers are not familiar
with it):
"TIMESTAMP WITH TIME ZONE (TSTZ) is a variant
of TIMESTAMP that includes an explicit time
zone displacement in its value. The time
zone displacement is the difference (in hours
and minutes) between local time and UTC
(Coordinated Universal Time--formerly
Greenwich Mean Time). The [TSTZ] datatype has
the form:
TIMESTAMP(fract_secs_prec) WITH TIME ZONE
where fractional_seconds_precision optionally
specifies the number of [seconds decimal
fraction digits] The default is 6."
I think that they really mean GMT or UT1 instead
of UTC, because UTC can sometime have 59 or 61
seconds per minute.

The problem, however, it that Delphi may not
allow proper connection to this type of table
field. But I have not verified this yet.

My older designs already store time as two fields
GMT time and local offset, and make use of the
OnGetText and OnSetText event handlers to correct
for the here-and-now and the then-and-there times.

HTH, JohnH

.

Relevant Pages

  • Re: Received mail timestamp is off by 7 hours
    ... It should be one hour ahead of UTC right now. ... Notice that the timestamp on your local e-mail server corresponds to ... the CMOS real-time clock should be set to ... UTC and fix your time zone to match your location. ...
    (freebsd-questions)
  • Re: Java (new Date()).getTime() - java.util.Timestamp("select timestamp from dual").getTime() ??
    ... That's current time in session time zone. ... And to get it in UTC, ... SQL> select current_timestamp, systimestamp, ... Note that sys_extract_utcreturns TIMESTAMP, ...
    (comp.databases.oracle.misc)
  • Re: handling different Timezones
    ... other than storing the time as UTC or with time zone ... > datetime fields from the subscriber's to the local database timezone to ...
    (microsoft.public.sqlserver.replication)
  • Re: Question about ISO8601 vs. strftime()
    ... online references to ISO8601 just get it wrong? ... that link only shows the form with the UTC ... but not the offset as far as I can tell. ... assumed to be in some local time zone. ...
    (comp.programming)
  • Re: Time Zones as politics
    ... be in a different time zone from his arch-rival, ... we are not permitted to criticize him in any way because after ... UTC +5 AKA Eastern Standard Time. ... Soon upon the establishment of the Greenwhich Meridian as the time ...
    (alt.horology)