Обсуждение: Timestamp problem

I have this problem where ResultSet.getTimestamp() messes up the value if the
underlying column is of type timestamp without time zone.  I have constructed
a test case.  Load the attached dump into a database.  It creates a table
with some example values, stored both as type varchar and timestamp, for
cross-checking.  The run the test program Test.java on that database.  I see
this example output if I have TZ=CET set in the environment:

Column 1 returned 15000000012
Column 2 returned as string 2007-03-25 00:30:00
Column 2 returned as timestamp 2007-03-25 00:30:00.0
Column 3 returned 25.03.2007 00:30:00
Column 1 returned 15000000013
Column 2 returned as string 2007-03-25 01:30:00
Column 2 returned as timestamp 2007-03-25 01:30:00.0
Column 3 returned 25.03.2007 01:30:00
Column 1 returned 15000000014
Column 2 returned as string 2007-03-25 02:30:00
Column 2 returned as timestamp 2007-03-25 03:30:00.0
PROBLEM:                                  ^^^^^^^^^^
Column 3 returned 25.03.2007 02:30:00
Column 1 returned 15000000015
Column 2 returned as string 2007-03-25 03:30:00
Column 2 returned as timestamp 2007-03-25 03:30:00.0
Column 3 returned 25.03.2007 03:30:00
Column 1 returned 15000000016
Column 2 returned as string 2007-03-25 04:30:00
Column 2 returned as timestamp 2007-03-25 04:30:00.0
Column 3 returned 25.03.2007 04:30:00

Note that 2007-03-25 between 02:00 and 03:00 is the change to daylight-saving
time.  In a DST-aware environment, the time 02:30 does not exist.  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.

I suppose what is happening internally here is that the JDBC driver converts
the value back and forth between several representations and because in one
of those represenations 02:30 is not valid, the value gets distorted.

The rest of the test data, which I have omitted from above, tests the change
from DST to normal time on 2007-10-28, which has no problems.

I can reproduce this with any PostgreSQL JDBC driver in existence, but FWIW,
the above output is from postgresql-8.3dev-602.jdbc4.jar, and java is

java version "1.6.0"
Java(TM) SE Runtime Environment (build 1.6.0-b105)
Java HotSpot(TM) Client VM (build 1.6.0-b105, mixed mode, sharing)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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. If
you want to avoid DST and similar you should explicitly pass a Calendar
object to Timestamp for a timezone that does not use daylight savings
(e.g. UTC) and use the same timezone to interpret the Timestamp.

The internal representation of java.sql.Timestamp (which is out our
control) is seconds-since-epoch, so you simply can't represent all
possible times-without-timezone if you interpret that using rules from a
timezone with daylight savings. In your case there is no possible
seconds-since-epoch value that will represent '2007-03-25 02:30:00' in
your default timezone.

-O

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?  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?

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.

> If
> you want to avoid DST and similar you should explicitly pass a Calendar
> object to Timestamp for a timezone that does not use daylight savings
> (e.g. UTC) and use the same timezone to interpret the Timestamp.

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.

The annoying thing is that the developers in this case explicitly chose the
timestamp without time zone type to avoid time zone issues altogether.  (All
their internal reckoning is in UTC.)  So making them pass time zone
information around isn't really a good answer in any case.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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
objectin 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
objectin the Java programming language. This method uses the given calendar to construct an appropriate millisecond
valuefor 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