Re: Timestamp problem

Peter Eisentraut wrote:
> Oliver Jowett wrote:
>> Peter Eisentraut wrote:
>>> Note,
>>> however, that this application does not use time zones or time-zone aware
>>> data types at all. It merely wishes to store '2007-03-25 02:30:00' and
>>> retrieve it in identical form.
>> getTimestamp() must convert the retrieved timestamp to *some* timezone
>> since Timestamp is only meaningful in a particular timezone. If you
>> don't pass an explicit Calendar, it uses the default JVM timezone.
>
> Why not use UTC instead of the default JVM time zone?

This whole area is poorly defined (again.. sigh). We use the default JVM
timezone because it seems a sensible default more than anything .. the
spec is silent here. Basically we just treat getTimestamp(n) as
equivalent to getTimestamp(n, new GregorianCalendar())

Having ResultSet.getTimestamp() use UTC would be somewhat surprising
behaviour, I think - in general other Java APIs assume the default JVM
timezone when not explicitly given a timezone.

As far as I can see the only way of getting predictable, portable
behaviour when using without-timezone types is to always pass a Calendar.

> Or if that is not
> appropriate for the timestamp *with* time zone type, shouldn't you be able to
> tell from the RowDescription message of the server's query response whether
> it is supposed to be timestamp with or without time zone?

The with time zone case shouldn't be affected anyway, if a timezone
offset is present in the value we use it and ignore any provided
Calendar entirely.

> I have found it tricky to predictably control "the JVM time zone". Some of
> these things are run from cron jobs, for example. And I think that different
> types of JVMs have different methods to set time zones, too. I have seen
> some weird behavior in the tests leading up to this.

Does java.util.TimeZone.setDefault() not do the trick here?

> The problem is that it's not always my code or the client's code that is
> acting here. There are Java tools many layers above this that appear to
> think that if it's a timestamp column, it should use getTimestamp(). They
> are not wrong, I believe.

Well.. JDBC just doesn't define what happens when you use getTimestamp()
with no Calendar on a timezone-less column as far as I can tell, so YMMV
if you have code that relies on a particular behaviour.

compare javadoc:

> getTimestamp
>
> public Timestamp getTimestamp(int columnIndex)
> throws SQLException
>
> Retrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp object in the Java programming language.

> getTimestamp
>
> public Timestamp getTimestamp(int columnIndex,
> Calendar cal)
> throws SQLException
>
> Retrieves the value of the designated column in the current row of this ResultSet object as a java.sql.Timestamp object in the Java programming language. This method uses the given calendar to construct an appropriate millisecond value for the timestamp if the underlying database does not store timezone information.

The Calendar variant is well defined, but the Calendar-less variant is
completely silent on what it should do about timezones if they're not
present in the data :(

I suspect that if we changed this to UTC, then we'd get complaints along
the lines of "ResultSet.getTimestamp(n) returns the wrong value, here, I
printed the Timestamp and it's wrong" (because Timestamp.toString() uses
the JVM's default timezone).

-O