From: | Oliver Jowett <oliver(at)opencloud(dot)com> |
---|---|
To: | Lukas Eder <lukas(dot)eder(at)gmail(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: java.sql.ResultSet.getTime() returns wrong time |
Date: | 2010-09-19 21:28:09 |
Message-ID: | 4C968069.4050801@opencloud.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Lukas Eder wrote:
> I have experienced a very peculiar issue with the postgresql JDBC driver
> when calling java.sql.ResultSet.getTime() or getTimestamp() on a field
> of type timetz.
>
> Here is how to reproduce the issue:
>
> 1. Set date and time to 2010-09-19 14:57:00 CEST (central european
> summer time: UTC+2) or something similar
> 2. Fetch "SELECT current_time" from the Postgres database directly.
> This will return the correct time, e.g "14:57:17.116452+02"
> 3. Fetch "SELECT current_time" from the Postgres JDBC driver. This
> will return a wrong time, e.g 13:57:17
The problem is this: there is no information that says the time string
that you are converting is covered by a particular set of timezone
rules. The only information given to the driver is that it is a time
with a fixed offset of +0200.
So the returned time is a representation of 1970-01-01 14:57:17 +0200,
NOT 1970-01-01 14:57:17 in your local timezone. When java.sql.Time then
applies the local timezone, you are in essence asking "What time is
1970-01-01 14:57:17 +0200 in the local timezone?" to which the answer is
"13:57:17".
Using the current date instead of 1970-01-01 is explicitly wrong for
java.sql.Time (see its javadoc), and is conceptually wrong for
timestamps: how do you know the JVM's current timezone rules are
applicable to that particular time? Consider the case where you stored
current_time in a timetz column, then retrieved it 6 months later after
the local daylight savings rules changed.
Did you see Kris's earlier response here? See
http://archives.postgresql.org/pgsql-jdbc/2010-05/msg00052.php. The
problem is we need to pass around a timezone offset, but JDBC +
java.util.Date give us no way to do that without subclassing those types
(which seems a bit hairy). Without that extra data, timetz just doesn't
map well to any of the standard Java date/time types.
-O
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-09-19 21:43:20 | Re: java.sql.ResultSet.getTime() returns wrong time |
Previous Message | admin | 2010-09-19 16:42:55 | Re: Broken pipe error |