An interval expression yields values of the INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND data type.
The syntax of interval expressions is as follows:
INTERVAL string
{ DAY [ (leading_field_precision) ] TO
SECOND [ (fractional_second_precision) ]
| YEAR [ (leading_field_precision) ] TO
MONTH
}
leading_field_precision and fractional_second_precision can be any integer from 0 to 9. The two parameters specify the precision of the corresponding element. If you omit the leading_field_precision parameter when you specify the DAY and YEAR elements, the database uses the default value of 2, which means that the value of the element cannot be greater than 2 digits. If you omit the fractional_second_precision parameter when you specify the SECOND element, the database uses the default value of 6, which means the value is accurate to 6 decimal places. OceanBase Database returns an error if the value returned by a query contains more digits than the default precision.
The following example shows a value of the INTERVAL DAY TO SECOND data type:
obclient> SELECT INTERVAL '999999999 23:59:59.999' day(9) to second FROM DUAL;
+-----------------------------------------------+
| INTERVAL'99999999923:59:59.999'DAY(9)TOSECOND |
+-----------------------------------------------+
| +999999999 23:59:59.999000 |
+-----------------------------------------------+
1 row in set