Using Time Intervals : Time Interval « SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL Data Types
3. Time Interval

Examples of time intervals include

1. 2 year 3 months
2. 5 months
3. C3 days 5 hours 16 minutes
4. 1 day 7 hours
5. C6 hours

Time Interval Types

1. INTERVAL YEAR[(years_precision)] TO MONTH
2. Stores a time interval measured in years and months.
3. You can specify an optional precision by supplying years_precision.
4. years_precision may be an integer from 0 to 9.
5. The default precision is 2, which means you can store two digits for the years in your interval.
6. You can store a positive or negative time interval.

INTERVAL DAY[(days_precision)]TO SECOND[(seconds_precision)]

1. Stores a time interval measured in days and seconds.
2. You can specify an optional precision for the days.
3. A days_precision integer is from 0 to 9 (default is 2).
4. You can also specify an optional precision for the fractional seconds.
5. A seconds_precision integer is from 0 to 9 (default is 6).
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

1. + or - is an optional indicator that specifies whether the time interval is positive or negative (default is positive).
2. y is the optional number of years for the interval.
3. m is the optional number of months for the interval. If you supply years and months, you must include TO MONTH in your literal.
4. 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