H2 bad syntax for timezone

[ben....@gmail.com]

The following SQL generated by jOOQ fails with H2,

select "ENTITY"."DATA" from "ENTITY" where "ENTITY"."CREATION_DATE" = cast(current_timestamp() as timestamp with time zone)

The error is due to the space between "time" and "zone", which should be a single word. I confirmed this in the H2 Web Console.

Syntax error in SQL statement 

"SELECT ""ENTITY"".""DATA"" FROM ""ENTITY"" WHERE ""ENTITY"".""CREATION_DATE"" = CAST(CURRENT_TIMESTAMP() AS TIMESTAMP WITH TIME[*] ZONE) "; 

expected "TIMEZONE"; 

SQL statement: select "ENTITY"."DATA" from "ENTITY" where "ENTITY"."CREATION_DATE" = cast(current_timestamp() as timestamp with time zone) [42001-193]

Is this a known issue?

Thanks,

Ben

[Lukas Eder]

Hi Ben,

Yes, this is a known issue:

https://github.com/jOOQ/jOOQ/issues/5806

Note that H2 will also change its data type name to TIME ZONE like most other databases:

https://github.com/h2database/h2database/issues/411

Since there are only very few H2 releases that support only "TIMEZONE", not "TIME ZONE" (once #411 is published), I wonder if we should even handle the bad data type name in jOOQ.

Cheers,

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

[ben....@gmail.com]

Thanks Lukas,

I forgot to reply earlier. I used an ExecuteListener to replace the bad SQL expression (via renderEnd). This change and setting the default timezone to UTC resolved the issues that I encountered. So an easy workaround until the next H2 release.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Oh, very nice workaround. It hadn't even occurred to me. Thanks for documenting this here!

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.