Hello,
I've found (undocumented?) change of behavior of
DSL.val(java.sql.Timestamp).
Tested on PostgreSQL 9.4/9.5 running in UTC timezone, JVM process running
in UTC default timezone too.
Here's the query:
DSLContext create = DSL.using(connection);
create.select(DSL.function(
"timezone",
Time.class,
DSL.val("Asia/Hong_Kong"),
DSL.val(new java.sql.Timestamp(
java.time.LocalDateTime.of(2016, 5, 23, 17, 0)
.toInstant(ZoneOffset.UTC)
.toEpochMilli())))
.cast(PostgresDataType.TIMEWITHOUTTIMEZONE))
.execute();
with jOOQ 3.6.2 it generates this (correct result):
Executing query : select cast(timezone(?, ?) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', timestamp
'2016-05-23 17:00:00.0') as time without time zone)
+--------+
|cast |
+--------+
|01:00:00|
+--------+
same query with jOOQ 3.8.1 (wrong result):
Executing query : select cast(timezone(?, cast(? as timestamp)) as time
without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong', timestamp
'2016-05-23 17:00:00.0') as time without time zone)
+--------+
|cast |
+--------+
|09:00:00|
+--------+
Debug output of both generated SQL queries looks the same, but it doesn't
seem as this is the ones being executed, because results are different.
This query is wrong:
select cast(timezone('Asia/Hong_Kong', timestamp '2016-05-23 17:00:00.0')
as time without time zone)
This is the right query:
select cast(timezone('Asia/Hong_Kong', '2016-05-23 17:00:00.0') as time
without time zone);
or this (gives the same result):
select cast(timezone('Asia/Hong_Kong', cast('2016-05-23 17:00:00.0' as
timestamp with time zone)) as time without time zone);
To get the latter query I had to rewrite above jOOQ query like this:
create.select(DSL.function(
"timezone",
java.sql.Time.class,
DSL.val("Asia/Hong_Kong"),
DSL.val(java.time.OffsetDateTime.ofInstant(
Instant.ofEpochMilli(
java.time.LocalDateTime.of(2016, 5, 23, 17, 0)
.toInstant(ZoneOffset.UTC)
.toEpochMilli()),
java.time.ZoneId.of("UTC"))))
.cast(PostgresDataType.TIMEWITHOUTTIMEZONE))
.execute();
Executing query : select cast(timezone(?, cast(? as timestamp with time
zone)) as time without time zone)
-> with bind values : select cast(timezone('Asia/Hong_Kong',
cast('2016-05-23T17:00Z' as timestamp with time zone)) as time without time
zone)
+--------+
|cast |
+--------+
|01:00:00|
+--------+
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
