INTERVAL DAY TO SECOND

The data type of INTERVAL DAY TO SECOND stores a time interval in days and seconds. Its format is:

INTERVAL DAY[(days_precision)]TO SECOND[(seconds_precision)

days_precision, optional, is an integer from 0 to 9, the default is 2. seconds_precision, optional, is an integer from 0 to 9, the default is 6. The data type of INTERVAL DAY TO SECOND can store a positive or negative time interval.

An INTERVAL DAY TO SECOND literal value is defined in the following syntax:

INTERVAL '[+|-][d] [h[:m[:s]]]' [DAY[(days_precision)]])[TO HOUR | MINUTE | SECOND[(seconds_precision)]]

Value	Optional	Descroption
+	optional	specifies if the time interval is positive, the default is positive.
-	optional	specifies if the time interval is negative.
d	Not optional	is the number of days.
h	optional	is the number of hours. TO HOUR must be set if days and hours are provided.
m	optional	is the number of minutes. TO MINUTES must be set if days and minutes are provided.
s	optional	is the number of seconds; TO SECOND must be set if days and seconds are provided.
days_precision	optional	is the precision for the days. The default is 2.
seconds_precision	optional	is the precision for the fractional seconds. The default is 6.

The following table shows some examples of day-to-second interval literals.

Literal	Description
INTERVAL '1' DAY	Interval of 1 days.
INTERVAL '1' HOUR	Interval of 1 hours.
INTERVAL '99' MINUTE	Interval of 99 minutes.
INTERVAL '99' SECOND	Interval of 99 seconds.
INTERVAL '1 2' DAY TO HOUR	Interval of 1 days 2 hours.
INTERVAL '1 2:34' DAY TO MINUTE	Interval of 1 days 2 hours 34 minutes.
INTERVAL '1 2:34:56' DAY TO SECOND	Interval of 1 days 2 hours 34 minutes 56 seconds.
INTERVAL '123 4:56:78.12' DAY(3)TO SECOND(2)	Interval of 123 days 4 hours 56 minutes 78.12 seconds.
INTERVAL '1 2:00:45' DAY TO SECOND	Interval of 1 days 2 hours 0 minutes 45 seconds.
INTERVAL '-1 2:34:56' DAY TO SECOND	Negative interval of 1 days 2 hours 34 minutes 56 seconds.

The following INTERVAL literal is invalid because the number of digits in the days exceeds the specified:

INTERVAL '1234 2:25:45' DAY(3) TO SECOND


CREATE TABLE myTable(
id       INTEGER,
duration INTERVAL DAY(3) TO SECOND (4)
);
INSERT INTO myTable(id, duration)VALUES (1, INTERVAL '1' DAY);
INSERT INTO myTable(id, duration)VALUES (2, INTERVAL '1' HOUR);
INSERT INTO myTable(id, duration)VALUES (3, INTERVAL '99' MINUTE);
INSERT INTO myTable(id, duration)VALUES (4, INTERVAL '9' SECOND);
INSERT INTO myTable(id, duration)VALUES (5, INTERVAL '1 2:34' DAY TO MINUTE);
INSERT INTO myTable(id, duration)VALUES (6, INTERVAL '1 2:34:56' DAY TO SECOND);

SQL> SELECT * FROM myTable;

        ID           DURATION
---------------------------------------------------
         1 +001 00:00:00.0000
         2 +000 01:00:00.0000
         3 +000 01:39:00.0000
         4 +000 00:00:09.0000
         5 +001 02:34:00.0000
         6 +001 02:34:56.0000

6 rows selected.

SQL>

Home »
Oracle »
Data types »

Time_intervals: