All Products
Search
Document Center

INTERVAL DAY TO SECOND data type

Last Updated: Jun 18, 2021

INTERVAL DAY TO SECOND stores time periods that are measured in days, hours, minutes, and seconds. This data type is useful for storing a value that represents the precise difference between two date and time values.

Syntax

INTERVAL DAY [(precision)] TO SECOND [(fractional_seconds_precision)]

Parameters

Parameter

Value

Description

precision

0~9

The precision of the DAY element. The default value is 2.

fractional_seconds_precision

0~9

The precision of the fractional part of the SECOND element. The default value is 6.

Examples

You can use the following formats when you insert a value of the INTERVAL DAY TO SECOND data type. For more information about how to specify values of interval data types, see Interval literals.

Syntax

Examples

Description

INTERVAL 'dd hh:mm:ss' DAY(precision) TO SECOND(fractional_seconds_precision)

INTERVAL '140 5:12:10.2222222' DAY(3) TO SECOND(7)

The interval is 140 days, 5 hours, 12 minutes, and 10.2222222 seconds.

INTERVAL 'dd hh'DAY(precision) TO HOUR

INTERVAL '400 5' DAY(3) TO HOUR

The interval is 400 days and 5 hours.

INTERVAL 'dd hh:mm'DAY(precision) TO MINUTE

INTERVAL '4 5:12' DAY TO MINUTE

The interval is four days, 5 hours, and 12 minutes.

INTERVAL 'hh:mm' HOUR TO MINUTE

INTERVAL '11:20' HOUR TO MINUTE

The interval is 11 hours and 20 minutes.

INTERVAL 'hh:mm:ss' HOUR TO SECOND(fractional_seconds_precision)

INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)

The interval is 11 hours, 12 minutes, and 10.2222222 seconds.

INTERVAL 'dd' DAY(precision)

INTERVAL '14' DAY

The interval is 14 days.

INTERVAL 'hh' HOUR

INTERVAL '160' HOUR

The interval is 160 hours.

INTERVAL 'mm' MINUTE

INTERVAL '14' MINUTE

The interval is 14 minutes.

INTERVAL 'ss' SECOND(fractional_seconds_precision)

INTERVAL '14.666' SECOND(2, 3)

The interval is 14.666 seconds.

The following code can be used to create two INTERVAL DAY TO SECOND columns named interval1 and interval2 in the Interval_Sample table and insert values into these columns.

CREATE TABLE Interval_Sample (interval1 INTERVAL DAY TO SECOND, interval2 INTERVAL DAY(3) TO SECOND(3));
INSERT INTO Interval_Sample (interval1, interval2) VALUES ( INTERVAL '15 06:10:08' DAY TO SECOND, INTERVAL '150 06:10:08' DAY(3) TO SECOND(3));
SELECT * FROM Interval_Sample;

The following result is returned:

+---------------------+-------------------+
| interval1           | interval2         |
+---------------------+-------------------+
| +15 06:10:08.000000 | +150 06:10:08.000 |
+---------------------+-------------------+

Calculations on interval data types and other date and time types

ApsaraDB for OceanBase supports the conversion between data types. Therefore, mathematical operations on interval data types and other date and time data types are supported. However, ApsaraDB for OceanBase does not support all addition, subtraction, multiplication, and division operations on two random data types. For more information about the matrix diagram that lists the supported calculations on DATE values, see Calculations on DATE and INTERVAL values. For more information about data type conversion, see Data type conversion.

For more information about examples of calculations on interval data types and other data types, see INTERVAL YEAR TO MONTH data type.