Available in versions: Dev (3.16) | Latest (3.15) | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | 3.7

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

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

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo