Jooq: Add DSL.offsetDateTimeDiff() and offsetDateTimeAdd()

[t9t]

Expected behavior and actual behavior:

I expect there to be a DSL.timestampDiff equivalent which takes OffsetDateTime and/or Field<OffsetDateTime> as arguments.

Steps to reproduce the problem:

I'm upgrading my code which current uses jOOQ 3.9 to jOOQ 3.10 and noticed that some of my code breaks, because the PostgreSQL time timestamp with time zone is now translated to OffsetDateTime (instead of Timestamp before). However, there is no matching DSL.timestampDiff() to compare OffsetDateTime.

For reference, my code currently looks like this, to determine a date field is more than 7 days in the past:

.where(DSL.timestampDiff(DSL.currentTimestamp(), USER.LAST_SYNC).ge(DayToSecond(7)))

"user"."last_sync" is a timestamp with time zone which was formerly translated into a Timestamp, and now a OffsetDateTime (note also that current_timestamp() in PostgreSQL returns a timestamp with time zone).

Versions:

• jOOQ: 3.10.1
• Java: 1.8.0_101 (with Kotlin 1.1.51)
• Database (include vendor): Postgresql 10
• JDBC Driver (include name if inofficial driver): org.postgresql:postgresql

[lukaseder]

Thank you very much for your suggestion. Indeed, these features are currently missing, although you can access the functionality through simple subtraction between two fields:

field1.sub(field2)

But we should definitely add this to the API as well.

[t9t]

Thanks @lukaseder. Looks like there is an issue with sub related to this as well: #6335 (wrong handling of timestamp-diffs), where it's suggested to use timestampDiff.

[lukaseder]

StackOverflowError... Yeah, you're right.

[lukaseder]

Then, the workaround is to resort to using plain SQL, right now:
https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating

[lukaseder]

I'm splitting this task into two. This task will deal with the TIMESTAMP WITH TIME ZONE type (OffsetDateTime) whereas the new task #6876 will deal with the TIMESTAMP type (LocalDateTime).

The other task is easier to implement as it is already fully supported.

[t9t]

@lukaseder Out of curiosity, is this ticket or #6335 planned to be implemented at some time? Currently I cannot really upgrade JOOQ without re-implementing my usages of DSL.timestampDiff() using plain SQL, which is a bit uncomfortable.

[lukaseder]

@t9t The delay is mostly due a testing issue - all the databases behave very differently when it comes to the TIMESTAMP WITH TIME ZONE data type and it is hard to find the most common denominator here.

But you can easily get the behaviour you want by rolling your own functions using plain SQL. Why is it uncomfortable? You don't have to embed plain SQL in all of your queries. You can easily write your own libraries:

public static Field<BigDecimal> offsetDateTimeDiff(Field<OffsetDateTime> t1, Field<OffsetDateTime> t2) {
    return DSL.field("... ({0}, {1})", BigDecimal.class, t1, t2);
}

[t9t]

Thanks for the snippet. It's uncomfortable for me now because I have a method with the plain SQL in one project, but bumped into this issue in a new project also, so I have to decide to copy the method or extract it to a common place. Not a huge deal though really.

I'm a bit surprised though, because it was always working fine when a Timestamp was used for TIMESTAMP WITH TIME ZONE fields. Then again I only have experience with PostgreSQL and not the other RDMBSes, so I have no insight in the challenges.

[lukaseder]

It's uncomfortable for me now because I have a method with the plain SQL in one project, but bumped into this issue in a new project also, so I have to decide to copy the method or extract it to a common place. Not a huge deal though really.

There are many many missing features and missing support for built-in functions. To us, this is just one of those many (and a very complicated one), even if to you, right now, it is the missing support :-)

I'm a bit surprised though, because it was always working fine when a Timestamp was used for TIMESTAMP WITH TIME ZONE fields.

That means you got very lucky not to have run into any time zone related trouble on either the client and/or the server (i.e. all your machines probably ran on UTC).

Then again I only have experience with PostgreSQL and not the other RDMBSes, so I have no insight in the challenges.

Summary:

• The SQL standard (and JDBC) idea of a TIMESTAMP WITH TIME ZONE is akin to java.time.OffsetDateTime
• The PostgreSQL idea of a TIMESTAMP WITH TIME ZONE is akin to java.time.Instant
• The Oracle idea of a TIMESTAMP WITH TIME ZONE is akin to java.time.ZonedDateTime | java.time.OffsetDateTime (yes, the union type)

There are probably more interpretations. Now, standardising date time arithmetic on these three types is super difficult as you can imagine. What does it mean to add an interval of 1 day to each of these types? In UT and OffsetDateTime, that means 86400 seconds (ignoring leap seconds). In ZonedDateTime, this might mean any of 23h, 24h, 25h and possibly a time zone shift in case we're jumping over daylight savings time (there are probably more possibilities, which I'm right now overlooking). What's the user expectation? Ignore Oracle's zoned date times?

Rather than getting this wrong (and then having to be backwards compatible), I prefer postponing the feature until there's really pressing reasons for it to be added.

Compared to the above risk and effort on the maintainer side, introducing one little plain SQL method that handles your immediate use-case on your side seems manageable :-)

[t9t]

Thank you very much for explaining the challenges in detail, I had no idea it was such a complicated issue. Having a simple method with plain SQL really doesn't sound like such a big deal now that I know what's behind it.. :)

[jasonab]

@lukaseder thanks for that example, although I cannot for the life of me figure out what might replace the "..." in the query. Appreciate any enlightenment there. :-)

[lukaseder]

@jasonab I'm assuming you're referring to this comment here: https://github.com/jOOQ/jOOQ/issues/6723#issuecomment-387321975

I intentionally didn't include that function, because this discussion has turned vendor agnostic. What database are you using?

[jasonab]

@lukaseder I'm using PostgreSQL, my main issue is I'm trying to add milliseconds to a timestamp:

USER.LOCKOUT_TIME.isNotNull().and(condition("{0} + ({1} || 'millisecond')::interval) > CAST(CURRENT_TIMESTAMP AS timestamp WITH time zone", USER.LOCKOUT_TIME, USER.LOCKOUT_DURATION))

I understand this is psql specific, so not easy for jOOQ to support.

[lukaseder]

@jasonab That looks about right, give or take 1-2 missing parentheses. What's missing?

[jasonab]

@lukaseder nothing specifically -- jOOQ actually adds an extra paren at the end (which is why one is missing), but otherwise it works just fine. This is just my use case for this issue (that I was hoping to otherwise simplify). I guess I was hoping that your "..." was more straightforward than mine. :-)

[lukaseder]

jOOQ actually adds an extra paren at the end (which is why one is missing)

Well, jOOQ also adds an extra paren at the beginning ;-)

I guess I was hoping that your "..." was more straightforward than mine. :-)

Nope :) Date time arithmetic is hairy and highly vendor specific. You can have a look at the org.jooq.impl.DateAdd, org.jooq.impl.DateDiff, org.jooq.impl.TimestampDiff, and org.jooq.impl.Expression classees to get a brief idea...