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.