Oracle Database 10g SQL (Osborne ORACLE Press Series)

The Oracle9i database introduced data types that allow you to store time intervals . Examples of time intervals include

• 1 year 3 months

• 25 months

• “3 days 5 hours 16 minutes

• 1 day 7 hours

• “56 hours

  Note	Time intervals are not to be confused with datetimes or timestamps. A datetime or timestamp records a specific date and time (7:32:16 P.M. on October 28, 2006, for example). A time interval records a length of time (1 year 3 months, for example).

In the store example, you might want to offer limited time discounts on products. For example, you might want to allow customers to use a coupon that is valid for a few months, or a special promotion discount that is valid for a few days. You ll see examples that feature coupons and promotions later in this section.

Table 4-8 shows the interval types.

You ll learn how to use the time interval types in the following sections.

Using the INTERVAL YEAR TO MONTH Type

You use the INTERVAL YEAR TO MONTH type to store time intervals measured in years and months. The following statement creates a table named coupons that stores coupon information. The coupons table contains an INTERVAL YEAR TO MONTH column named duration to record the interval of time for which the coupon is valid:

CREATE TABLE coupons ( coupon_id INTEGER CONSTRAINT coupons_pk PRIMARY KEY, name VARCHAR2(30) NOT NULL, duration INTERVAL YEAR(3) TO MONTH );

Notice I ve provided a precision of 3 for the duration column. This means up to three digits may be stored for the year part of the interval.

To supply an INTERVAL YEAR TO MONTH literal value to the database, you use the following simplified syntax:

INTERVAL '[+-][y][-m]' [YEAR[( years_precision )])] [TO MONTH]

where

• + or “ is an optional indicator that specifies whether the time interval is positive or negative (default is positive).

• y is the optional number of years for the interval.

• m is the optional number of months for the interval. If you supply years and months, you must include TO MONTH in your literal.

• years_precision is the optional precision for the years (default is 2).

The following table shows some examples of year to month interval literals.

The following INSERT statements add rows to the coupons table with the duration column set to some of the valid intervals shown in the previous table:

INSERT INTO coupons (coupon_id, name, duration) VALUES (1, ' off Z Files', INTERVAL '1' YEAR); INSERT INTO coupons (coupon_id, name, duration) VALUES (2, ' off Pop 3', INTERVAL '11' MONTH); INSERT INTO coupons (coupon_id, name, duration) VALUES (3, ' off Modern Science', INTERVAL '14' MONTH); INSERT INTO coupons (coupon_id, name, duration) VALUES (4, ' off Tank War', INTERVAL '1-3' YEAR TO MONTH); INSERT INTO coupons (coupon_id, name, duration) VALUES (5, ' off Chemistry', INTERVAL '0-5' YEAR TO MONTH); INSERT INTO coupons (coupon_id, name, duration) VALUES (6, ' off Creative Yell', INTERVAL '123' YEAR(3));

If you try to add a row with the duration column set to the invalid interval of INTERVAL ˜1234 YEAR(3), you ll get an error because the precision of the duration column is 3 and is therefore too small. The following INSERT shows the error:

SQL> INSERT INTO coupons (coupon_id, name, duration) 2 VALUES (7, ' off Z Files', INTERVAL '1234' YEAR(3)); VALUES (7, ' off Z Files', INTERVAL '1234' YEAR(3)) * ERROR at line 2: ORA-01873: the leading precision of the interval is too small

The following query retrieves the rows from the coupons table so you can see the formatting of the duration column values:

SELECT * FROM coupons; COUPON_ID NAME DURATION ---------- ------------------------------ -------- 1 off Z Files +001-00 2 off Pop 3 +000-11 3 off Modern Science +001-02 4 off Tank War +001-03 5 off Chemistry +000-05 6 off Creative Yell +123-00

Using the INTERVAL DAY TO SECOND Type

You use the INTERVAL DAY TO SECOND type to store time intervals measured in days and seconds. The following statement creates a table named promotions that stores promotion information. The promotions table contains an INTERVAL DAY TO SECOND column named duration to record the interval of time for which the promotion is valid:

CREATE TABLE promotions ( promotion_id INTEGER CONSTRAINT promotions_pk PRIMARY KEY, name VARCHAR2(30) NOT NULL, duration INTERVAL DAY(3) TO SECOND (4) );

Notice I ve provided a precision of 3 for the day and a precision of 4 for the fractional seconds of the duration column. This means up to three digits may be stored for the day of the interval and up to four digits to the right of the decimal point for the fractional seconds.

To supply an INTERVAL DAY TO SECOND literal value to the database, you use the following simplified syntax:

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

where

• + or “ is an optional indicator that specifies whether the time interval is positive or negative (default is positive).

• d is the number of days for the interval.

• h is the optional number of hours for the interval; if you supply days and hours, you must include TO HOUR in your literal.

• m is the optional number of minutes for the interval; if you supply days and minutes, you must include TO MINUTES in your literal.

• s is the optional number of seconds for the interval; if you supply days and seconds you must include TO SECOND in your literal.

• days_precision is the optional precision for the days (default is 2).

• seconds_precision is the optional precision for the fractional seconds (default is 6).

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

The following INSERT statements add rows to the promotions table with the duration column values set to some of the valid intervals shown in the previous 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 '25' MINUTE); INSERT INTO promotions (promotion_id, name, duration) VALUES (4, '20% off Tank War', INTERVAL '45' 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 rows from the promotions table so you can see 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

Time Interval “Related Functions

There are a number of functions that allow you to get and process time intervals; these functions are shown in Table 4-9.

You ll learn more about the functions shown in Table 4-9 in the following sections.

NUMTODSINTERVAL()

You use NUMTODSINTERVAL( x , interval_unit ) to convert the number x to an INTERVAL DAY TO SECOND with the interval for x supplied in interval_unit . You may set interval_unit to DAY , HOUR , MINUTE , or SECOND. For example:

SELECT NUMTODSINTERVAL(1.5, 'DAY'), NUMTODSINTERVAL(3.25, 'HOUR'), NUMTODSINTERVAL(5, 'MINUTE'), NUMTODSINTERVAL(10.123456789, 'SECOND') FROM dual; NUMTODSINTERVAL(1.5,'DAY') ---------------------------------------------- NUMTODSINTERVAL(3.25,'HOUR') ---------------------------------------------- NUMTODSINTERVAL(5,'MINUTE') ---------------------------------------------- NUMTODSINTERVAL(10.123456789,'SECOND') ---------------------------------------------- +000000001 12:00:00.000000000 +000000000 03:15:00.000000000 +000000000 00:05:00.000000000 +000000000 00:00:10.123456789

NUMTOYMINTERVAL()

You use NUMTOYMINTERVAL( x , interval_unit ) to convert the number x to an INTERVAL YEAR TO MONTH with the interval for x supplied in interval_unit . You may set interval_unit to YEAR or MONTH. For example:

SELECT NUMTOYMINTERVAL(1.5, 'YEAR'), NUMTOYMINTERVAL(3.25, 'MONTH') FROM dual; NUMTOYMINTERVAL(1.5,'YEAR') --------------------------- NUMTOYMINTERVAL(3.25,'MONTH') ----------------------------- +000000001-06 +000000000-03