TimeZone conversion in Connector/J; maybe a patch is in order?

[David Tonhofer]

Hi, I'm in a bit of a stress situation, so I will keep this short, just to check whether it
makes sense. I have been looking through the Connector/J code for 3.0.15-ga, maybe
this already exists in later versions?

Basically, it's about conversion of DATE TIMESTAMP or DATETIME to Timestamp.

Current code looks a bit ugly (sorry, guys, no offense intended) and there is something missing:
a method to set up *your own* Calendar for conversion.

Current code in ResultSet does the following, more or less:

1) Sucks bytes from the server over the wire, this is the value as seen in a online Select,
e.g. YYYY-MM-DD HH:MM:SS.

2) If you run getAsString() you get the "YYYY-MM-DD HH:MM:SS" String and can convert at
will. This is what I have been doing myself so far, but now I'm using Jakarta BeanUtils,
which call getTimestamp(). So:

3) If getTimestamp() is called() then eventually fastTimestampCreate() is called with a
null conversion Calendar. This means fastTimestampCreate() will interprete the
YYYY-MM-DD HH:MM:SS as expressing time in the SQL client's timezone, with a Gregorian
Calendar. Therein lies a problem if you have many SQL clients in different timezones.

4) The Timestamp result returned by fastTimestampCreate() is passed through
TimeUtil.changeTimezone() wich, if Connection.useTimezone() is true, modifies the Timestamp
so that, in the end, the YYYY-MM-DD HH:MM:SS is interpreted as expressing time in the SQL
server's timezone, with a Gregorian Calendar.

In order to make TimeUtil.changeTimezone() obsolete and add flexibility, one should probably
do this:

Configure the Connector with a value 'timestampTimezone' that can take values: 'server',
'client' or an offset, like 'UTC+1'. Maybe an additional 'timestampCalendar', too.

Initialize a new variable 'Calendar conversionCalendar' in the Connector so that:

--> case of 'server' (also, if 'useTimezone' has been set)
conversionCalendar = new GregorianCalendar()
conversionCalendar.setTimeZone(getServerTimezone())

--> case of 'client' (also, the default)
conversionCalendar = new GregorianCalendar()
conversionCalendar.setTimeZone(TimeZone.getDefaultTimeZone())

--> case of '<other value>'
conversionCalendar = new GregorianCalendar()
conversionCalendar.setTimeZone(TimeZone.getTimeZone(<other value>))

Now the calls in ResultSet can be simplified and the semantics are clearer:

Instead of the nested call:

return TimeUtil.changeTimezone(this.connection,
fastTimestampCreate(null, year, month - 1, day,
hour, minutes, seconds, 0),
connection.getServerTimezone(), tz);

Just use:

return fastTimestampCreate(connection.getConversionCalendar(), year, month - 1, day,
hour, minutes, seconds, 0))


Ok, so that's about it....Flame away!

-- David