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.