Mega Code Archive
Using Time Intervals
<![CDATA[
Examples of time intervals include
2 year 3 months
5 months
C3 days 5 hours 16 minutes
1 day 7 hours
C6 hours
Time Interval Types
INTERVAL YEAR[(years_precision)] TO MONTH
Stores a time interval measured in years and months.
You can specify an optional precision by supplying years_precision.
years_precision 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.
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.
A days_precision integer is from 0 to 9 (default is 2).
You can also specify an optional precision for the fractional seconds.
A seconds_precision integer is from 0 to 9 (default is 6).
You can store a positive or negative time interval.
Using the INTERVAL YEAR TO MONTH Type
INTERVAL YEAR TO MONTH type stores time intervals measured in years and months.
SQL>
SQL> CREATE TABLE myTable (
2 id INTEGER,
3 duration INTERVAL YEAR(3) TO MONTH
4 );
Table created.
SQL>
SQL> desc myTable;
Name Null? Type
-------------------------------------------------------------------------------------------
ID NUMBER(38)
DURATION INTERVAL YEAR(3) TO MONTH
SQL>
SQL> drop table myTable;
Table dropped.
A precision of 3 for the duration column means up to three digits may be stored for the year part of the interval.
An INTERVAL YEAR TO MONTH literal value:
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).
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
]]>