Use interval year to month type

Oracle database 9i Databases introduce a new feature that can be used to store time intervals. Examples of time intervals include:

1 Years 3 months

25 months

-3 days, 5 hours, 16 minutes

1 days 7 hours

-56 hours

Attention:

Do not confuse the concept of time intervals with time values or timestamps. The time interval is recorded for a period of time (for example, 1 years and 3 months), while a time value or timestamp records a specific date and time (for example, October 28, 2006 7:32 P.M. 16 seconds).

In this example of an imaginary online store, we may want to discount a particular item for a specific period of time. For example, you can allow customers to use coupons, which are valid for several months, or are discounted for promotional products within a few days. Several examples of using coupons and promotions are described later in this section.

Table 5-10 lists the types of time intervals.

Table 5-10 time interval types

Class Type	Speak clearly
interval year[  (years_precision) to MONTH	store an interval of time, in years and months , you can specify the precision of the year by specifying an optional years_precision parameter, which is an integer of 0~9. The default precision is 2, which means that you can store two digits for the number of years in a time interval. An error is returned if you try to add a row to a table that exceeds the records that can be stored by the interval year to month column. A time interval can store positive numbers or a negative number
INTERVAL day[(days_precision)] To second[(seconds_precision)]	Stores a time interval in days and seconds; You can specify the precision of the day by specifying the optional days_precision parameter, which is an integer of 0~9 and the default value is 2. In addition, you can specify the precision of fractional seconds by specifying the optional seconds_precision parameter, which is an integer of 0~9 and the default value is 6. The time interval can store either positive or

Table 5-11 Examples of time interval literals

Time interval literal	Description
INTERVAL ' 1 ' year	Time interval is 1 years
INTERVAL ' One ' MONTH	Time interval is 11 months
INTERVAL ' MONTH	Time interval is 14 months (equals 1 years 2 months)
INTERVAL ' 1-3 ' year to MONTH	Time interval is 1 years 3 months
INTERVAL ' 0-5 ' year to MONTH	Time interval is 0 years 5 months
INTERVAL ' 123 ' year (3) to MONTH	123-year interval with 3 digits of precision
INTERVAL ' -1-5 ' year to MONTH	The interval is negative, with a value of 1 years and 5 months
INTERVAL ' 1234 ' year (3)	Invalid time interval: 1234 contains 4 digits, so it contains more digits than is allowed for precision (up to 3 digits allowed)

SQL Example:

Sql> SelectInterval' One' Month  fromDual;interval' One'MONTH---------------------------------------+xx- OneSQL> SelectInterval' One'  Year  fromDual;interval' One' Year---------------------------------------+ One-xxSQL> SelectInterval' -' Month  fromDual;interval' -'MONTH---------------------------------------+ on- GenevaSQL> SelectInterval'1-3'  Year  to Month  fromDual;interval'1-3'Yeartomonth---------------------------------------+ on-GenevaSQL> SelectInterval'0-3'  Year  to Month  fromDual;interval'0-3'Yeartomonth---------------------------------------+xx-GenevaSQL> SelectInterval'101'  Year(3) fromDual;interval'101' Year(3)---------------------------------------+101-xxSQL> SelectInterval'-10-5'  Year  to Month  fromDual;interval'-10-5'Yeartomonth----------------------------------------Ten- toSQL> SelectInterval'1234'  Year(3) fromdual;SelectInterval'1234'  Year(3) fromDualora-01873: The leading precision of the interval is too small SQL> 

5.8.1 using interval year to month type

The INTERVAL year to month type can be used to store the time interval for a unit of years and months. The following statement creates a table named coupons that stores coupon information. The coupons table contains a column duration that interval year to month, which is used to record the time interval at which the coupon is valid. Note that the Duration column is specified here with a precision of 3, which means that you can store 3 digits for the number of years of the column:

CREATE TABLE Coupons ( coupon_id INTEGER CONSTRAINT coupons_pk PRIMARY KEY, Name VARCHAR2 (+) is not NULL, Duration INTERVAL Year (3) to MONTH );

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

INTERVAL ' [+|-][y][-m] ' [year[(Years_precision)]] [to MONTH]

which

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

Y is an optional parameter that represents the number of years in the time interval section.

M is an optional parameter that represents the number of months in a time interval. If you specify a number of years and months, you must include to month in the interval clause.

Year_precision is an optional parameter that describes the precision of the number of years (the default is 2).

"From:http://www.cnblogs.com/theurgy/articles/1549454.html"

Use interval year to month type