Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Interval Expressions

An interval expression yields a value of INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND.

Description of interval_expression.gif follows
Description of the illustration interval_expression.gif

The interval_value_expr can be the value of an INTERVAL column or a compound expression that yields an interval value. Datetimes and intervals can be combined according to the rules defined in Table 2-6. The six combinations that yield interval values are valid in an interval expression.

Both leading_field_precision and fractional_second_precision can be any integer from 0 to 9. If you omit the leading_field_precision for either DAY or YEAR, then Oracle Database uses the default value of 2. If you omit the fractional_second_precision for second, then the database uses the default value of 6. If the value returned by a query contains more digits that the default precision, then Oracle Database returns an error. Therefore, it is good practice to specify a precision that you know will be at least as large as any value returned by the query.

For example, the following statement subtracts the value of the order_date column in the sample table orders (a datetime value) from the system timestamp (another datetime value) to yield an interval value expression. Because we do not know how many days ago the oldest order was placed, we specify the maximum value of 9 for the DAY lading field precision:

SELECT (SYSTIMESTAMP - order_date) DAY(9) TO SECOND from orders
   WHERE order_id = 2458;