Subject PreparedStatement.setTimestamp(int, Timestamp, Calendar) issue - comments needed
Author Roman Rokytskyy
Hi All,

Probably you have read the discussion about the meaning of the above
mentioned method between me and Ron. We have continued the discussion
off-list and here's the results:

We have found out that there is no agreement among driver developers how to
interpret the phrase "With a Calendar object, the driver can calculate the
timestamp taking into account a custom timezone." from the JDBC

For example Oracle treats it as "write data into the database converting the
specified timestamp into the specified timezone" (e.g. calendar specifies
target time zone). In turn MySQL developers interpret this as "the timestamp
is specified in the time zone of the passed calendar, write data into the
database using local time zone".


we have following timestamp "31-07-2004 19:48:54 CEST" (CEST is GMT+01).

PreparedStatement ps = ....
Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
Timestamp ts = new Timestamp(new Date().getTime());
ps.setTimestamp(1, ts, cal);

In Oracle the stored timestamp would be "31-07-2004 18:48:54", in MySQL that
would be "31-07-2004 20:48:54".

So far JayBird uses MySQL interpretation of the specification. However Ron
managed to convince me that it is wrong. Reason is that timestamp object
does not know anything about time zones - time is stored as milliseconds
after 1.1.1970 0:00:00.000 UTC. This is general contract in Java. Therefore
MySQL interpretation can be translated as "number of milliseconds in UTC in
CEST". This seems to be nonsense.

Therefore I would like to change the behavior of the driver to follow the
common sense (and Oracle) interpretation. This might affect existing
applications. I will introduce a new connection parameter
"invert_time_zone", when it is specified, driver will use MySQL

Comments? Suggestions?