An interval expression generates a value of the INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND data type.
The syntax of an interval expression 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. These parameters specify the precision of the corresponding elements. If you omit the leading_field_precision parameter when specifying the DAY or YEAR element, the default value of 2 is used, indicating that the value of the element cannot exceed a two-digit integer. If you omit the fractional_second_precision parameter when specifying the SECOND element, the default value of 6 is used, indicating that the value is precise to the sixth decimal place. If the query returns a value with more digits than the default precision, OceanBase Database will return an error.
The following example shows a value of the INTERVAL DAY TO SECOND 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