Timestamp problem

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Timestamp problem
Date: 2008-01-03 15:16:15
Message-ID: 200801031616.16202.peter_e@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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/

Attachment Content-Type Size
dump.sql text/plain 921 bytes
out text/plain 2.0 KB
Test.java text/x-java 770 bytes

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Jonathan Mast 2008-01-03 15:47:02 Re: Permissions problem
Previous Message Jake Aderholdt 2008-01-03 12:46:26 JDBC driver class