Обсуждение: ResultSet.getObject(..., LocalTime.class) not working with Postgrestimetz type

Retrieving the value of a timetz column fails when using getObject(, LocalTime.class)

Consider the following code:

   Connection con = DriverManager.getConnection(...);
   Statement stmt = con.createStatement();
   ResultSet rs = stmt.executeQuery("select current_time");
   rs.next();
   LocalTime lt = rs.getObject(1, LocalTime.class);

The above fails with:

org.postgresql.util.PSQLException: Bad value for type timestamp/date/time: {1}
    at org.postgresql.jdbc.TimestampUtils.toLocalTime(TimestampUtils.java:433)
    at org.postgresql.jdbc.PgResultSet.getLocalTime(PgResultSet.java:563)
    at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:3391)
    at TestLocalTime.main(TestLocalTime.java:23)
Caused by: java.time.format.DateTimeParseException: Text '09:48:19.747249+02' could not be parsed, unparsed text found
atindex 15
 
    at java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1952)
    at java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1851)
    at java.time.LocalTime.parse(LocalTime.java:441)
    at java.time.LocalTime.parse(LocalTime.java:426)
    at org.postgresql.jdbc.TimestampUtils.toLocalTime(TimestampUtils.java:430)

getObject(1, OffsetTime.class) fails with "conversion to class java.time.OffsetTime from 92 not supported"

I am not sure if this is the same as: https://github.com/pgjdbc/pgjdbc/issues/1048
If it's not the same root cause, should I create an issue?

Using "select localtime" instead, works just fine


Regards
Thomas
  

For reference, the JDBC specification does not specify support 
java.time.LocalXXX types on WITH TIME ZONE types.

Especially because it is rather ambiguous what local would mean: would 
it mean local in the default JVM time zone, local at the original zone 
or local at UTC?

Mark

On 6-4-2019 09:59, Thomas Kellerer wrote:
> Retrieving the value of a timetz column fails when using getObject(, 
> LocalTime.class)
> 
> Consider the following code:
> 
>    Connection con = DriverManager.getConnection(...);
>    Statement stmt = con.createStatement();
>    ResultSet rs = stmt.executeQuery("select current_time");
>    rs.next();
>    LocalTime lt = rs.getObject(1, LocalTime.class);
> 
> The above fails with:
> 
> org.postgresql.util.PSQLException: Bad value for type 
> timestamp/date/time: {1}
>      at 
> org.postgresql.jdbc.TimestampUtils.toLocalTime(TimestampUtils.java:433)
>      at org.postgresql.jdbc.PgResultSet.getLocalTime(PgResultSet.java:563)
>      at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:3391)
>      at TestLocalTime.main(TestLocalTime.java:23)
> Caused by: java.time.format.DateTimeParseException: Text 
> '09:48:19.747249+02' could not be parsed, unparsed text found at index 15
>      at 
> java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1952) 
> 
>      at 
> java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1851)
>      at java.time.LocalTime.parse(LocalTime.java:441)
>      at java.time.LocalTime.parse(LocalTime.java:426)
>      at 
> org.postgresql.jdbc.TimestampUtils.toLocalTime(TimestampUtils.java:430)
> 
> getObject(1, OffsetTime.class) fails with "conversion to class 
> java.time.OffsetTime from 92 not supported"
> 
> I am not sure if this is the same as: 
> https://github.com/pgjdbc/pgjdbc/issues/1048
> If it's not the same root cause, should I create an issue?
> 
> Using "select localtime" instead, works just fine
> 
> 
> Regards
> Thomas
> 
> 
> 
> 
> 


-- 
Mark Rotteveel

Mark Rotteveel schrieb am 06.04.2019 um 10:15:
>> Retrieving the value of a timetz column fails when using getObject(, LocalTime.class)
>>
>> I am not sure if this is the same as: https://github.com/pgjdbc/pgjdbc/issues/1048
>> If it's not the same root cause, should I create an issue?
>>
>> Using "select localtime" instead, works just fine

> For reference, the JDBC specification does not specify support java.time.LocalXXX types on WITH TIME ZONE types.
>
> Especially because it is rather ambiguous what local would mean: would it mean local in the default JVM time zone,
localat the original zone or local at UTC? 

Makes sense.

However, the column is reported as Types.TIME and not Types.TIME_WITH_TIMEZONE, that's why I stumbled over it.

I think the driver should then support getObject(1, OffsetTime.class), shouldn't it?

Thomas

On 06.04.19 19:40, Thomas Kellerer wrote:
> Mark Rotteveel schrieb am 06.04.2019 um 10:15:
>>> Retrieving the value of a timetz column fails when using getObject(, 
>>> LocalTime.class)
>>>
>>> I am not sure if this is the same as: 
>>> https://github.com/pgjdbc/pgjdbc/issues/1048
>>> If it's not the same root cause, should I create an issue?
>>>
>>> Using "select localtime" instead, works just fine
> 
>> For reference, the JDBC specification does not specify support 
>> java.time.LocalXXX types on WITH TIME ZONE types.
>>
>> Especially because it is rather ambiguous what local would mean: would 
>> it mean local in the default JVM time zone, local at the original zone 
>> or local at UTC?
> 
> Makes sense.
> 
> However, the column is reported as Types.TIME and not 
> Types.TIME_WITH_TIMEZONE, that's why I stumbled over it.
> 
> I think the driver should then support getObject(1, OffsetTime.class), 
> shouldn't it?

It was originally not implemented out of laziness, somewhat justified by 
the fact that the documentation recommends against using the type saying 
it's only present because of legacy and standards compliance reasons. 
 From a functional point of view there is nothing wrong with 
implementing it. Again, in practice it's hard to find a use for the type.

Yes the type should be Types.TIME_WITH_TIMEZONE. However in #695 [2] it 
was decided that following the JDBC specification would break frameworks 
and it would be better to instead go against the JDBC specification and 
instead having each framework implement driver specific hacks.

  [1] 
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES 

  [2] https://github.com/pgjdbc/pgjdbc/pull/695

Cheers
Philippe

On 9-4-2019 13:20, pm@netcetera.ch wrote:
> On 06.04.19 19:40, Thomas Kellerer wrote:
>> I think the driver should then support getObject(1, OffsetTime.class), 
>> shouldn't it?
> 
> It was originally not implemented out of laziness, somewhat justified by 
> the fact that the documentation recommends against using the type saying 
> it's only present because of legacy and standards compliance reasons. 
>  From a functional point of view there is nothing wrong with 
> implementing it. Again, in practice it's hard to find a use for the type.
> 
> Yes the type should be Types.TIME_WITH_TIMEZONE. However in #695 [2] it 
> was decided that following the JDBC specification would break frameworks 
> and it would be better to instead go against the JDBC specification and 
> instead having each framework implement driver specific hacks.

This decision will still mean that frameworks will have to implement 
driver-specific hacks though: That is those tools or frameworks that do 
follow the guidance of the JDBC 4.2 or higher specification and expect 
to be able to obtain the LocalXXX types here.

If you declare that a column is Types.TIME(STAMP), then you should also 
be able to return java.time.LocalTime, java.time.LocalDateTime (and of 
course java.sql.Time/java.sql.Timestamp).

Declaring Types.TIME(STAMP), but not supporting java.time.LocalXXX is in 
my opinion a worse 'violation' of the JDBC specification compared to 
declaring Types.TIME(STAMP) instead of Types.TIME(STAMP)_WITH_TIMEZONE. 
By saying you are Types.TIMESTAMP, you implicitly promise to deliver the 
type conversions defined in Appendix B of JDBC 4.3 (B.4/B.5). Supporting 
java.time.OffsetDateTime / java.time.OffsetTime on Types.TIME(STAMP) is 
then just a non-standard extension.

That of course leaves the problem of ambiguity what local means in the 
context of a type with time zone information.

Mark
-- 
Mark Rotteveel

On 2019-04-16 02:20, Dave Cramer wrote:
> The whole time, timestamp, timezone thing was not well thought out and
> is the bane of all driver implementers existence...

With timezone support coming in Firebird 4 and having implemented it 
just recently in Jaybird 4, I look forward with a bit of trepidation to 
those releases...

Mark

On 16.04.19 02:20, Dave Cramer wrote:
> 
> 
> The whole time, timestamp, timezone thing was not well thought out and 
> is the bane of all driver implementers existence...


Pre JDBC 4.2 and java.sql types I agree.

JDBC 4.2+ and java.time types I disagree. Issues with JDBC 4.2 and 
java.time types start when:

- people want backwards compatibility with bugs in java.sql
- support is implemented in terms of java.sql
- people want to support other java.time types and the direct 1:1 mappings

If you stay away from these then everything is really easy.

Cheers
Philippe

On 16.04.19 02:20, Dave Cramer wrote:
> 
> 
> The whole time, timestamp, timezone thing was not well thought out and 
> is the bane of all driver implementers existence...


Pre JDBC 4.2 and java.sql types I agree.

JDBC 4.2+ and java.time types I disagree. Issues with JDBC 4.2+ and 
java.time types start when:

- people want backwards compatibility with bugs in java.sql
- support is implemented in terms of java.sql
- people want to support other java.time types besides the direct 1:1 
mappings

If you stay away from these then everything is really easy.

Cheers
Philippe

On 10.04.19 14:43, Mark Rotteveel wrote:
> On 9-4-2019 13:20, pm@netcetera.ch wrote:
>> On 06.04.19 19:40, Thomas Kellerer wrote:
>>> I think the driver should then support getObject(1, 
>>> OffsetTime.class), shouldn't it?
>>
>> It was originally not implemented out of laziness, somewhat justified 
>> by the fact that the documentation recommends against using the type 
>> saying it's only present because of legacy and standards compliance 
>> reasons.  From a functional point of view there is nothing wrong with 
>> implementing it. Again, in practice it's hard to find a use for the type.
>>
>> Yes the type should be Types.TIME_WITH_TIMEZONE. However in #695 [2] 
>> it was decided that following the JDBC specification would break 
>> frameworks and it would be better to instead go against the JDBC 
>> specification and instead having each framework implement driver 
>> specific hacks.
> 
> This decision will still mean that frameworks will have to implement 
> driver-specific hacks though: That is those tools or frameworks that do 
> follow the guidance of the JDBC 4.2 or higher specification and expect 
> to be able to obtain the LocalXXX types here.
> 
> If you declare that a column is Types.TIME(STAMP), then you should also 
> be able to return java.time.LocalTime, java.time.LocalDateTime (and of 
> course java.sql.Time/java.sql.Timestamp).
> 
> Declaring Types.TIME(STAMP), but not supporting java.time.LocalXXX is in 
> my opinion a worse 'violation' of the JDBC specification compared to 
> declaring Types.TIME(STAMP) instead of Types.TIME(STAMP)_WITH_TIMEZONE. 
> By saying you are Types.TIMESTAMP, you implicitly promise to deliver the 
> type conversions defined in Appendix B of JDBC 4.3 (B.4/B.5). Supporting 
> java.time.OffsetDateTime / java.time.OffsetTime on Types.TIME(STAMP) is 
> then just a non-standard extension.
> 
> That of course leaves the problem of ambiguity what local means in the 
> context of a type with time zone information.

I agree with you. We should report _WITH_TIMEZONE as the type and 
support Offset* types for the tz database types. For the non tz types we 
support the Local* types and report Types accordingly.

This is a case were deviating from the standard with well defined 
semantics just leads to more and more issues down the road.

Cheers
Philippe

On 17.04.19 19:10, Dave Cramer wrote:
> Not everyone agrees with WITH TIMEZONE qnd it doesn't help that the 
> server does not store the timezone

It is my understanding the server converts to UTC when storing and 
returns UTC.

Cheers
Philippe

On 17.04.19 19:10, Dave Cramer wrote:
> Not everyone agrees with WITH TIMEZONE qnd it doesn't help that the 
> server does not store the timezone

It is my understanding the server converts to UTC when storing and 
returns UTC.

Cheers
Philippe

On 19.04.19 15:36, Dave Cramer wrote:
> 
> 
> On Thu, 18 Apr 2019 at 13:19, Philippe Marschall <pm@netcetera.ch 
> <mailto:pm@netcetera.ch>> wrote:
> 
>     On 17.04.19 19:10, Dave Cramer wrote:
>      > Not everyone agrees with WITH TIMEZONE qnd it doesn't help that the
>      > server does not store the timezone
> 
>     It is my understanding the server converts to UTC when storing and
>     returns UTC.
> 
> 
> Yes, so the problem becomes what Timezone should we convert it to ? The 
> server timezone, or the client timezone?

I don't think we should do conversion. To me JDBC in an interface to the 
database, it exposes the database functionality, behavior and semantics. 
I personally expect JDBC to return what the database returns. If I want 
to have it converted to something else then I have to do that with the 
semantics I want.
Java 8 Date Time types allow us to do exactly this.

Cheers
Philippe