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