Interval data types

Using the interval data types you can define time periods that are especially useful for datetime arithmetic.

Data type Range Note

INTERVAL YEAR(p) TO MONTH

'-999999999-11' to '999999999-11'

For example: '5-3'

p = leading field precision (optional)

1 ≤ p ≤ 9, default = 2

The leading field precision p is given as the maximal number of decimal digits for the number of years.

INTERVAL DAY(lfp) TO SECOND(fsp)

'-999999999 23:59:59.999' to '999999999 23:59:59.999'

For example: '2 12:50:10.123'

lfp = leading field precision (optional)

1 ≤ lfp ≤ 9, default = 2

The leading field precision lfp is given as the maximal number of decimal digits for the number of days.

fsp = fractional second precision (optional)

0 ≤ fsp ≤ 9, default = 3

The accuracy is limited to milliseconds.

The fractional seconds precision fsp is given as the number of decimals used to store fractional seconds. For example, 3 means millisecond precision.