Hello Dmitry, Thank you very much for your detailed E-Mail. I will comment inline.
2016-05-23 21:44 GMT+02:00 Dmitry Gusev <[email protected]>: > 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(); > > Just to be sure to get the full picture: Is there any reason for this conversion chain? Why not Timestamp.valueOf(LocalDateTime.of(...)) ? > 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| > +--------+ > But that cast, which is generated by jOOQ seems to be correct, no? After all, you're binding a java.sql.Timestamp, which corresponds to the SQL TIMESTAMP data type. Temporal casts are sometimes necessary in PostgreSQL in order to correctly disambiguate the types. If you want to stay in control of your bind variables, you could use an org.jooq.Binding. > Debug output of both generated SQL queries looks the same, > Yes, that debug output inlines all bind variables for convenience. As you can see, the two versions *should* really be 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) > I'd say, it's "correct" because you bound a java.sql.Timestamp variable. But it's obviously "wrong" because that's not what you expected. > > This is the right query: > > select cast(timezone('Asia/Hong_Kong', '2016-05-23 17:00:00.0') as time > without time zone); > Hmm, I see. You could pass the Timestamp.toString() value instead of the Timestamp value itself, to achieve this behaviour. > 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); > Indeed. Unfortunately, formal TIMESTAMP WITH TIME ZONE data type support is still on the roadmap in jOOQ. The JSR-310 support that you're using in the next example... 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| > +--------+ > > ... is not yet complete or fully documented. Now, the question is why this worked for you before. Perhaps by accident? -- 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.
