Thread: java.sql.ResultSet.getTime() returns wrong time

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

After some debugging, I found out that in TimestampUtils.toTime(Calendar, String), the date is zeroed out, i.e. 2010-09-19 is turned into 1970-01-01. Of course, January 1 is CET (UTC+1) and not CEST (UTC+2), which explains the result being one hour off. Since I'm requesting the current time on a day in September, I would expect to receive the time as it is displayed in summer time, and not on any arbitrary day in the past. I found some similar bug reports on your archive with no solution yet, e.g.:

http://archives.postgresql.org/pgsql-jdbc/2010-05/msg00041.php

Cheers,
Lukas

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

Oliver Jowett <oliver@opencloud.com> writes:
> 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.

timetz is a fundamentally brain-dead data type to start with ---
it simply doesn't carry enough information to deal with timezones
meaningfully, at least not once you start considering DST changes.
This is the SQL standard's fault not ours, so there's not a lot
we can do about it other than recommend people avoid timetz.

            regards, tom lane

Hi folks,

Thanks for your feedback, guys. Tom, you're right, I would not have used timetz, except for the fact, that the current_time returns exactly that. But I guess, that's not a driver problem. So, Oliver, I understand your argument, it sounds logical. I guess then, it's a feature, not a bug ;-)

Cheers
Lukas