Roman,

First, thanks for working on this and being so responsive!

I will attempt to take this problem one step at a time because time
zones are quite complicated (even more so when trying to discuss
over email!). If I have failed to convince you and the group that
this seems like a problem then I can either (a) store the date/times
as doubles and do the agnostic conversion myself or (b) make our own
version of the JayBird driver. I'd rather do neither of these and
try to realize why this request for change is either correct or
incorrect – please read my points below and let me know your
thoughts (if you want to talk in person I would be glad to jump on
the phone – could be easier!).


Test Case
----------------------
Was the test case you had in your last response run using the code
that still included the local time zone in the calculation? If so I
still believe this is part of the problem since it looks like – at a
minimum – the sign was reversed for the calculation in the last code
release (aside from the daylight savings issue you brought up – see
below for more on that). Thinking in terms of just the algorithm
(not the code):

Local Machine Time Zone=EST

Hours From The Epoch (GMT/UTC)=1000
(not realistic, but lets use 1000 for ease of algorithm proofs)

EST=GMT-5 (offset of -5 hours)
GMT=GMT-0 (no offset)
PST=GMT-8 (offset of -8 hours)

Case 1 - without using machine's time zone in offset calculation
(time + tz.offset) – using GMT as the "base":
using GMT: 1000+(0) =1000
using EST: 1000+(-5)=995
using PST: 1000+(-8)=992

Case 2 - using machine's time zone in offset calculation (time +
(machine.tz.offset – tz.offfset)
using GMT: 1000+(0-(-5))=1005
using EST: 1000+((-5)-(-5))=1000
using PST: 1000+((-8)-(-5))=997
<change mahine's time zone to PST>
using GMT: 1000+(0-(-8))=1008
using EST: 1000+((-5)-(-8))=1003
using PST: 1000+((-8)-(-8))=1000

In Case 1 the results are consistently offset by the timezone offset
value.

In Case 2 the offset is centric to the local timezone offset not the
requested timezone offset; but using "+" does get the value moving
in the proper direction (the JayBird driver currently uses "-" which
threw it off double in the wrong direction) – however the saved data
is then local machine specific instead of agnostic. The main issue
with Case 2 is that if the machine's time zone changes then there is
inconsistency with the date/time values in the database! All of the
old date/times will come back with incorrect values because the
offset will be incorrect for that data. Or, consider this scenario:
I use Firebird on a laptop with an application internationally, I
travel from country to country changing the time zone in each
location; I record data in the database based on time; I get back to
my company headquarters which is in EST and synchronize my data
using a straight SQL data dump – all date/times I recorded are
inconsistent and are even inconsistent with the headquarters
database! This is a fundamental data integrity issue – but at a
minimum the sign should be reversed in the current code base to fix
the "direction" of the resulting value.


Day Light Savings
--------------------------
Your concern about day light savings is valid – and to correct this
in the calculation using only the passed in calendar object
the "getOffset" method could be used instead of the "getRawOffset"
method. The time zone can build in the day light savings rules when
built from the SimpleTimeZone object so based on when in time the
offset is used it will take into account the daylight savings offset
(e.g. the Java standard time zone object of "EDT" vs "EST"). Using
the local machines timezone does not address the day light savings
problem because that would only be taking into account when that
local machine's timezone is in daylight savings (?). The only way
to account for this is to use the passed time zone's definition of
its offset if it is defined. So – the calculation is something like
(off the cuff and untested here):

long time = value.getTime() + cal.getTimeZone().getOffset
(value.getTime());

I am not sure I followed your last comment about having to use the
local machine's time zone because of a deeper conversion using GMT?
What does that do and why – it seems that once the calculation above
is done then there is no more need for more calculations? Where is
that code – maybe this would explain more to me why you need to use
local time zone in the calculation.


Custom Time Zone
----------------------------
"With a a Calendar object, the driver can calculate the timestamp
taking into account a custom timezone.". A custom timezone is
nothing more than a developer creating a Timezone using any GMT
offset (maybe + 10 hours 15 minutes) and possibly day light savings
information built in. So this is automatic when using the offset
value of the timezone object. This requirement seems to be covered.


No Calendar Passed
--------------------------------
If no Calendar object is passed then the driver should use the
machines default/local time zone in the calculation.


Oracle JDBC
-------------------
I have used Oracle extensively in this exact use case of time zone
conversions. My recollection with Oracle was that if I used:
setTimestamp(1,ts) it would use the local time zone to write to the
db, but if I used: setTimestamp(1,ts,Calendar.getInstance
(TimeZone.getTimeZone("UTC"))); it would use UTC conversion and
write that converted date/time value to the db in UTC. This was a
few months ago using the Oracle JDBC thin driver (I believe); I will
try to contact a colleague who is an Oracle expert and work with him
so we can get some exact answers as to Oracles behavior under the
same circumstances. I once found a bug in Oracle's released JDBC
driver for caching prepared statements when a certain column type
was used (that was a needle in a haystack!) – so maybe they aren't
perfect!?


Thanks again!

Ron



--- In Firebird-Java@yahoogroups.com, "Roman Rokytskyy"
<rrokytskyy@a...> wrote:

> > Great! Be sure to change both the encodeTimestamp() and
> > decodeTimestamp() methods appropriately - both are using the

local

> > machine's time zone inappropriately (they should not use it at

all).

>
> I did the following test case:
>
> Connection connection = getConnectionViaDriverManager();
> try {
> PreparedStatement stmt = connection.prepareStatement(
> "INSERT INTO test_blob(id, ts_field) VALUES

(?, ?)");

>
> try {
> Calendar calendar = Calendar.getInstance();
>
> Timestamp ts = new Timestamp

(calendar.getTimeInMillis());

>
> stmt.setInt(1, 1);
> stmt.setTimestamp(2, ts);
>
> stmt.execute();
>
> stmt.setInt(1, 2);
> stmt.setTimestamp(2, ts, calendar);
>
> stmt.execute();
>
> stmt.setInt(1, 3);
> stmt.setTimestamp(2, ts,
> Calendar.getInstance(TimeZone.getTimeZone("UTC")));
>
> stmt.execute();
>
>
> Statement selectStmt = connection.createStatement

();

> try {
> ResultSet rs = selectStmt.executeQuery(
> "SELECT id, CAST(ts_field AS VARCHAR(35)),

ts_field

> FROM test_blob");
>
> while(rs.next()) {
> System.out.println("ID " + rs.getInt(1) +
> ", time_str '" + rs.getString(2) +
> "', time ts " + rs.getTimestamp(3) +
> ", time ts_cal " + rs.getTimestamp(3,
> Calendar.getInstance()));
> }
> } finally {
> selectStmt.close();
> }
>
> } finally {
> stmt.close();
> }
>
> } finally {
> connection.close();
> }
>
> Idea is to write a timestamp into the database, first time without

the

> calendar and next time with the same calendar that generated the

timestamp.

> Then I read the data, not as timestamps, but I let server convert

them.

>
> ID 1, time_str '2004-07-29 23:31:19.4410', time ts 2004-07-29

23:31:19.441,

> time ts_cal 2004-07-29 23:31:19.441
>
> ID 2, time_str '2004-07-29 23:31:19.4410', time ts 2004-07-29

23:31:19.441,

> time ts_cal 2004-07-29 23:31:19.441
>
> ID 2, time_str '2004-07-30 00:31:19.4410', time ts 2004-07-30

00:31:19.441,

> time ts_cal 2004-07-30 00:31:19.441
>
> So, the most strange is the last result. I must admit that I do not
> understand " With a a Calendar object, the driver can calculate the
> timestamp taking into account a custom timezone. ".
>
> If driver in the third case should have written '2004-07-29

22:31:19.4410'

> (well, that's also the question, what do we do with the daylight

saving

> time?), then there's a mistake in driver about the sign. Should be
>
> long time = value.getTime() +
> (cal.getTimeZone().getRawOffset() -
> Calendar.getInstance().getTimeZone().getRawOffset());
>
> and
>
> long time = value.getTime() -
> (cal.getTimeZone().getRawOffset() -
> Calendar.getInstance().getTimeZone().getRawOffset());
>
> I just checked the Oracle driver - it simply ignores the calendar,

data are

> written into the database using the local time zone in all cases.
>
> So, unless somebody points me to an explanation of how should I

understand

> those words from the specification, I will make no changes into

the driver

> at this point.
>
> Ron, if this behavior of the driver is unacceptable for you,

please either

> convince me that there's a mistake in a sign, or you have to

create a custom

> version of the driver (it is relatively easy to buid).
>
> BTW, we do have to take the local time zone into account, so your

suggested

> patch is not correct - it produces different results when used

with and

> without calendar. This happens due to the fact that there is one

more local

> time zone conversion happening, however I cannot use UTC there,

because

> there is no possibility to obtain a GMT time zone with activated

daylight

> saving time.
>
> Roman