CODEWITHSUNDEEP
javajooq
jlar310
jlar310

Reputation: 649

jOOQ Query Critera and forcedType converters

Our Informix database stores DATETIME types as local-time, but as we build new code with jOOQ, we are mapping these local-times to UTC timestamps with java.time.Instant.

We have multiple operations across several time zones. Each operation has it's own local database and saved timestamps have always been local-time for that operation. So our jOOQ forcedType for DATETIME columns has a custom converter that knows the time-zone for each individual backend database and converts to the correct UTC timestamp. This all works great for read/write operations on DATETIME types.

However, when we want to apply query criteria to these converted columns, things get more complicated. For example:

jooq.select(TICKET.ID)
    .from(TICKET)
    .where(TICKET.DROPTIME.between(x, y))

Here jOOQ wants x and y to be of type java.time.Instant because that is the type of TICKET.DROPTIME. However, I have to be very careful about my values for x and y because actual comparison in the generated SQL is done with local times.

Example jOOQ generated code:

WHERE(ticket.droptime BETWEEN cast(? AS DATETIME YEAR TO SECOND) AND cast(? AS DATETIME YEAR TO SECOND))

So here, ticket.droptime is local time even though the jOOQ between function demands Instant arguments. But if I drop in Instant.now() for x or y, then my query is totally dependent on the time zone in which the code is running.

The best workaround I can think of is to manually compute an Instant that is offset by the difference between the database time zone and the system time zone of the running code to get query criteria (x and y) as Instant values that will map to the desired local time.

Instant now = Instant.now();
Instant x = ZonedDateTime.ofInstant(now.minus(6, ChronoUnit.HOURS), zoneIdOfLocalDatabase)
    .toLocalDateTime().atZone(ZoneId.systemDefault()).toInstant();
Instant y = ZonedDateTime.ofInstant(now.minus(10, ChronoUnit.MINUTES), zoneIdOfLocalDatabase)
    .toLocalDateTime().atZone(ZoneId.systemDefault()).toInstant();


jooq.select(TICKET.ID)
    .from(TICKET)
    .where(TICKET.DROPTIME.between(x, y)

This should work, but it would be a very easy for a developer to miss this step.

Another option is plain SQL:

jooq.select(TICKET.ID)
    .from(TICKET)
    .where("ticket.droptime between CURRENT - 6 UNITS HOUR AND CURRENT - 10 UNITS MINUTE")

But the whole goal of jOOQ is to avoid raw SQL strings and we are actually planning to migrate to a different database backend some day and don't want to have to hunt these down.

Getting to my question: Is there a more automatic way to query forcedTypes here? Or do I have to manually do zone offset conversion on Instant values to get the proper local timestamp for the query criteria?

Would it even be possible for jOOQ to detect forcedTypes in the where clause and automatically convert the parameters?

Upvotes: 2

Views: 269

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

The reason for this behaviour is that even if you're using an Instant as your user representation of the data type, jOOQ behind the scenes, remembers that it's a LocalDateTime. So, your Converter<T, U> implementation binding to Converter<LocalDateTime, Instant> (or similar) only affects what your Java code can see.

If you want to influence also the generated SQL, you'll have to use a data type binding, instead, which allows you to influence how the bind variable is generated (and whether a CAST is even necessary).

Upvotes: 2

Related Questions