Conversion of SQL datetime values to JDBC objects using Calendar - any JDBC spec lead guidance?

[Daniel John Debrunner <dj...@apache.org>]

[Derived & expanded from the discussion in DERBY-1816]

Derby has SQL datetime types DATE/TIME/TIMESTAMP. The SQL standard 
(section 4.6.2) says the fields (year, month day etc.) are "constrained 
by the natural rules for dates using the Gregorian calendar"

The JDBC datetime objects store a milli-second value relative to the 
epoch of 1970-01-01 and thus are not tied to the Gregorian calendar with 
the possible exception of the conversion to/from String.

JDBC ResultSet has getDate/getTime/getTimestamp methods that convert SQL 
date-time values to java.sql.{Date,Time,Timestamp} objects. These 
methods have two variants, ones that take a java.util.Calendar object 
and those that don't.

For the methods that take a Calendar the behaviour is somewhat 
undefined, and thinking about it leads to several questions:

1) The Javadoc has comments like:
"This method uses the given calendar to construct an appropriate 
millisecond value for the timestamp if the underlying database does not 
store timezone information."

"cal - the java.util.Calendar object to use in constructing the date"

To me this implies the actual Calendar object passed in is used and 
modified by the JDBC driver to calculate the milli-second value to 
construct the Time/Date/Timestamp object. Thus after one of these calls 
the SQL column's value is "returned" twice, once as an JDBC object 
(Time/Date/Timestamp) and once as the current contents of the passed-in 
Calendar. Is this correct or is the Calendar object expected to be 
unchanged by the getXXX() call?
[embedded uses the passed-in Calendar - (with minor bugs) ]
[client uses different Calendar objects just fetching the timezone from 
passed-in Calendar]

2) If the answer to 1) is that the actual Calendar is used, then should 
the Calendar object have clear() called before setting any of the 
fields, or should just the valid fields defined by SQL be set?
This makes a difference if the caller has set for a GregorianCalendar 
the ERA field to GregorianCalendar.BC. Clearing the fields will ignore 
the BC setting, setting only the fields defined by the datetime value 
will cause honour the BC request.
[embedded & client cal clear()]

3) What happens if the passed-in Calendar is not a GregorianCalendar?
Should the Calendar just have its fields set that correspond to the SQL 
fields set (YEAR,MONTH,DAY), so that 2007-05-10 would be the year 2007 
in the non-Gregorian calendar? Note that for some calendars the ERA is 
key, meaning the answer for 2) would have to be: do not call clear().

Or should the SQL datetime always be treated as a normalized Gregorian 
value and instead converted to milli-second value using a 
GregorianCalendar and then set the passed-in calendar to that 
milli-second value?

E.g. with a SQL value of 2007-05-10 and passing in a Jewish calendar is 
the absolute value of the date returned today (22 Iyyar 5767, 
2007-05-10AD) or sometime in the past (year 2007 in the Jewish calendar, 
somewhere ~1754BC Gregorian).

[embedded/client just uses the SQL fields to set the Calendar fields]

4) For a java.sql.Time object the 'date components must be set to the 
"zero epoch" value of January 1, 1970'. That's clear that the date 
components are based upon the Gregorian calendar. Then the issue is the 
passed-in calendar cannot be used to generate the mill-second value to 
create the java.sql.Time object because it cannot be guaranteed that 
setting the YEAR field to 1970 will correspond to the epoch. So the 
passed in Calendar object would be used for the timezone but not used to 
calculate the milli-second value. And then would the passed in Calendar 
object needs to be set to the millisecond value corresponding to the 
return java.sql.Time object?

Getting more confused the more I look at this ...
Dan.