Using Time Intervals | 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.

Table 4-8: Time Interval Types
Type	Description
INTERVAL YEAR[( years_precision )]TO MONTH	Stores a time interval measured in years and months. You can specify an optional precision for the years by supplying years_precision , which may be an integer from 0 to 9. The default precision is 2, which means you can store two digits for the years in your interval. If you try to add a row with more year digits than your INTERVAL YEAR TO MONTH column can store, you'll get an error. You can store a positive or negative time interval.
INTERVAL DAY[( days_precision )]TO SECOND[( seconds_precision )]	Stores a time interval measured in days and seconds. You can specify an optional precision for the days by supplying a days_precision integer from 0 to 9 (default is 2). In addition, you can also specify an optional precision for the fractional seconds by supplying a seconds_precision integer from 0 to 9 (default is 6). You can store a positive or negative time interval.

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.

Literal	Description
INTERVAL ˜1 YEAR	Interval of 1 year
INTERVAL ˜11 MONTH	Interval of 11 months
INTERVAL ˜14 MONTH	Interval of 14 months (equivalent to 1 year 2 months)
INTERVAL ˜1-3 YEAR TO MONTH	Interval of 1 year 3 months
INTERVAL ˜0-5 YEAR TO MONTH	Interval of 0 years 5 months
INTERVAL ˜123 YEAR(3) TO MONTH	Interval of 123 years with a precision of 3 digits
INTERVAL ˜-1-5 YEAR TO MONTH	A negative interval of 1 year 5 months
INTERVAL ˜1234 YEAR(3)	Invalid interval: 1234 contains four digits and therefore contains one too many digits allowed by the precision of 3 (which allows up to three digits)

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.

Literal	Description
INTERVAL ˜3 DAY	Interval of 3 days
INTERVAL ˜2 HOUR	Interval of 2 hours
INTERVAL ˜25 MINUTE	Interval of 25 minutes
INTERVAL ˜45 SECOND	Interval of 45 seconds
INTERVAL ˜3 2 DAY TO HOUR	Interval of 3 days 2 hours
INTERVAL ˜3 2:25 DAY TO MINUTE	Interval of 3 days 2 hours 25 minutes
INTERVAL ˜3 2:25:45 DAY TO SECOND	Interval of 3 days 2 hours 25 minutes 45 seconds
INTERVAL ˜123 2:25:45.12 DAY(3) TO SECOND(2)	Interval of 123 days 2 hours 25 minutes 45.12 seconds; the precision for days is 3 digits and the precision for the fractional seconds is 2 digits
INTERVAL ˜3 2:00:45 DAY TO SECOND	Interval of 3 days 2 hours 0 minutes 45 seconds
INTERVAL ˜-3 2:25:45 DAY TO SECOND	Negative interval of 3 days 2 hours 25 minutes 45 seconds
INTERVAL ˜1234 2:25:45 DAY(3) TO SECOND	Invalid interval because the number of digits in the days exceeds the specified precision of 3
INTERVAL ˜123 2:25:45.123 DAY TO SECOND(2)	Invalid interval because the number of digits in the fractional seconds exceeds the specified precision of 2

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.

Table 4-9: Time Interval “Related Functions
Function	Description
NUMTODSINTERVAL( x , interval_unit )	Converts the number x to an INTERVAL DAY TO SECOND with the interval for x supplied in interval_unit , which you may set to DAY , HOUR , MINUTE , or SECOND .
NUMTOYMINTERVAL( x , interval_unit )	Converts the number x to an INTERVAL YEAR TO MONTH with the interval for x supplied in interval_unit , which you may set to YEAR or MONTH .
TO_DSINTERVAL( x )	Converts the string x to an INTERVAL DAY TO SECOND .
TO_YMINTERVAL( x )	Converts the string x to an INTERVAL YEAR TO MONTH .

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