Digest for jooq-user@googlegroups.com - 1 update in 1 topic

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 1 update in 1 topic

http://groups.google.com/group/jooq-user/topics mailing list
Mark Rotteveel <[hidden email]>: Oct 20 06:28PM +0200

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 digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].