> Firebird itself stores timestamps as kind of floating point value (more
> exactly 8 bytes), where the integer part (or the first 4 bytes) describe the
> day and the fraction part (or the last 4 bytes) describe the number of
> 1/10000 seconds into that day.

Right.

> If this definition holds, then I suspect jaybirds interpretation is broken.
>
> Example 1:
> 2009.10.25 02:59:59 MESZ = 2009.10.25 00:59:59 UTC
> Java: 1256432399000 = Encoded: 55129,107990000
> But one second LATER:
> 2009.10.25 02:00:00 MEZ = 2009.10.25 01:00:00 UTC
> Java: 1256432400000 = Encoded: 55129,72000000
>
> Example 2:
> 2009.03.29 01:59:59 MEZ = 2009.03.29 00:59:59 UTC
> Java: 1238288399000 = Encoded to 54919,71990000
> But one second LATER:
> 2009.03.29 03:00:00 MESZ = 2009.03.29 01:00:00 UTC
> Java: 1238288400000 = Encoded: 54919,108000000
>
>
> By definition I would expect the second fraction in increase by 10000 in
> both examples.
> This happens because jaybird ignores DTS in Germany. My local timezone is
> CET = Europe/Berlin.

It is not Jaybird, but Java. Jaybird uses calendar to extract components
of the date. In your case Java returns 2009, 10, 25, 2, 59, 59 for the
first timestamp and 2009, 10, 25, 2, 0, 0 for the second. Jaybird simply
encodes that time.

> This means that jaybird can never encode certain values:
> 54919,72000000 to 54919,108000000

Correct. That time does not exist in your locale. I suspect that if your
locale were UTC, Java would return you different components and you
would get the results you want. But I did not try it.

> And there are multiple interpretations to other values:
> 55129,72000000 to 55129,107990000

Again, it's up to Java - driver will just extract components and set
using the system's default calendar.

> ONE simple fix would be to encode the date using a calendar, then use
> getTime(), add 1/10 of the time fraction and then use setTime().
> This would fix the interpretation of the second part of timestamp byte array
> pair.
>
> (Note that then the range of valid values would depend on the date in
> timezones with DTS. Most days would have 0-863.999.999, while one day would
> be 0-827.999.999 and one day would be 0-899.999.999.)
>
>
> The current implementation is really BROKEN because it doesn't even allow to
> get the same values from the database that were stored earlier:
>
> XSQLVAR v = new XSQLVAR();
> Date original = parser.parse( "2009.10.25 02:30:00 MESZ" );
> Date failure = v.decodeTimestamp( v.encodeTimestamp( new
> java.sql.Timestamp( original.getTime() ) ) );
> System.out.println( berlin.format( original ) + " -> " +
> berlin.format( failure ) );
> System.out.println( utc.format( original ) + " -> " + utc.format(
> failure ) );
> System.out.println( original.getTime() == failure.getTime() );
>
> Prints:
>
> 2009.10.25 02:30:00 MESZ -> 2009.10.25 02:30:00 MEZ
> 2009.10.25 00:30:00 UTC -> 2009.10.25 01:30:00 UTC
> false
>
>
> From my POV this "false" is clearly a hard bug: The database driver discards
> information.
> Note that this bug exists involving A SINGLE TIMEZONE. The code above uses 3
> distinct formatters just to show the problem more clearly. Just note that
> you can leave out the first two println's.
>
>
> Any comments?

The test case is not quite correct - it does not involve database which
also interprets timestamp according to the system locale. Try to
construct a case that involves database-side conversion of the timestamp
to string and then client side conversion from string back, you would
have a lot of fun. Depending whether you execute a test case in CET or
in CEST, it will either succeed or fail (tip - normally Java uses
default time zone).

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

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

System.out.println("Time " + localCalendar.getTime().toString() + " is "
+ localCalendar.getTime().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);

System.out.println("Time " + localDstCalendar.getTime().toString() + "
is " + localDstCalendar.getTime().getTime());

You will see the same formatted date (it uses default calendar, which
now is CET), but different number of milliseconds since "day zero".

Topic is complex and I suspect that we will not reach any agreement here
- there's too much room for interpretations and I know no specification
that would tell us how to use it. We had already a long thread about DST
few years ago, where we discussed how to interpret the offset in the
specified calendar (add an offset or subtract the offset, one database
used addition, another - subtraction). At the end we got another
connection parameter...

One of the solutions would be to use always the UTC calendar to extract
the date components, but it would also require use of UTC on the server.
That would work in your environment, but not in others...

Roman