TIMESTAMP WITH TIME ZONE

[Ben Hood]

Hi Lukas,

What is the current idiomatic way to bind to a TIMESTAMP WITH TIME ZONE in Postgres?

I’ve seen the pointer to implement a Binding

https://www.jooq.org/doc/3.10/manual/code-generation/custom-data-type-bindings/

So I was wondering if there is an example somewhere for TIMESTAMP WITH TIME ZONE?

For reference, my schema has aliased TIMESTAMP WITH TIME ZONE in this way (to enforce UTC everywhere):

CREATE DOMAIN UTC_TIMESTAMP AS TIMESTAMP WITH TIME ZONE CHECK (EXTRACT(TIMEZONE FROM VALUE) = 0);
CREATE TYPE UTC_RANGE AS RANGE ( SUBTYPE = UTC_TIMESTAMP );

Cheers,

Ben

[Lukas Eder]

jOOQ binds TIMESTAMP WITH TIME ZONE to java.time.OffsetDateTime by default.

Domains are currently not supported by the jOOQ code generator. You will have to apply a data type rewriting to all relevant columns if you want out-of-the-box behaviour.

Why do you need that domain, though? PostgreSQL's TIMESTAMP WITH TIME ZONE type is really just a UTC timestamp... The timezone setting is a configuration parameter:

https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-TIMEZONE

--
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 Hood]

On Tue, May 8, 2018 at 1:28 PM, Lukas Eder <lukas...@gmail.com> wrote:
> jOOQ binds TIMESTAMP WITH TIME ZONE to java.time.OffsetDateTime by default.
>
> Domains are currently not supported by the jOOQ code generator. You will
> have to apply a data type rewriting to all relevant columns if you want
> out-of-the-box behaviour.

Can I do this data type rewriting as an extension point in JOOQ? Or
did you mean something Postgres specific?

> Why do you need that domain, though? PostgreSQL's TIMESTAMP WITH TIME ZONE
> type is really just a UTC timestamp... The timezone setting is a
> configuration parameter:
> https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-TIMEZONE

Maybe .... the point of the domain was to make it impossible to use a
timezone other than UTC, anywhere, whether via JOOQ or in a proc, ad
hoc script, report or anything.

You might be right - the motivation was to make timestamps absolutely
bomb proof in the DB.

[Ben Hood]

I think I might have solved the issue by putting

SET TIME ZONE ‘UTC’;

into the connection pool init and then at runtime by supplying an OffsetDateTime.

But it would be to good to get the OffsetDateTime code generation working, instead of mapping to Object.

Can this be done with a converter, or does it require a full binding?

> --
> 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.

[Lukas Eder]

2018-05-08 14:36 GMT+02:00 Ben Hood <0x6e...@gmail.com>:

On Tue, May 8, 2018 at 1:28 PM, Lukas Eder <lukas...@gmail.com> wrote:
> jOOQ binds TIMESTAMP WITH TIME ZONE to java.time.OffsetDateTime by default.
>
> Domains are currently not supported by the jOOQ code generator. You will
> have to apply a data type rewriting to all relevant columns if you want
> out-of-the-box behaviour.

Can I do this data type rewriting as an extension point in JOOQ? Or
did you mean something Postgres specific?

https://www.jooq.org/doc/latest/manual/code-generation/data-type-rewrites

[Lukas Eder]

2018-05-08 16:55 GMT+02:00 Ben Hood <b...@relops.com>:

I think I might have solved the issue by putting

SET TIME ZONE ‘UTC’;

into the connection pool init and then at runtime by supplying an OffsetDateTime.

But it would be to good to get the OffsetDateTime code generation working, instead of mapping to Object.

Can this be done with a converter, or does it require a full binding?

You can use a Converter, risking that the type jOOQ is getting back from ResultSet.getObject() is not the correct type, and risking that jOOQ will not bind variables correctly, because it does not really know how to bind Object types (e.g. what casts might be needed)

Use a Binding if you want to be sure.

Or just use the data type rewriting feature to tell jOOQ that the type is really TIMESTAMP WITH TIME ZONE. This is the least work for you. 

[Lukas Eder]

... But before you move on, please make sure you properly understand PostgreSQL's implementation of TIMESTAMP WITH TIME ZONE. In 10 years of doing jOOQ, I've never encountered anyone (ab)using domains to patch time zones on their data types. It feels like the wrong approach. I'm sure PostgreSQL gets this right.

And once you don't use domains, everything will be much easier on the jOOQ side as well.

[Ben Hood]

On 9 May 2018, at 08:30, Lukas Eder <lukas...@gmail.com> wrote:

https://www.jooq.org/doc/latest/manual/code-generation/data-type-rewrites

Sorry, I misunderstood previously - I didn’t realize you were referring to the forcedType stanza - thanks for clarifying.

[Ben Hood]

> On 9 May 2018, at 08:32, Lukas Eder <lukas...@gmail.com> wrote:
>
> You can use a Converter, risking that the type jOOQ is getting back from ResultSet.getObject() is not the correct type, and risking that jOOQ will not bind variables correctly, because it does not really know how to bind Object types (e.g. what casts might be needed)
>
> Use a Binding if you want to be sure.
>
> Or just use the data type rewriting feature to tell jOOQ that the type is really TIMESTAMP WITH TIME ZONE. This is the least work for you.

Your last point makes complete sense, and this question wouldn’t have arisen if I had actually read the title of the forcedTypes man page, which is "Data type rewrites”.

So my bad for not RFTM.

[Ben Hood]

> On 9 May 2018, at 08:33, Lukas Eder <lukas...@gmail.com> wrote:
>
> ... But before you move on, please make sure you properly understand PostgreSQL's implementation of TIMESTAMP WITH TIME ZONE. In 10 years of doing jOOQ, I've never encountered anyone (ab)using domains to patch time zones on their data types. It feels like the wrong approach. I'm sure PostgreSQL gets this right.

Many thanks for taking the time share this wisdom, which goes beyond the scope of JOOQ - very much appreciated.

For note, in my own (potentially deluded) mind, the objective of the domain was to specialize the built in TIMESTAMP WITH TIME ZONE type to make it impossible to insert a non-UTC timestamp into the database, either from an application or from a proc.

In my mind, this use of this domain is a way of re-using a check constraint across the entire schema. This check constraint applies to all columns of the domain type. I’ve also created a custom range type to extend from this domain, which inherits the check.

I am not asserting that the Postgres TIMESTAMP WITH TIME ZONE gets anything wrong. I am just observing that this data type allows clients to insert timestamps with arbitrary zone offsets. This is the behavior I want to prevent.

Note that I am ignorant of how Postgres would store this under the covers - for all I know Postgres could be storing the timestamp in UTC in addition to the offset.

So I will take your advice and seek clarification from the Postgres mailing list before moving on.

> And once you don't use domains, everything will be much easier on the jOOQ side as well.

Point taken.

[Lukas Eder]

2018-05-10 8:39 GMT+02:00 Ben Hood <b...@relops.com>:

Your last point makes complete sense, and this question wouldn’t have arisen if I had actually read the title of the forcedTypes man page, which is "Data type rewrites”.

So my bad for not RFTM.

No worries at all :) This part of the manual might be a bit confusing, as historically, the "data type rewriting" and "custom data types" are both using the <forcedTypes/> configuration element...

[Lukas Eder]

Very interesting, thanks for explaining, I'd be very curious to learn if your approach will be judged viable on that Postgres mailing list. Indeed, domains are a kind of "shared check constraint", and as such, extremely useful. But I would not have thought them to be able to validate client time zone offsets. If they're able to do that, all the better and I would stand corrected.

Do note that eventually, jOOQ will have to provide better support for domains from PostgreSQL. It's just not been a priority (yet).