The INTERVAL DAY TO SECOND data type stores periods in days, hours, minutes, and seconds. This data type can represent the precise difference between two datetime values. This topic describes the syntax, parameters, and considerations of the INTERVAL DAY TO SECOND data type.
Syntax
INTERVAL DAY [(precision)] TO SECOND [(fractional_seconds_precision)]
Parameters
Parameter |
Value range |
Description |
|---|---|---|
| precision | [0,9] | The precision of the DAY element. Default value: 2. |
| fractional_seconds_precision | [0,9] | The precision of the fractional seconds of the SECOND element. Default value: 6, which indicates that the value is accurate to microseconds. |
Considerations
The following table lists supported formats that you can use when you insert values of the INTERVAL DAY TO SECOND data type. For more information about how to specify values of interval data types, see Interval literals.
Syntax |
Example |
Description |
|---|---|---|
| INTERVAL 'dd hh:mm:ss' DAY(precision) TO SECOND(fractional_seconds_precision) | INTERVAL '140 5:12:10.2222222' DAY(3) TO SECOND(7) | Indicates an interval of 140 days, 5 hours, 12 minutes, and 10.2222222 seconds. |
| INTERVAL 'dd hh'DAY(precision) TO HOUR | INTERVAL '400 5' DAY(3) TO HOUR | Indicates an interval of 400 days and 5 hours. |
| INTERVAL 'dd hh:mm'DAY(precision) TO MINUTE | INTERVAL '4 5:12' DAY TO MINUTE | Indicates an interval of 4 days, 5 hours, and 12 minutes. |
| INTERVAL 'hh:mm' HOUR TO MINUTE | INTERVAL '11:20' HOUR TO MINUTE | Indicates an interval of 11 hours and 20 minutes. |
| INTERVAL 'hh:mm:ss' HOUR TO SECOND(fractional_seconds_precision) | INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) | Indicates an interval of 11 hours, 12 minutes, and 10.2222222 seconds. |
| INTERVAL 'dd' DAY(precision) | INTERVAL '14' DAY | Indicates an interval of 14 days. |
| INTERVAL 'hh' HOUR | INTERVAL '160' HOUR | Indicates an interval of 160 hours. |
| INTERVAL 'mm' MINUTE | INTERVAL '14' MINUTE | Indicates an interval of 14 minutes. |
| INTERVAL 'ss' SECOND(fractional_seconds_precision) | INTERVAL '14.666' SECOND(2, 3) | Indicates an interval of 14.666 seconds. |
Calculation of intervals and other date types
OceanBase Database supports conversion between data types. Therefore, you can perform mathematical operations on interval values and other date values. However, OceanBase Database does not support any addition, subtraction, multiplication, and division operation on values of different data types.
For more information about the calculation matrix and conversion of the supported data types, see Calculation of dates, timestamps, and intervals and Data type conversion rules.
For more information about the calculation of intervals and other data types, see INTERVAL YEAR TO MONTH data type.
Examples
Create two columns interval1 and interval2 of the INTERVAL DAY TO SECOND data type in the Interval_Sample table and insert values into the two 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));
obclient> SELECT * FROM Interval_Sample;
+---------------------+-------------------+
| INTERVAL1 | INTERVAL2 |
+---------------------+-------------------+
| +15 06:10:08.000000 | +150 06:10:08.000 |
+---------------------+-------------------+
1 row in set