All Products
Document Center

Interval expressions

Last Updated: Jun 18, 2021

An interval expression generates a value of the INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND data type.

The following syntax is provided:

   { DAY [ (leading_field_precision) ] TO
     SECOND [ (fractional_second_precision) ]
   | YEAR [ (leading_field_precision) ] TO
leading_field_precision and fractional_second_precision can be integers from 0 to 9. The two parameters specify the precisions of the corresponding element values. If you omit the leading_field_precision parameter when you specify the DAY and YEAR elements, the used default value is 2. The value 2 indicates that the value of the element cannot be an integer that has more than two digits. If you omit the following parameter for the SECOND element:

fractional_second_ precision, the default value is 6. The value 6 indicates that the element value is accurate to the sixth decimal place. If the return value of a query has more digits than the default precision, ApsaraDB for OceanBase returns an error.

For example, the following statement shows an INTERVAL value of the DAY TO SECOND type.

select INTERVAL '999999999 23:59:59.999' day(9) to second from dual;