Hello, Currently, the only way to handle date-time arithmetic of this kind is by using Field.add(...) to add days to dates in Oracle-style: http://www.jooq.org/javadoc/latest/org/jooq/Field.html#add%28java.lang.Number%29
Or to use the SQL standard EXTRACT() function from the factory to create your own arithmetic http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#extract%28org.jooq.Field,%20org.jooq.DatePart%29 More sophisiticated date-time arithmetic is planned for a future release. However, it is currently not a priority, as the API design is non-trivial for this non-standard extension: https://sourceforge.net/apps/trac/jooq/ticket/585 In the mean time, I suggest, you use "plain SQL" for datediff as documented here: http://www.jooq.org/manual/DSL/SQL/ Something along these lines: function("datediff", Integer.class, val(date1), val(date2)).sub(someinteger) Cheers Lukas 2012/3/5 <[email protected]>: > Hello, > > today i didn't find a way to create this kind of query with jooq (Mysql) : > > "select avg(floor(datediff(date1,date2))-someinteger) from table;" > > date1 & date2 being DATETIME type and someinteger an INTEGER. > > DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date > to the other. expr1 and expr2 are date or date-and-time expressions.
