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 and trailing fields
Each interval literal contains a leading field and an optional trailing field. The leading field defines the basic unit of measurement for the date or time, and the trailing field defines the smallest increment of the basic unit.
For example, the DAY TO SECOND interval literal specifies the smallest unit to the day. The leading field is DAY, and the trailing field is SECOND. The trailing field is optional and can be omitted when specifying an interval literal.
The fields in an interval literal are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. Their weights decrease in order from YEAR to SECOND. When specifying a trailing field, its weight must be less than that of the leading field; otherwise, the specification is invalid. For example, INTERVAL '1-2' DAY TO YEAR is an invalid literal.
The number of digits for the leading field ranges from 0 to 9, with a default value of 2. The SECOND field specifies the number of seconds, with a maximum precision of 9 decimal places and a minimum of 0 decimal places. The default precision is 6 decimal places. If the value of a field exceeds the specified range, the database returns an error. If the number of decimal places in the SECOND field exceeds the specified precision, it will be rounded to a value that matches the specified precision.
INTERVAL YEAR TO MONTH literals
The INTERVAL YEAR TO MONTH literal is used to specify a period of time in years and months.
Here are some examples of INTERVAL YEAR TO MONTH literals:
| Example | Description |
|---|---|
| INTERVAL '265-2' YEAR(3) TO MONTH | Represents an interval of 265 years and 2 months. The precision of the leading field YEAR is greater than the default of 2 digits, so it must be specified with a precision value that matches the number of digits in the value. |
| INTERVAL '265' YEAR(3) | Represents an interval of 265 years. |
| INTERVAL '500' MONTH(3) | Represents an interval of 500 months or 41 years and 8 months. |
| INTERVAL '10' MONTH | Represents an interval of 10 months. |
| INTERVAL '123' YEAR | Returns an error because the value 123 exceeds the default precision of 2 digits. |
You can add or subtract another INTERVAL YEAR TO MONTH literal from an 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 specific time units.
The following table provides some examples of INTERVAL DAY TO SECOND literals.
| Example | Description |
|---|---|
| INTERVAL '4 5:12:10.222' DAY TO SECOND(3) | Represents an interval of 4 days, 5 hours, 12 minutes, and 10.222 seconds. The default precision for the SECOND field is 6, so if you do not manually specify a precision of 3, the result will be padded with zeros. |
| INTERVAL '4 5:12' DAY TO MINUTE | Represents an interval of 4 days, 5 hours, and 12 minutes. |
| INTERVAL '400 5' DAY(3) TO HOUR | Represents an interval of 400 days and 5 hours. The leading DAY field exceeds the default precision of 2, so it is manually specified as 3. |
| INTERVAL '400' DAY(3) | Represents an interval of 400 days. |
| INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) | Represents an interval of 11 hours, 12 minutes, and 10.2222222 seconds. The value of the SECOND field exceeds the default precision of 6, so it is manually specified to match the value. |
| INTERVAL '11:20' HOUR TO MINUTE | Represents an interval of 11 hours and 20 minutes. |
| INTERVAL '10' HOUR | Represents an interval of 10 hours |
| INTERVAL '10:22' MINUTE TO SECOND | Represents an interval of 10 minutes and 22 seconds. |
| INTERVAL '10' MINUTE | Represents an interval of 10 minutes. |
| INTERVAL '4' DAY | Represents an interval of 4 days. |
| INTERVAL '25' HOUR | Represents an interval of 25 hours. |
| INTERVAL '40' MINUTE | Represents an interval of 40 minutes. |
| INTERVAL '120' HOUR(3) | Represents an interval of 120 hours. |
| INTERVAL '30.12345' SECOND(2,4) | Represents an interval of 30.1235 seconds. The number of decimal places in the seconds field exceeds the specified precision, so it is rounded to the fourth decimal place. |
You can add or subtract another INTERVAL DAY TO SECOND literal from a INTERVAL DAY TO SECOND literal. For example: INTERVAL'20' DAY - INTERVAL'239' HOUR = INTERVAL'10-1' DAY TO SECOND.