Subject AW: [firebird-support] Timezone again
Author Steffen Heil

> 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, I am not. I was hoping that internally it is not interpreted but treated
binary, which does prevent a lot of trouble.

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

Fine, as expected. (Not that I had preferred it as more standard timestamp
such as milliseconds sice 1970 or such.)

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

This is really bad. I (and propably most of other users) thought that always
inserting information into logging tables with CURRENT_TIMESTAMP would allow
correct ordering of information. But in fact, IT DOES NOT... I consider this
a bug. (Propably less in the implementation but in the specification.)

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

However, if it cannout be done on a firebird application layer, maybe there
are tricks to make the os respond to firebird with another timezone than the
rest of the system is running in? Anyone on the list who knows linux well

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

That's what I just learned the hard way. Especially CURRENT_TIMESTAMP seems
really bad...

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

No. I would even consider storing timestamps together with timezones a
mistake. For me it smells like storing a radix together with integers. The
database should store information, not display formatting hints.

> Leap seconds are implementor defined...

Leap seconds are an really interesting point. But let's first get hours
right, then think about details... :D

Could it be an option to add an configuration parameter to firebird.conf to
force firebird to use a specific timezone for internal things like
CURRENT_TIMESTAMP and parsing and string concatenation?

Best Regards,

[Non-text portions of this message have been removed]