I ran into a problem with dateDiff on Firebird with jOOQ 3.12.1, and I'm not sure if it is not supported on Firebird, or if I'm missing some option.

I'm trying to generate a query with a condition that is the equivalent of:

ABS(DATEDIFF(DAY FROM a.PREVIOUS_POST_DATE TO a.NEXT_POST_DATE)) <= 1

However using

var oneDay = DayToSecond.valueOf(Duration.ofDays(1))
var previousPostDate =
  linkInfoDates.field("PREVIOUS_POST_DATE", SQLDataType.LOCALDATETIME);
var nextPostDate =
  linkInfoDates.field("NEXT_POST_DATE", SQLDataType.LOCALDATETIME);

and (as fragment of a larger query):

abs(localDateTimeDiff(previousPostDate,
  nextPostDate)).lessOrEqual(oneDay)

produces SQL like:

abs(datediff(millisecond, "a"."NEXT_POST_DATE",
  "a"."PREVIOUS_POST_DATE")) <= '+1 00:00:00.000000000'

The problem is that Firebird has no interval literal, so the right hand side of the comparison ('+1 00:00:00.000000000') is not valid and produces an error:

java.sql.SQLException: conversion error from string "+1 00:00:00.000000000" [SQLState:22018, ISC error code:335544334]

Is there an option I'm missing, or should I resort to using an escape to plain SQL:

abs(field("DATEDIFF(DAY FROM " + previousPostDate + " TO " + nextPostDate + ")", SQLDataType.INTEGER)).lessOrEqual(1)

Mark
--
Mark Rotteveel

--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jooq-user/f91c1cfd-e015-76b4-fe9e-b461d91ea712%40lawinegevaar.nl.

Reply via email to