TIMESTMAP WITH TIME ZONE in PostgreSQL

[Thorsten Schöning]

Hello all,

I have a backend which uses PostgreSQL 9.x only and some tables with
the datatype "TIMESTAMP WITH TIME ZONE". I do use jOOQ code generator
and in that the datatype of all those columns is java.sql.Timestamp
and org.jooq.impl.SQLDataType.TIMESTAMP. The problem now is that this
type is without timezone by default, whereas I do need it. My app
doesn't only store the timestamps but does compare them in different
timezones to properly get things like changes of a day etc. jOOQ
currently does generate queries like the following:

> CAST((TIMESTAMP '2014-07-03 01:01:23.0' AT TIME ZONE 'UTC') AS DATE)

The printed timestamp is created as "new Timestamp(...)" with correct
UTC milliseconds, but rendered as one in the timezone "Europe/Berlin".
Which wouldn't be a problem at all if jOOQ would properly use a
Postgres timestamp with timezone to render this field. In that case
Postgres would recognize the timezone of my DB session and properly
convert the local timestamp to UTC. Currently it uses the given
timestamp as it as UTC because "TIMESTAMP" as datatype ignores and
timezone settings of the session.


I did recognize that jOOQ seems to have support for
PostgresDataType.TIMESTAMPWITHTIMEZONE, but the code generator doesn't
seem to use that one for some reason. I did some reading of the
documentation, searched the web, debugged the code and found that
org.jooq.util.JavaGenerator.getTypeReference is behaving strangely:

http://grepcode.com/file/repo1.maven.org/maven2/org.jooq/jooq-codegen/3.4.0/org/jooq/util/JavaGenerator.java#JavaGenerator.getTypeReference%28org.jooq.util.Database%2Corg.jooq.util.SchemaDefinition%2Cjava.lang.String%2Cint%2Cint%2Cint%2Cboolean%2Cboolean%2Cjava.lang.String%29

> 2914 if (dataType != null && dataType.getSQLDataType() != null) {

jOOQ properly recognized the datatype as Psotgres specific, but with
this alternative it seems to use the default SQL datatype for some
reason. From my understanding I would have expected that it uses the
dialect specific datatype at all cases.

How can I get jOOQ to use the Postgres specific timestamp with
timezone? Already tried "forcedType" in the configuration, but didn't
work and doesn't seem to be used in the code above.

Is my problem related to the following issue? It sounds similar to
me and the thread where I came from had a similar issue. Lukas
suggested to implement a CustomField there, is that the only solution?
I don't want to think over and over again about that problem, I would
like to address it at one place, preferable the code generation
because it looks to me like jOOQ already provide everything but the
generator is simply behaving strange.

> Bad inlining of PostgreSQL's TIMESTAMP WITH TIME ZONE data type

https://github.com/jOOQ/jOOQ/issues/2738
https://groups.google.com/forum/#!msg/jooq-user/hM97PXjzksM/Ggk3w2hkEFcJ

Thanks for your help!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail:Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

[Thorsten Schöning]

Guten Tag Thorsten Schöning,
am Donnerstag, 21. August 2014 um 16:57 schrieben Sie:

> I don't want to think over and over again about that problem, I would
> like to address it at one place, preferable the code generation
> because it looks to me like jOOQ already provide everything but the
> generator is simply behaving strange.

I've replaced with a simple text editor all occurrences of
SQLDataType.TIMESTAMP with PostgresDataType.TIMESTAMPTWITHTIMEZONE and
it didn't make any difference, my SQL ist still rendered wrong.
Thinking a about it further, I think the main reason is how I provide
my timestamp:

> .where( DSL.field("({0} AT TIME ZONE 'UTC')", METER_RECORD.CAPTURED_AT)
> .cast(SQLDataType.DATE)
> .equal( DSL.field("({0} AT TIME ZONE 'UTC')", capturedAt)
> .cast(SQLDataType.DATE)))

METER_RECORD.CAPTURED_AT is timestamp with timezone in Postgres, jOOQ
generates it with SQLDataType.TIMESTAMP, this is what I replaced to
PostgresDataType.TIMESTAMPTWITHTIMEZONE because I thought that the
field with "caputerdAt" is going to become a TIMESTAMPWITHTIMEZONE as
well. It doesn't. I additionally tried the following:

> .where( DSL.field("({0} AT TIME ZONE 'UTC')", METER_RECORD.CAPTURED_AT)
> .cast(SQLDataType.DATE)
> .equal( DSL.field("({0} AT TIME ZONE 'UTC')", PostgresDataType.TIMESTAMPWITHTIMEZONE, capturedAt)
> .cast(SQLDataType.DATE)))

This time I provided the datatype I need, but didn't change anything
as well.

I debugged the code and DSL.field is ultimately running into
Util.queryParts:

> result.add(new Val<Object>(substitute, DSL.getDataType(type)));

which calls:

> return DefaultDataType.getDataType(SQLDialect.SQL99, type);

SQL99 delivers "timestamp" instead of my Postgres specific type.

How do I need to create the field to get Postgres specific timestamp
with timezone? From my current understanding of the code I won't ever
because all objects are resolved against their SQL99 types. Or is
there any rendering later which replaces those times with the dialect
specific ones? If yes, what am I doing wrong that this doesn't work
for me? Is that part of issue #2738?

Is that what Lukas meant by implementing an own CustomField, replacing
DSL.field(..., capturedAt) above with? Am I correct that I wouldn't
need that for DSL.field(..., METER_RECORD.CAPTURED_AT) because
Postgres already knows the correct datatype of that table column and
jOOQ doesn't render any wrong datatypes into the SQL for those
existing columns?

[Lukas Eder]

Hello Thorsten,

Thank you for your detailed explanations. I must admit that suggesting using a CustomField was really just a suggestion. I have not actually evaluated that option to see whether it works in all cases, e.g. in yours.

Issue #2738 is indeed the issue that should resolve this - probably for all other databases as well. However, it may not be very easy to implement, as JDBC 4.1 and less do not really support this type. This has changed with JDBC 4.2:

http://docs.oracle.com/javase/8/docs/api/java/sql/Types.html#TIMESTAMP_WITH_TIMEZONE

Unfortunately, that's not an option for us yet, as we're still supporting JDBC 4.0 / Java 6.

Long story short, currently, jOOQ does not really officially support TIMESTAMP WITH TIMEZONE. But this is certainly a thing whose priority we can increase.

I'm also positive that lack of data type support will be a thing of the past once we implement support for TypeProviders in jOOQ 3.5:

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

Best

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

[Thorsten Schöning]

Guten Tag Lukas Eder,
am Donnerstag, 21. August 2014 um 19:40 schrieben Sie:

> Thank you for your detailed explanations. I must admit that
> suggesting using a CustomField was really just a suggestion. I have
> not actually evaluated that option to see whether it works in all cases, e.g. in yours.

From what I've tried until now I think it should and I already get my
SQL rendered correctly, but there's still something wrong with it and
binding values to it. I don't seem to really understand how
CustomField.toSQL and .bind play together or such and would be very
grateful if your could have a look at my implementation.

What I currently see is that toSQL gets called once with a paramType of
null for some reason, therefore I fallback to INDEXED and generate my
SQL with "?". Afterwards toSQL is called again with paramType INLINED
and I generate my SQL with inlined values. The third call is to bind
itself and I bind according the documentation using statement of the
context, nextIndex etc.

Those calls fit to logging: First the statement gets logged as a
prepared statement with placeholders, afterwards inlined and I guess
the third call to bind is because it gets executed afterwards.

That's what I get logged:

> 21:32:12 DEBUG org.jooq.tools.LoggerListener.debug: Executing query : SELECT
> "meter_record"."id",
> "meter_record"."captured_at"
> FROM "meter_record"
> JOIN "oms_record"
> ON "meter_record"."id" = "oms_record"."meter_record"
> LEFT OUTER JOIN "meter_status_history"
> ON "oms_record"."id" = "meter_status_history"."oms_record"
> WHERE (
> CAST(("meter_record"."captured_at" AT TIME ZONE 'UTC') AS DATE) = CAST(((TIMESTAMP WITH TIME ZONE ?) AT TIME ZONE ?) AS DATE)
> AND "oms_record"."meter" = ?
> AND (
> "meter_status_history"."status" IS NULL
> OR "meter_status_history"."status" = ?
> )
> )
> ORDER BY "meter_record"."captured_at" DESC
> 21:32:15 DEBUG org.jooq.tools.LoggerListener.debug: -> with bind values : SELECT
> "meter_record"."id",
> "meter_record"."captured_at"
> FROM "meter_record"
> JOIN "oms_record"
> ON "meter_record"."id" = "oms_record"."meter_record"
> LEFT OUTER JOIN "meter_status_history"
> ON "oms_record"."id" = "meter_status_history"."oms_record"
> WHERE (
> CAST(("meter_record"."captured_at" AT TIME ZONE 'UTC') AS DATE) = CAST(((TIMESTAMP WITH TIME ZONE '2014-07-03 01:01:23.0') AT TIME ZONE 'UTC') AS DATE)
> AND "oms_record"."meter" = 1
> AND (
> "meter_status_history"."status" IS NULL
> OR "meter_status_history"."status" = 0
> )
> )
> ORDER BY "meter_record"."captured_at" DESC

The problem now is that besides the SQL looks syntactically OK to me,
I get an exception:

> Caused by: org.jooq.exception.DataAccessException: SQL [SELECT
> "meter_record"."id",
> "meter_record"."captured_at"
> FROM "meter_record"
> JOIN "oms_record"
> ON "meter_record"."id" = "oms_record"."meter_record"
> LEFT OUTER JOIN "meter_status_history"
> ON "oms_record"."id" = "meter_status_history"."oms_record"
> WHERE (
> CAST(("meter_record"."captured_at" AT TIME ZONE 'UTC') AS DATE) = CAST(((TIMESTAMP WITH TIME ZONE ?) AT TIME ZONE ?) AS DATE)
> AND "oms_record"."meter" = ?
> AND (
> "meter_status_history"."status" IS NULL
> OR "meter_status_history"."status" = ?
> )
> )
> ORDER BY "meter_record"."captured_at" DESC]; ERROR: syntax error at or near "$1"
> Position: 369
> at org.jooq.impl.Utils.translate(Utils.java:1428) ~[jooq-3.4.1.jar:na]

I guess there's something wrong with my binding, but I don't see the
problem. That's what I do:

> context.statement().setTimestamp( context.nextIndex(), this.ts);
> context.statement().setString( context.nextIndex(), this.tz.getID());

Do you have any idea of what could be the problem? I feel I'm this
close to get what I want... For your interest I attached my class.

Thanks again for any help you can provide!

[Lukas Eder]

For the record, there's a relevant question on Stack Overflow that has been asked just recently:

http://stackoverflow.com/q/25432514/521799

I'll respond to your new mail in a bit...

[Lukas Eder]

Hello,

2014-08-21 21:42 GMT+02:00 Thorsten Schöning <tscho...@am-soft.de>:

Guten Tag Lukas Eder,
am Donnerstag, 21. August 2014 um 19:40 schrieben Sie:

> Thank you for your detailed explanations. I must admit that
> suggesting using a CustomField was really just a suggestion. I have
> not actually evaluated that option to see whether it works in all cases, e.g. in yours.

From what I've tried until now I think it should and I already get my
SQL rendered correctly, but there's still something wrong with it and
binding values to it. I don't seem to really understand how
CustomField.toSQL and .bind play together or such and would be very
grateful if your could have a look at my implementation.

What I currently see is that toSQL gets called once with a paramType of
null for some reason, therefore I fallback to INDEXED and generate my
SQL with "?".

That's a bug and your workaround is correct. Thanks for mentioning that. This will be fixed:

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

Afterwards toSQL is called again with paramType INLINED
and I generate my SQL with inlined values.

Yes, that's probably due to debug logging, right? The same query can be rendered several times, depending on what various ExecuteListeners (such as the logger) do with it.

 

The third call is to bind
itself and I bind according the documentation using statement of the
context, nextIndex etc.

Those calls fit to logging: First the statement gets logged as a
prepared statement with placeholders, afterwards inlined and I guess
the third call to bind is because it gets executed afterwards.

Yes. The relevant code triggering those runs is in AbstractQuery:

SQL rendering:

https://github.com/jOOQ/jOOQ/blob/258afc69fe736d0a8c15a6ffe0a57c7d69ed9cc6/jOOQ/src/main/java/org/jooq/impl/AbstractQuery.java#L294

Variable binding:

https://github.com/jOOQ/jOOQ/blob/258afc69fe736d0a8c15a6ffe0a57c7d69ed9cc6/jOOQ/src/main/java/org/jooq/impl/AbstractQuery.java#L329

The problem is probably that you confused a TIMESTAMP WITH TIME ZONE literal (which is a literal = a constant, not a bind value) with the corresponding bind value. What you might need to do instead is either one of these:

CAST(? AS TIMESTAMP WITH TIME ZONE)

?::TIMESTAMP WITH TIME ZONE

This works for me:

DSL.using(configuration).fetch("SELECT CAST(CAST(? AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE ? AS DATE)", new java.sql.Timestamp(0), "UTC");

Otherwise, the implementation looks fine, well done!

[Thorsten Schöning]

Guten Tag Lukas Eder,
am Freitag, 22. August 2014 um 15:40 schrieben Sie:

> That's a bug and your workaround is correct. Thanks for mentioning that. This will be fixed:
> https://github.com/jOOQ/jOOQ/issues/3586

Thanks. Could you have another look at
org.jooq.util.JavaGenerator.getTypeReference with the following lines
as well please?

> 2912 // If there is a standard SQLDataType available for the dialect-
> 2913 // specific DataType t, then reference that one.

> 2914 if (dataType != null && dataType.getSQLDataType() != null) {

I don't understand why dialect specific types with available
SQLDataType are ignored in favor of the SQLDataType. Every dialect
specific Postgres type has a SQLDataType associated and therefore from
my limited current understanding would never be used in code
generation at all. I would have expected that every dialect specific
type gets precedence to get their special behavior and implementation
details. Or that a user can override the above behavior somehow to get
it's dialect specific types.

As mentioned earlier, during generation jOOQ properly recognizes
PostgresDataType.TIMEZONEWITHTIMESTAMP at the above lines, I checked
using a debugger in Eclipse, but afterwards SQLDataType.Timestamp is
used because that is associated with the Postgres specific type.

> The problem is probably that you confused a TIMESTAMP WITH TIME
> ZONE literal (which is a literal = a constant, not a bind value)
> with the corresponding bind value. What you might need to do instead is either one of these:

You are right, that was the issue, found it just some minutes ago as
well. :-) Thanks!

http://stackoverflow.com/a/13694068/2055163

[Lukas Eder]

2014-08-22 16:22 GMT+02:00 Thorsten Schöning <tscho...@am-soft.de>:

Guten Tag Lukas Eder,
am Freitag, 22. August 2014 um 15:40 schrieben Sie:

> That's a bug and your workaround is correct. Thanks for mentioning that. This will be fixed:
> https://github.com/jOOQ/jOOQ/issues/3586

Thanks. Could you have another look at
org.jooq.util.JavaGenerator.getTypeReference with the following lines
as well please?

> 2912            // If there is a standard SQLDataType available for the dialect-
> 2913            // specific DataType t, then reference that one.

> 2914            if (dataType != null && dataType.getSQLDataType() != null) {

I don't understand why dialect specific types with available
SQLDataType are ignored in favor of the SQLDataType. Every dialect
specific Postgres type has a SQLDataType associated and therefore from
my limited current understanding would never be used in code
generation at all. I would have expected that every dialect specific
type gets precedence to get their special behavior and implementation
details. Or that a user can override the above behavior somehow to get
it's dialect specific types.

The PostgresDataType.TIMESTAMPWITHTIMEZONE type is currently treated as a *synonym* for SQLDataType.TIMESTAMP by the declaration in PostgresDataType. This is not correct, of course, and will be fixed with 

https://github.com/jOOQ/jOOQ/issues/2738 (among other things that will be fixed).

It's true that these things are not immediately obvious (or particularly well designed) from the various DataType classes.

[Thorsten Schöning]

Guten Tag Lukas Eder,
am Freitag, 22. August 2014 um 17:19 schrieben Sie:

> The PostgresDataType.TIMESTAMPWITHTIMEZONE type is currently
> treated as a *synonym* for SQLDataType.TIMESTAMP by the declaration
> in PostgresDataType.

Thanks for the clarification, seems I totally misunderstood the
concept. So I will stay with the custom field approach for now.

[Lukas Eder]

2014-08-22 17:44 GMT+02:00 Thorsten Schöning <tscho...@am-soft.de>:

Guten Tag Lukas Eder,
am Freitag, 22. August 2014 um 17:19 schrieben Sie:

> The PostgresDataType.TIMESTAMPWITHTIMEZONE type is currently
> treated as a *synonym* for SQLDataType.TIMESTAMP by the declaration
> in PostgresDataType.

Thanks for the clarification, seems I totally misunderstood the
concept. So I will stay with the custom field approach for now.

Yes, but it's indeed confusing and not straight-forward. We're really hoping that TypeProviders will help cleaning these things up.