Hi Mark,
Thank you very much for your thorough bug report. I do think this is a bug,
although I'm not sure if we should:
- Generate an integer value instead of an interval
- Change the API entirely, as other dialects will probably have a similar
problem with this particular usage.
A workaround is, as always, to use plain SQL. I would not resort to using
string concatenation, but the templating functionality instead:
abs(field("DATEDIFF(DAY FROM {0} TO {1})", SQLDataType.INTEGER,
previousPostDate, nextPostDate)).lessOrEqual(1)
Another option is to use Field.coerce(SQLDataType.INTEGER) on your datediff
expression:
abs(localDateTimeDiff(previousPostDate,
nextPostDate).coerce(INTEGER)).lessOrEqual(oneDay)
Coerce re-assigns a new jOOQ DataType to a Field expression, without any
impact on the resulting SQL statement.
We'll look into how to fix this thoroughly, but you should be able to
continue working with one of the above workarounds.
Thanks,
Lukas
On Sun, Oct 20, 2019 at 6:28 PM Mark Rotteveel <[email protected]> wrote:
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jooq-user/f91c1cfd-e015-76b4-fe9e-b461d91ea712%40lawinegevaar.nl
> .
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAB4ELO6Ya_BcjXetTt17TVKT5htStw4hpy09ZZH4PnYiCM06Ww%40mail.gmail.com.
