Dialect dependent converter for OffsetDateTime

[Philipp Middendorf]

Hi!

I'm using H2 and Postgres as data base backends, using the same DDL script to initialize both. With columns of type "TIMESTAMP WITH TIME ZONE", Postgres indeed creates a column of this type. H2, however, doesn't complain and creates a column without a time zone. The column SQLDataType is TIMESTAMPWITHTIMEZONE and I'm using OffsetDateTime as the backing data type. However, this leads to a parse error because apparently, H2 returns an ISO time stamp without a time zone at the end.

Long story short, is it possible to attach a dialect-dependent converter to my column, so I can treat H2 different from Postgres and the others? Or is there an even better solution?

  Regards

    Philipp

[Lukas Eder]

Hi Philipp,

Yes, you can implement dialect-specific logic in data type bindings (org.jooq.Binding). Converters are just a very much simplified version for simple use-cases. More information here:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

Let me know if you need more help.

By the way, Thomas Müller from H2 is just now working on this feature. I think there's some experimental support for TIMESTAMP WITH TIME ZONE in the latest version

Lukas

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Philipp Middendorf]

Hi Lukas,

thanks for the incredibly fast reply! Since we're using H2 only for testing purposes, I tried the latest git version and indeed, there's "timestamp with time zone" available. However, the javadoc for the time parsing function in h2 states:

 

Parse a time string. The format is: [-]hour:minute:second[.nanos] ...

And jooq tries to pass, for instance, '1987-08-21T00:00Z', so I get the error:

org.jooq.exception.DataAccessException: SQL [null]; Cannot parse "TIMESTAMP WITH TIMEZONE" constant "1987-08-21T00:00Z"; 

Could this be a bug in jooq, then? 

Philipp

[Philipp Middendorf]

Upon further investigation, it seems that jOOQ calls OffsetDateTime.toString, which uses the "shortest" string available. So if there are no seconds, it'll not output ":00" but omit it. I confirmed that it works if I add a second to the timestamp so it's 1987-08-21T00:00:01Z. Should I file a github issue?

[Lukas Eder]

2016-05-26 12:02 GMT+02:00 Philipp Middendorf <phill...@gmail.com>:

Hi Lukas,

thanks for the incredibly fast reply! Since we're using H2 only for testing purposes, I tried the latest git version and indeed, there's "timestamp with time zone" available. However, the javadoc for the time parsing function in h2 states:

 

Parse a time string. The format is: [-]hour:minute:second[.nanos] ...

And jooq tries to pass, for instance, '1987-08-21T00:00Z', so I get the error:

I have to say here that JSR 310 support and TIMESTAMP WITH TIME ZONE support is still experimental in jOOQ as well. Unfortunately, the JDBC spec isn't exactly encouraging in this area, and the drivers have hardly yet implemented these features, so we're a bit lost right now.

In the case of H2, we'll wait until the feature is no longer experimental before settling for an official out of the box data type binding. For instance, I'm surprised that the time format allows the minus sign, given that TIME is a strictly "positive" value (as opposed to, e.g., INTERVAL)

org.jooq.exception.DataAccessException: SQL [null]; Cannot parse "TIMESTAMP WITH TIMEZONE" constant "1987-08-21T00:00Z"; 

Could this be a bug in jooq, then?

Or... a missing / incomplete feature :)

[Lukas Eder]

2016-05-26 12:25 GMT+02:00 Philipp Middendorf <phill...@gmail.com>:

Upon further investigation, it seems that jOOQ calls OffsetDateTime.toString, which uses the "shortest" string available. So if there are no seconds, it'll not output ":00" but omit it. I confirmed that it works if I add a second to the timestamp so it's 1987-08-21T00:00:01Z. Should I file a github issue?

Hmm, that's weird. It would seem more sensible to always produce the same timestamp length. Yes, please, file a GitHub issue. This should definitely be improved