| Subject | Re: [firebird-support] Timezone again |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2009-11-05T17:38:30Z |
Steffen,
before or after Nov 17, 1858. The other describes the time during that
date as a fraction of a day in 1/10000 second units (clunks). Yes,
there is more precision in 32 bits than there are clunks in a day.
data type called TIMESTAMP WITHOUT TIME ZONE. The standard says
"35) If <with or without time zone> is not specified, then WITHOUT
TIME ZONE is implicit.". Which is good, because time zones were
added in SQL-99, so those of us who had timestamps before then were
all without timezones.
You're assuming that Firebird runs internally on GMT and does
mystic transformations between what it "knows" is the real time
and whatever happens to show up on the system clock. No. If
you insert a string like "2009.10.25 02:30:00", it stores the
number of days between 1858.11.17 and 2009.10.25 in one integer
and two hours and thirty minutes expressed in clunks in the
other.
Firebird does know that some times are illegal - anything on
February 29 in non-leap years, and some days when the west
moved from the Julian to the Gregorian calendar, but it has
no notion that one hour in March or April doesn't exist and
some hour in October or November is duplicated.
If you store CURRENT_TIMESTAMP, CURRENT_TIME or 'now', firebird
asks the system for the current local time, without a care about
whether that minute might be duplicated or skipped.
that converts strings to dates. It's used inside the engine only to
compare stored dates to literal strings. Internally, a date is just
a two-part number.
timestamps in UTC. You don't have to run your servers in UTC if you
code your applications so they always convert from local to UTC
before storing data and never use CURRENT_TIMESTAMP, CURRENT_TIME,
or 'now'. There's probably a UDF somewhere that converts between
UTC and local time.
TIMEZONE and TIME WITH TIMEZONE. The timezone has to be stored with
each value, since, as you've demonstrated, most of us live in two
different timezones, which vary with the season.
The standard appears not to say thing t about non-existent
timestamps like 2010.01.01 00.00.01 MESZ.
Leap seconds are implementor defined. "Whether an SQL-implementation
supports leap seconds, and the consequences of such support for date
and interval arithmetic, is implementation-defined." But "The range
for seconds is 6l.9 00 to 61.9(N) where “9(N)” indicates a sequence
of N instances of the digit “9” and “N” indicates the number of digits
specified by <time fractional seconds precision>." "The range for
SECOND allows for as many as two “leap seconds”. Interval arithmetic
that involves leap seconds or discontinuities in calendars will produce
implementation-defined results."
Another interesting note:
"NOTE 106 — Within the non-null values of a <datetime type>, the
value of the time zone interval is in the range –14:00 to +14:00.
The range for time zone intervals is larger than many readers
might expect because it is governed by political decisions in
governmental bodies rather than by any natural law."
Good luck,
Ann
>Both you and Helen are correct. One integer contains the number of days
>
> After some discussion on the firebird-java list, I do understand a little
> more about firebirds time formats, but a few questions are still left:
>
> The wire protocol seems to send timestamps as a pair of integers encoded
> into 8 bytes, one integer giving the number of days since some day, and one
> integer giving the number of 1/10000 seconds since daystart.
>
> 1) Is this definition correct?
before or after Nov 17, 1858. The other describes the time during that
date as a fraction of a day in 1/10000 second units (clunks). Yes,
there is more precision in 32 bits than there are clunks in a day.
>No. You're over-thinking this problem. Firebird is using a SQL
> 2) If there is no timezone definition, the allowed range for the second
> value varies between 23h and 25h?
data type called TIMESTAMP WITHOUT TIME ZONE. The standard says
"35) If <with or without time zone> is not specified, then WITHOUT
TIME ZONE is implicit.". Which is good, because time zones were
added in SQL-99, so those of us who had timestamps before then were
all without timezones.
You're assuming that Firebird runs internally on GMT and does
mystic transformations between what it "knows" is the real time
and whatever happens to show up on the system clock. No. If
you insert a string like "2009.10.25 02:30:00", it stores the
number of days between 1858.11.17 and 2009.10.25 in one integer
and two hours and thirty minutes expressed in clunks in the
other.
Firebird does know that some times are illegal - anything on
February 29 in non-leap years, and some days when the west
moved from the Julian to the Gregorian calendar, but it has
no notion that one hour in March or April doesn't exist and
some hour in October or November is duplicated.
If you store CURRENT_TIMESTAMP, CURRENT_TIME or 'now', firebird
asks the system for the current local time, without a care about
whether that minute might be duplicated or skipped.
>Yes, but not where you think it is. There's code in cvt.cpp (I think)
> 3) Is firebird in any place parsing this information? Or formatting it as
> string? Where?
that converts strings to dates. It's used inside the engine only to
compare stored dates to literal strings. Internally, a date is just
a two-part number.
>None.
> 4) Which timezone is used in 3) ?
>No.
> 5) Can the timezone be fixed (per database?, per server?) ?
> Right now I have the problem, that there are times that cannot be correctlyThe best answer now (a bit late for your application) is to store
> encoded in firebird:
> 2009.10.25 02:30:00 MESZ -> 2009.10.25 02:30:00 MEZ for example, they have
> the same encoding, but are one hour different.
timestamps in UTC. You don't have to run your servers in UTC if you
code your applications so they always convert from local to UTC
before storing data and never use CURRENT_TIMESTAMP, CURRENT_TIME,
or 'now'. There's probably a UDF somewhere that converts between
UTC and local time.
> I cannot imaging, I am the first user to have problems with this encoding,What's needed is to add two new types to Firebird - TIMESTAMP WITH
> but I cannot find much on the web...
>
TIMEZONE and TIME WITH TIMEZONE. The timezone has to be stored with
each value, since, as you've demonstrated, most of us live in two
different timezones, which vary with the season.
The standard appears not to say thing t about non-existent
timestamps like 2010.01.01 00.00.01 MESZ.
Leap seconds are implementor defined. "Whether an SQL-implementation
supports leap seconds, and the consequences of such support for date
and interval arithmetic, is implementation-defined." But "The range
for seconds is 6l.9 00 to 61.9(N) where “9(N)” indicates a sequence
of N instances of the digit “9” and “N” indicates the number of digits
specified by <time fractional seconds precision>." "The range for
SECOND allows for as many as two “leap seconds”. Interval arithmetic
that involves leap seconds or discontinuities in calendars will produce
implementation-defined results."
Another interesting note:
"NOTE 106 — Within the non-null values of a <datetime type>, the
value of the time zone interval is in the range –14:00 to +14:00.
The range for time zone intervals is larger than many readers
might expect because it is governed by political decisions in
governmental bodies rather than by any natural law."
Good luck,
Ann