Note
Since v4.0, Firebird has direct TIMEZONE support
Ann W. Harrison replies to Steffen Heil's questions:
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.
Is this definition correct?
Both you and Helen are correct. One integer contains the number of days 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.
If there is no timezone definition, the allowed range for the second value varies between 23h and 25h?
No. You're over-thinking this problem. Firebird is using a SQL data type called TIMESTAMP WITHOUT TIME ZONE. The standard says "35) If 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.
Is firebird in any place parsing this information? Or formatting it as string? Where?
Yes, but not where you think it is. There's code in cvt.cpp (I think) 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.
Which timezone is used in 3) ?
None.
Can the timezone be fixed (per database?, per server?) ?
No.
Right now I have the problem, that there are times that cannot be correctly 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.
The best answer now (a bit late for your application) is to store 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 imagine that I am the first user to have problems with this encoding, but I cannot find much on the web...
What's needed is to add two new types to Firebird - TIMESTAMP WITH 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."