An interval literal specifies a period of time. OceanBase Database supports two types of interval literals: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.
Leading field and trailing field
Each type of interval literals contains a leading field and may contain a trailing field. The leading field defines the basic unit of the date or time that is being measured. The trailing field defines the minimum increment of the basic unit.
For example, DAY TO SECOND specifies an interval literal with a basic unit of days, where the leading field is DAY and the trailing field is SECOND. The trailing field is optional and can be omitted when you specify an interval literal.
Fields available in the interval literals include YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. The weight of these fields decreases from YEAR. When you specify the trailing field, make sure that the weight of the trailing field is lower than that of the leading field. Otherwise, the specified trailing field is invalid. For example, INTERVAL '1-2' DAY TO YEAR is an invalid literal.
The value range of the number of digits of the leading field value is [0,9]. The default value is 2. The SECOND field specifies the number of seconds. The value can be accurate to 0 to 9 decimal places after the decimal point. By default, the value is accurate to 6 decimal places after the decimal point. If the value of this field exceeds the precision range, an error is returned. If the number of decimal digits in the SECOND field value exceeds the specified precision range, the field value will be rounded to the specified precision.
INTERVAL YEAR TO MONTH literal
The INTERVAL YEAR TO MONTH literal specifies a period of time in years and months.
The following table lists some INTERVAL YEAR TO MONTH literals.
| Example | Description |
|---|---|
| INTERVAL '265-2' YEAR(3) TO MONTH | Indicates an interval of 265 years and 2 months. The value of the YEAR leading field exceeds the default precision 2. In this example, the precision must be specified as 3. |
| INTERVAL '265' YEAR(3) | Indicates an interval of 265 years. |
| INTERVAL '500' MONTH(3) | Indicates an interval of 500 months or 41 years and 8 months. |
| INTERVAL '10' MONTH | Indicates an interval of 10 months. |
| INTERVAL '123' YEAR | An error is returned, because the field value 123 exceeds the default precision 2. |
You can add an INTERVAL YEAR TO MONTH literal to or subtract it from another INTERVAL YEAR TO MONTH literal. For example, INTERVAL '6-2' YEAR TO MONTH + INTERVAL'21' MONTH = INTERVAL '7-11' YEAR TO MONTH.
INTERVAL DAY TO SECOND literal
The INTERVAL DAY TO SECOND literal specifies a period of time in days and seconds.
The following table lists some INTERVAL DAY TO SECOND literals.
| Example | Description |
|---|---|
| INTERVAL '4 5:12:10.222' DAY TO SECOND(3) | Indicates an interval of 4 days, 5 hours, 12 minutes, and 10.222 seconds. The default precision of the SECOND field value is 6 decimal places after the decimal point. In this example, the precision is manually specified as 3 decimal places after the decimal point. Otherwise, 0 is added after the decimal in the returned result to meet the requirement of 6 decimal places. |
| INTERVAL '4 5:12' DAY TO MINUTE | Indicates an interval of 4 days, 5 hours, and 12 minutes. |
| INTERVAL '400 5' DAY(3) TO HOUR | Indicates an interval of 400 days and 5 hours. The value of the leading field DAY exceeds the default precision 2. In this example, the precision is manually specified as 3. |
| INTERVAL '400' DAY(3) | Indicates an interval of 400 days. |
| INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) | Indicates an interval of 11 hours, 12 minutes, and 10.2222222 seconds. The value of the leading field SECOND exceeds the default precision of 6 decimal places after the decimal point. In this example, the precision is manually specified as 7 decimal places after the decimal point. |
| INTERVAL '11:20' HOUR TO MINUTE | Indicates an interval of 11 hours and 20 minutes. |
| INTERVAL '10' HOUR | Indicates an interval of 10 hours. |
| INTERVAL '10:22' MINUTE TO SECOND | Indicates an interval of 10 minutes and 22 seconds. |
| INTERVAL '10' MINUTE | Indicates an interval of 10 minutes. |
| INTERVAL '4' DAY | Indicates an interval of 4 days. |
| INTERVAL '25' HOUR | Indicates an interval of 25 hours. |
| INTERVAL '40' MINUTE | Indicates an interval of 40 minutes. |
| INTERVAL '120' HOUR(3) | Indicates an interval of 120 hours. |
| INTERVAL '30.12345' SECOND(2,4) | Indicates an interval of 30.1235 seconds. The number of decimal places after the decimal point in SECOND exceeds the default precision and the field value is rounded to the fourth place after the decimal point. |
You can add an INTERVAL DAY TO SECOND literal to or subtract it from another INTERVAL DAY TO SECOND literal. For example, INTERVAL'20' DAY - INTERVAL'239' HOUR = INTERVAL'10-1' DAY TO SECOND.