All Products
Search
Document Center

Interval literals

Last Updated: Jun 18, 2021

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: YEAR, MONTH, DAY, HOUR, MINUTE, and 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

The 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:

Example

Description

INTERVAL '265-2' YEAR(3) TO MONTH

An interval of 265 years and two months. The precision of the leading field YEAR is greater than the default precision: two digits. The specified precision must match the number of digits of the value.

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

The 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:

Example

Description

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 SECOND field is 6. If you do not manually set the precision to 3, the system pads the decimal places of the returned result with zeros.

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 DAY exceeds the default precision: two digits. Manually specify the precision as 3.

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 SECOND field value exceeds the default precision: six digits. The precision that matches the value is manually specified here.

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.