![]() Previous |
![]() Next |
You can perform several arithmetic operations on date (DATETIME
), timestamp (TIMESTAMP
, TIMESTAMP_TZ
, and TIMESTAMP_LTZ
) and interval (DSINTERVAL
and YMINTERVAL
) data. Oracle calculates the results based on the following rules:
You can use NUMBER
constants in arithmetic operations on date and timestamp values, but not interval values. Oracle internally converts timestamp values to date values and interprets NUMBER
constants in arithmetic datetime and interval expressions as numbers of days. For example, SYSDATE
+ 1 is tomorrow. SYSDATE
- 7 is one week ago. SYSDATE
+ (10/1440) is ten minutes from now. Subtracting the hire_date
column of the sample table employees
from SYSDATE
returns the number of days since each employee was hired. You cannot multiply or divide date or timestamp values.
Oracle implicitly converts BINARY_FLOAT
and BINARY_DOUBLE
operands to NUMBER
.
Each DATETIME
value contains a time component, and the result of many date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours. These fractions are also returned by Oracle built-in functions for common operations on DATETIME
data. For example, the MONTHS_BETWEEN
function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.
If one operand is a DATETIME
value or a numeric value (neither of which contains time zone or fractional seconds components), then:
Oracle implicitly converts the other operand to DATETIME
data. (The exception is multiplication of a numeric value times an interval, which returns an interval.)
If the other operand has a time zone value, then Oracle uses the session time zone in the returned value.
If the other operand has a fractional seconds value, then the fractional seconds value is lost.
When you pass a timestamp, interval, or numeric value to a built-in function that was designed only for the DATETIME
data type, Oracle implicitly converts the non-DATETIME
value to a DATETIME
value.
When interval calculations return a datetime value, the result must be an actual datetime value or the Database returns an error.
Oracle performs all timestamp arithmetic in UTC time. For TIMESTAMP_LTZ
, Oracle converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For TIMESTAMP_TZ
, the datetime value is always in UTC, so no conversion is necessary.
Table: Matrix of Datetime Arithmetic is a matrix of datetime arithmetic operations. Dashes represent operations that are not supported.
Matrix of Datetime Arithmetic
Operand & Operator | DATETIME | TIMESTAMP | INTERVAL | Numeric |
---|---|---|---|---|
DATETIME |
— |
— |
— |
— |
+ |
|
|
|
|
- |
|
|
|
|
* |
|
|
|
|
/ |
|
|
|
|
TIMESTAMP |
— |
— |
— |
— |
+ |
|
|
|
|
- |
|
|
|
|
* |
|
|
|
|
/ |
|
|
|
|
INTERVAL |
— |
— |
— |
— |
+ |
|
|
|
|
- |
|
|
|
|
* |
|
|
|
|
/ |
|
|
|
|
Numeric |
— |
— |
— |
— |
+ |
|
|
|
|
- |
|
|
|
|
* |
|
|
|
|
/ |
|
|
|
|
Examples You can add an interval value expression to a start time. Consider the sample table oe.orders
with a column order_date
.