Implementing date_sub() function with intervals with jOOQ

java, jooq, mysql, sql

Solution

The solution:

Being slightly biased towards the Oracle database, jOOQ implements adding / subtracting day intervals simply by using:

// Java
DSL.currentTimestamp().sub(1);

The above renders:

-- Oracle
sysdate - 1

-- MySQL
date_add(current_timestamp(), interval -1 day)

Of course, you can also access the `date_add()` function directly, if you prefer that:

// Java
DSL.dateAdd(DSL.currentTimestamp(), -1);

Some documentation:

- The manual's section about date time arithmetic

- The manual's section about interval data types

- `Field.add()` Javadoc

- `DSL.dateAdd()` Javadoc

Problem

I've been dealing with this since yestertay. The thing is I am migrating my queries to jOOQ and I got stuck when I tried to implement this part: ``` select * from table where condition1 and date1 >= date_sub(now(), interval 1 days) ``` Specifically this part of the condition: date_sub(now(), interval 1 days) with jOOQ. So my questions are: Which functions should I use from jOOQ to represent date_sub? How do I implement interval X days with jOOQ? To clarify, the dates are of the type Timestamp Thanks in advance!

Original source