Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: Test.java
Description: text/x-java (770 bytes)
Attachment: out
Description: text/plain (2.0 KB)
Attachment: dump.sql
Description: text/plain (921 bytes)

Responses

pgsql-jdbc by date

Next:From: Jonathan MastDate: 2008-01-03 15:47:02
Subject: Re: Permissions problem
Previous:From: Jake AderholdtDate: 2008-01-03 12:46:26
Subject: JDBC driver class

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group