The INTERVAL DAY TO SECOND data type stores a period of time in days, hours, minutes, and seconds. This data type is used to represent the exact difference between two datetime values. This topic describes the syntax, parameters, and usage notes of the INTERVAL DAY TO SECOND data type.
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. |
Considerations
The following table lists the supported formats for inserting values of the INTERVAL DAY TO SECOND data type. For more information about how to specify values of the interval data type, 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) | 140 days 5 hours 12 minutes 10.2222222 seconds. |
| INTERVAL 'dd hh'DAY(precision) TO HOUR | INTERVAL '400 5' DAY(3) TO HOUR | 400 days 5 hours. |
| INTERVAL 'dd hh:mm'DAY(precision) TO MINUTE | INTERVAL '4 5:12' DAY TO MINUTE | 4 days 5 hours 12 minutes. |
| INTERVAL 'hh:mm' HOUR TO MINUTE | INTERVAL '11:20' HOUR TO MINUTE | 11 hours 20 minutes. |
| INTERVAL 'hh:mm:ss' HOUR TO SECOND(fractional_seconds_precision) | INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) | 11 hours 12 minutes 10.2222222 seconds. |
| INTERVAL 'dd' DAY(precision) | INTERVAL '14' DAY | 14 days. |
| INTERVAL 'hh' HOUR | INTERVAL '160' HOUR | 160 hours. |
| INTERVAL 'mm' MINUTE | INTERVAL '14' MINUTE | 14 minutes. |
| INTERVAL 'ss' SECOND(fractional_seconds_precision) | INTERVAL '14.666' SECOND(2, 3) | 14.666 seconds. |
Calculations with the INTERVAL DAY TO SECOND data type and other date types
OceanBase Database supports data type conversion, so values of the INTERVAL DAY TO SECOND data type can be used in mathematical operations with other date values. However, the database does not support arbitrary addition, subtraction, multiplication, or division between different data types.
For more information about the supported date type calculation matrix and data type conversion, see Calculations with DATE and INTERVAL data types and Data type conversion rules.
For more information about calculations with the INTERVAL DAY TO SECOND data type and other data types, see INTERVAL YEAR TO MONTH data type.
Examples
Create two columns of the INTERVAL DAY TO SECOND data type 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));
obclient> SELECT * FROM Interval_Sample;
+---------------------+-------------------+
| INTERVAL1 | INTERVAL2 |
+---------------------+-------------------+
| +15 06:10:08.000000 | +150 06:10:08.000 |
+---------------------+-------------------+
1 row in set