| The jOOQ User Manual. Multiple Pages : SQL building : Column expressions : Arithmetic expressions | previous : next |
All versions: 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.13 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2 | 2.6
Arithmetic expressions
Available in ✅ 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:
- INTERVAL YEAR TO MONTH: org.jooq.types.YearToMonth
- INTERVAL DAY TO SECOND: org.jooq.types.DayToSecond
