Steffen Heil wrote:

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

But that's exactly what it does. Firebird doesn't really do anything
with timestamps. They're just two-part integer numbers that can be
compared for equality, less than, etc. In some cases it has to compare
it to strings, and when that happens it does a simple string-to-binary
or binary-to-string conversion of the actual value, no matter what
timezone the server is running in or what timezone the string was
formatted in, etc.

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

But the FB server does take the timestamp from the machine it is running
on, so assuming you don't change that machine's timezone in an ad hoc
manner, the timestamps stored in that database WILL allow correct
ordering of information. But I still think generators and sequence
numbers are better for that purpose, because it just might happen that
two events happen in such tight sequence that they get the same
timestamp. I wouldn't want to rely on that not happening, anyway.

> > > 5) Can the timezone be fixed (per database?, per server?) ?
> > No.
>
> Bad.
> 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
> enough?

It IS fixed in that all databases on the same machine will all use the
timezone configured on that machine.

What is your actual need here?

Do you need to be able to change the server machine's timezone?

Do you need to be able to compare timestamps between databases from
different machines (with potentially different timezones)?

Do you need to be able to migrate your database between machines (with
potentially different timezones)?

You could always store the server machine's timezone somewhere in a
config table and have all timestamps stored in that timezone. You can
then safely use current_timestamp. Your app layer will have to convert
between its desired timezone and the server's. Everywhere. But you could
write a udf for it.

But if you need to do anything of the above, that causes timezone
changes, then you need to do more, like e.g. storing the timezone with
each stored timestamp, always convert all timestamps to some globally
predefined timezone like utc, or update all timestamps in the database
to the new timezone when it is changed. Which approach is best only you
can know because it's your application.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64