An interval literal specifies the value of a period of time. ApsaraDB for OceanBase supports the following two types of interval literals:
INTERVAL YEAR TO MONTH
INTERVA DAY TO SECOND
Leading and trailing fields
Each type of interval literal contains a leading field and an optional trailing field. The leading field defines the basic unit of measured date or time. The trailing field defines the smallest increment of the considered basic unit. For example, DAY TO MINUTE is used to specify an interval literal whose minimum unit is month. The leading field is
YEAR and the trailing field is
MINUTE. The trailing field is optional. When you specify the interval literal, the trailing field can be omitted.
Interval literals have the following fields:
SECOND. The weights of these fields are in descending order. The weight of the
YEAR field is the largest. If you need to specify a trailing field, the weight of the trailing field in the literal must be 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 number of digits in the value of the leading field ranges from 0 to 9. The default value is 2. The
SECOND field specifies the number of seconds. This field value can be accurate to at most nine decimal places and at least zero decimal place. The default precision is six decimal places. When a field value exceeds the specified range, the database returns an error. If the number of decimal places of the
SECOND field exceeds the specified precision, the field value is rounded to the value that is a specified precision.
INTERVAL YEAR TO MONTH literals
INTERVAL YEAR TO MONTH literal specifies a period of time in the unit of year and month.
The following examples show you some
INTERVAL YEAR TO MONTH literals:
INTERVAL '265-2' YEAR(3) TO MONTH
An interval of 265 years and two months. The precision of the leading field
INTERVAL '265' YEAR(3)
An interval of 265 years.
INTERVAL '500' MONTH(3)
An interval of 500 months or an interval of 41 years and eight months.
INTERVAL '10' MONTH
An interval of 10 months.
INTERVAL '123' YEAR
Returns an error. The value 123 exceeds the default precision: two digits.
You can add one
INTERVAL YEAR TO MONTH literal to another
INTERVAL YEAR TO MONTH literal or subtract one INTERVAL YEAR TO MONTH literal from another INTERVAL YEAR TO MONTH literal. For example:
INTERVAL '6-2' YEAR TO MONTH plus
INTERVAL'21' MONTH equals to
INTERVAL '7-11' YEAR TO MONTH.
INTERVAL DAY TO SECOND literals
INTERVAL DAY TO SECOND literal specifies a period of time for which the day and the specific time are used as the unit.
The following examples show some
INTERVAL DAY TO SECOND literals:
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
An interval of four days, 5 hours, 12 minutes, and 10.222 seconds.
The default decimal point precision of the
INTERVAL '4 5:12' DAY TO MINUTE
An interval of four days, 5 hours, and 12 minutes.
INTERVAL '400 5' DAY(3) TO HOUR
An interval of 400 days and 5 hours. The precision of the leading field
INTERVAL '400' DAY(3)
An interval of 400 days.
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)
An interval of 11 hours, 12 minutes, and 10.2222222 seconds.The precision for the value of the
INTERVAL '11:20' HOUR TO MINUTE
An interval of 11 hours and 20 minutes.
INTERVAL '10' HOUR
An interval of 10 hours.
INTERVAL '10:22' MINUTE TO SECOND
An interval of 10 minutes and 22 seconds.
INTERVAL '10' MINUTE
An interval of 10 minutes.
INTERVAL '4' DAY
An interval of four days.
INTERVAL '25' HOUR
Indicates an interval of 25 hours.
INTERVAL '40' MINUTE
An interval of 40 minutes.
INTERVAL '120' HOUR(3)
An interval of 120 hours.
INTERVAL '30.12345' SECOND(2,4)
An interval of 30.1235 seconds. The number of decimal places of the SECOND value exceeds the specified precision. Therefore, this value is rounded to the fourth decimal place.
You can perform an addition or subtraction operation on an
INTERVAL DAY TO SECOND literal and another
INTERVAL DAY TO SECOND literal. For example,
INTERVAL'20' DAY minus
INTERVAL'239' HOUR is equal to
INTERVAL'10-1' DAY TO SECOND.