Subject Re: [firebird-support] Timezone again
Author Helen Borrie
At 11:18 AM 5/11/2009, you wrote:
>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?

No. The right-hand longword is the proportion of one day elapsed since midnight of the day specified by the left-hand longword.

>2) If there is no timezone definition, the allowed range for the second
>value varies between 23h and 25h?

Not applicable. There is no variation.

>3) Is firebird in any place parsing this information? Or formatting it as
>string? Where?

In the API, with isc_encode_date and isc_decode_date functions. Since you're using Jaybird, this will be implemented there natively. For calculation purposes, the pair of accreted longwords is treated as NUMERIC(18,9) for Dialect 3 and DOUBLE PRECISION for Dialect 1.

>4) Which timezone is used in 3) ?

From Firebird's P.O.V., none. I think Roman already informed you about a default timezone that is assumed by Java.

>5) Can the timezone be fixed (per database?, per server?) ?

Internally, no. It wouldn't know what it has to fix. It just gets an encoded number and stores it.

>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.

If you take out the timezone codes, they are the same to Firebird. You need something external to ensure that the number stored is the number you want. For the server and the clients, you would need to employ server time synchronization. If you want some mechanism to standardize time to e.g. UTC then you can combine your forces: have a reference table (or a UDF) to put the timestamp into the required timezone context. Triggers would be the way to get the value stored the way you want it.

>This happenes that the java code simply takes the numbers from above and
>multiplies them by the respective factors and adds them.

That's crystal-clear, then. ;-)