Arithmetic expressions

Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition

Numeric arithmetic expressions

Your database can do the math for you. Arithmetic operations are implemented just like numeric functions, with similar limitations as far as type restrictions are concerned. You can use any of these operators:

  +  -  *  /  %

In order to express a SQL query like this one:

SELECT ((1 + 2) * (5 - 3) / 2) % 10 FROM DUAL

You can write something like this in jOOQ:

create.select(val(1).add(2).mul(val(5).sub(3)).div(2).mod(10);

Datetime arithmetic expressions

jOOQ also supports the Oracle-style syntax for adding days to a Field<? extends java.util.Date>

SELECT SYSDATE + 3 FROM DUAL;

create.select(currentTimestamp().add(3));

For more advanced datetime arithmetic, use the Factory's timestampDiff() and dateDiff() functions, as well as jOOQ's built-in SQL standard INTERVAL data type support: