> However the code given shows that jaybirds internal encoding and decoding
> functions are not even bijective! Neither are they inverted functions of
> each other.

Not yet convinced:

Calendar localCalendar = new GregorianCalendar();
localCalendar.set(Calendar.YEAR, 2009);
localCalendar.set(Calendar.MONTH, 9);
localCalendar.set(Calendar.DATE, 25);
localCalendar.set(Calendar.HOUR_OF_DAY, 2);
localCalendar.set(Calendar.MINUTE, 30);
localCalendar.set(Calendar.SECOND, 0);

java.util.Date timeLocal = localCalendar.getTime();

java.sql.Timestamp tsLocal = new java.sql.Timestamp(timeLocal.getTime());
java.sql.Timestamp possibleFailureLocal =
test.decodeTimestamp(test.encodeTimestamp(tsLocal));

System.out.println("Date " + timeLocal.toString() + " : " +
timeLocal.getTime() + " == " + possibleFailureLocal.getTime());

Calendar localDstCalendar = new
GregorianCalendar(TimeZone.getTimeZone("CEST"));
localDstCalendar.set(Calendar.YEAR, 2009);
localDstCalendar.set(Calendar.MONTH, 9);
localDstCalendar.set(Calendar.DATE, 25);
localDstCalendar.set(Calendar.HOUR_OF_DAY, 2);
localDstCalendar.set(Calendar.MINUTE, 30);
localDstCalendar.set(Calendar.SECOND, 0);

java.util.Date timeDstLocal = localCalendar.getTime();

java.sql.Timestamp tsDstLocal = new
java.sql.Timestamp(timeDstLocal.getTime());
java.sql.Timestamp possibleFailureDstLocal =
test.decodeTimestamp(test.encodeTimestamp(tsDstLocal));

System.out.println("Date " + timeDstLocal.toString() + " : " +
timeDstLocal.getTime() + " == " + possibleFailureDstLocal.getTime());

Calendar utcCalendar = new GregorianCalendar(TimeZone.getTimeZone("UTC"));

java.sql.Timestamp possibleFailureLocalCorrected =
test.decodeTimestamp(
test.decodeTimestamp(
test.encodeTimestamp(
test.encodeTimestamp(tsLocal, utcCalendar))), utcCalendar);

System.out.println("Date " + timeLocal.toString() + " : " +
timeLocal.getTime() + " == " + possibleFailureLocalCorrected.getTime());

Output is:

Date Sun Oct 25 02:30:00 CET 2009 : 1256434200562 == 1256434200562
Date Sun Oct 25 03:30:00 CET 2009 : 1256437800572 == 1256437800572
Date Sun Oct 25 02:30:00 CET 2009 : 1256434200562 == 1256437800562

>> And it is also not correct to say that Jaybird is playing tricks here -
> that is Java, that does this conversion trick.
>> See the following code...
>> You will see the same formatted date (it uses default calendar, which now
> is CET), but different number of milliseconds since "day zero".
>
> No.
> On my computer this code prints:
>
> Time Sun Oct 25 02:30:00 CET 2009 is 1256434200051
> Time Sun Oct 25 03:30:00 CET 2009 is 1256437800176
>
> Which seems quite correct. Note the 02 vs 03 in the middle.

Right, my fault. But that only means that Java handles the time as it
should.

> I thought the specification would be, that the second encoded integer is the
> number of seconds into the day...?
> So this could be a good start.

Yes, but we get problem with DST. Firebird knows nothing about DST, so
if I would count number of seconds since day start, timestamps stored in
Firebird would be two days a year for 21 hour incorrect and very likely
will cause an error for the last hour - the number of seconds since day
start would tell that we have a 25th hour, but that does not fit the
domain definition of the TS datatype.

> I know, that this interpretation is also far from perfect. However it does
> allow to store and read information exactly. As long as the timezone is not
> changed inbetween, it would ensure that every timestamp written can be read
> agan, which is not the case right now.

Above is my test case, which works fine.

> Maybe there could be a global parameter to tell jaybird to use a utc in any
> place?

Do not know yet. Issue is that it seems that in this case one would need
to use completely different routines to encode/decode timestamps and I
do not want to have such code in the driver - it is hard to maintain.

Roman