INTERVAL data types

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

jOOQ fills a gap opened by JDBC, which neglects an important SQL data type as defined by the SQL standards: INTERVAL types. SQL knows two different types of intervals:

• YEAR TO MONTH: This interval type models a number of months and years
• DAY TO SECOND: This interval type models a number of days, hours, minutes, seconds and milliseconds

Both interval types ship with a variant of subtypes, such as DAY TO HOUR, HOUR TO SECOND, etc. jOOQ models these types as Java objects extending java.lang.Number: org.jooq.types.YearToMonth (where Number.intValue() corresponds to the absolute number of months) and org.jooq.types.DayToSecond (where Number.intValue() corresponds to the absolute number of milliseconds)

Interval arithmetic

In addition to the arithmetic expressions documented previously, interval arithmetic is also supported by jOOQ. Essentially, the following operations are supported:

• DATETIME - DATETIME => INTERVAL
• DATETIME + or - INTERVAL => DATETIME
• INTERVAL + DATETIME => DATETIME
• INTERVAL + - INTERVAL => INTERVAL
• INTERVAL * or / NUMERIC => INTERVAL
• NUMERIC * INTERVAL => INTERVAL