Oracle Database INTERVAL Day to second type usage

The INTERVAL day to second type can be used to store the time interval in days and seconds of the unit. The following statement creates a table named promotions that stores promotional information. The Promotions table contains a column duration of type interval day to second, which is used to record the time interval at which the promotion is valid:

CREATE TABLE Promotions ( promotion_id INTEGER CONSTRAINT promotions_pk PRIMARY KEY, Name VARCHAR2 (+) is not NULL, Duration INTERVAL Day (3) to SECOND (4) );

Note that this specifies that the Duration column has a precision of 3, and the Seconds of the decimal part precision is 4. This means that you can store 3 digits for the day of the column, and a maximum of 4 digits for the second of the column to the right of the decimal point.

To provide a interval day to second literal value to the database, you can use the following simplified syntax:

INTERVAL ' [+|-][d] [h[: m[: S]] "[day[(Days_precision)]]) [To HOUR | MINUTE | second[(seconds_precision)]

which

+ or-is an optional indicator that indicates whether the interval is positive or negative (the default is a positive number).

D is the number of days in a time interval.

H is an optional parameter that represents the number of hours in a time interval. If you specify days and hours, you must include to HOUR in the interval clause.

H is an optional parameter that represents the number of minutes in a time interval. If you specify days and minutes, you must include to MINUTES in the interval clause.

S is an optional parameter that represents the number of seconds in a time interval. If you specify days and seconds, you must include to SECOND in the interval clause.

Days_precision is an optional parameter that describes the precision of the number of days (the default value is 2).

Seconds_precision is an optional parameter that describes the precision of the second (the default value is 6).

Table 5-12 shows an example of the time interval literal for several interval day-to-second types.

Table 5-12 Examples of time interval literals

Time interval literal	Description
INTERVAL ' 3 ' Day	Time interval is 3 days
INTERVAL ' 2 ' HOUR	Time interval is 2 hours
INTERVAL ' MINUTE	Time interval is 25 minutes
INTERVAL ' SECOND	Time interval is 45 seconds
INTERVAL ' 3 2 ' Day to HOUR	Time interval is 3 days 2 hours
INTERVAL ' 3 2:25 ' Day to MINUTE	Time interval is 3 days, 2 hours, 25 minutes.
INTERVAL ' 3 2:25:45 ' Day to SECOND	Time interval is 3 days, 2 hours, 25 minutes, 45 seconds.
INTERVAL ' 123 2:25:45.12 ' Day (3) To SECOND (2)	The interval is 123 days, 2 hours, 25 minutes, 45.12 seconds; the precision of the day is 3 digits, and the precision of the seconds is 2 digits.
INTERVAL ' 3 2:00:45 ' Day to SECOND	Time interval is 3 days, 2 hours, 0 minutes, 45 seconds.
INTERVAL '-3 2:25:45 ' Day to SECOND	The time interval is negative, and the value is 3 days, 2 hours, 25 minutes, 45 seconds.
INTERVAL ' 1234 2:25:45 ' Day (3) To SECOND	The time interval is invalid because the number of days exceeds the specified precision 3
INTERVAL ' 123 2:25:45.123 ' Day To SECOND (2)	The time interval is invalid because the number of bits in seconds exceeds the specified precision 2

The following INSERT statement adds a row of records to the promotions table:

INSERT into Promotions (promotion_id, name, duration) VALUES (1, ' 10% off Z Files ', INTERVAL ' 3 ' Day); INSERT into Promotions (promotion_id, name, duration) VALUES (2, ' 20% off Pop 3 ', INTERVAL ' 2 ' HOUR); INSERT into Promotions (promotion_id, name, duration) VALUES (3, ' 30% off modern science ', INTERVAL ' MINUTE); INSERT into Promotions (promotion_id, name, duration) VALUES (4, ' 20% off Tank War ', INTERVAL ' SECOND); INSERT into Promotions (promotion_id, name, duration) VALUES (5, ' 10% off chemistry ', INTERVAL ' 3 2:25 ' Day to MINUTE); INSERT into Promotions (promotion_id, name, duration) VALUES (6, ' 20% off Creative Yell ', INTERVAL ' 3 2:25:45 ' Day to SECOND); INSERT into Promotions (promotion_id, name, duration) VALUES (7, ' 15% off My Front line ', INTERVAL ' 123 2:25:45.12 ' Day (3) to SECOND (2));

The following query retrieves the promotions table and notes the formatting of the Duration column values:

SELECT * from promotions; promotion_id NAME DURATION ------------ ------------------------------   ------------------ 1 10% off Z Files +003 00:00:00.0000 2 20% off Pop 3 +000 02:00:00.0000 3 30% off modern science +000 00:25:00.0000 4 20% off Tank War +000 00:00:45.0000 5 10% off Chemistry +003 02:25:00.0000 6 20% off Creative Yell +003 02:25:45.0000 7 15% off My Front line +123 02:25:45.1200

Oracle Database INTERVAL Day to second type usage