Обсуждение: JDBC and timetz again

Hello,

last year I asked[1] about not being able go use ResultSet.getObject(..., LocalTime.class) for columns defined as
timetz.
This typically happens when using "current_time" in a SELECT statement.

So I decided to switch to ResultSet.getTime() for those columns. However, it turns out this doesn't work properly when
DSTis in effect. 

Postgres runs locally on my laptop and my Windows is set to Europe/Berlin and to automatically adjust DST.

But when I run "SELECT current_time" at 10:00 ResultSet.getTime() returns 09:00.

I had a brief look at the source code, and it seems that the driver does try to adjust the time to the current TimeZone
(asreturned from Calendar). But still returning a time that is an hour off seems like a bug to me. 

I managed to work around that, by adding the TimeZone.getRawOffset() to the time retrieved from the driver. But that
seemswrong. 

Any ideas?


Regards
Thomas

[1] https://www.postgresql.org/message-id/flat/q89m8j%2463i8%241%40blaine.gmane.org

On 2020-04-21 08:00, Thomas Kellerer wrote:
> Hello,
> 
> last year I asked[1] about not being able go use
> ResultSet.getObject(..., LocalTime.class) for columns defined as
> timetz.
> This typically happens when using "current_time" in a SELECT statement.
> 
> So I decided to switch to ResultSet.getTime() for those columns.
> However, it turns out this doesn't work properly when DST is in
> effect.
> 
> Postgres runs locally on my laptop and my Windows is set to
> Europe/Berlin and to automatically adjust DST.
> 
> But when I run "SELECT current_time" at 10:00 ResultSet.getTime() 
> returns 09:00.
> 
> I had a brief look at the source code, and it seems that the driver
> does try to adjust the time to the current TimeZone (as returned from
> Calendar). But still returning a time that is an hour off seems like a
> bug to me.
> 
> I managed to work around that, by adding the TimeZone.getRawOffset()
> to the time retrieved from the driver. But that seems wrong.
> 
> Any ideas?


Could it be it is using 1970-01-01 as a base date in a conversion 
somewhere, so its rebasing the time with DST to non-DST time?

Note that JDBC itself doesn't define support for java.sql.Time (or 
java.time.LocalTime) for time with time zone types (instead it maps to 
java.time.OffsetTime).

Mark

Mark Rotteveel schrieb am 21.04.2020 um 11:02:
> Note that JDBC itself doesn't define support for java.sql.Time (or
> java.time.LocalTime) for time with time zone types (instead it maps
> to java.time.OffsetTime).
I know that JDBC doesn't support that (and I mentioned the thread were that was discussed previously) but I would
expectthe driver to at least return a value that is correct. 

The interesting thing is, that if I use ResultSet.getString() on that column I do get the correct "value", e.g. the
following:

  ResultSet rs = stmt.executeQuery("select current_time");
  rs.next();
  System.out.println("getString: " + rs.getString(1));
  System.out.println("getTime:   " + rs.getTime(1));

will print (when run at 12:26:08):

  getString: 12:26:08.275455+02
  getTime:   11:26:08

So at some point the driver does get the correct time, but the conversion to a java.sql.Time seems to lose one hour
somewhere.

Thomas

On 2020-04-21 12:28, Thomas Kellerer wrote:
> Mark Rotteveel schrieb am 21.04.2020 um 11:02:
>> Note that JDBC itself doesn't define support for java.sql.Time (or
>> java.time.LocalTime) for time with time zone types (instead it maps
>> to java.time.OffsetTime).
> I know that JDBC doesn't support that (and I mentioned the thread were
> that was discussed previously) but I would expect the driver to at
> least return a value that is correct.
> 
> The interesting thing is, that if I use ResultSet.getString() on that
> column I do get the correct "value", e.g. the following:
> 
>   ResultSet rs = stmt.executeQuery("select current_time");
>   rs.next();
>   System.out.println("getString: " + rs.getString(1));
>   System.out.println("getTime:   " + rs.getTime(1));
> 
> will print (when run at 12:26:08):
> 
>   getString: 12:26:08.275455+02
>   getTime:   11:26:08
> 
> So at some point the driver does get the correct time, but the
> conversion to a java.sql.Time seems to lose one hour somewhere.

To be clear, I haven't checked exactly what the PostgreSQL JDBC driver 
does, but I think that it has to do with this part of the awkward 
definition of `java.sql.Time`[1]:

"""
The date components should be set to the "zero epoch" value of January 
1, 1970 and should not be accessed.
"""

Take 12:26:08 in Europe/Berlin, today (with DST) that is 10:26:08 UTC, 
take 10:26:08 UTC on 1970-01-01 and in Europe/Berlin it's 11:26:08.

[1]: 
https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Time.html