SAND CDBMS SQL Reference Guide
Date/Time Arithmetic

 

Previous Topic:
Intervals and Date/Time Values
Chapter Index
Next Topic:
Interval Aggregation

 

Interval Arithmetic

Intervals can be added to and subtracted from other intervals in most cases. The following table shows the possible combinations of addition and subtraction between two intervals, and the resulting interval types ("*" means that an arithmetic operation is not permitted between the two interval types):

+ | – DAYS MONTHS YEARS HOURS MINUTES SECONDS MICROSECONDS
DAYS DAYS * * SECONDS SECONDS SECONDS SECONDS
MONTHS * MONTHS MONTHS * * * *
YEARS * MONTHS MONTHS * * * *
HOURS SECONDS * * SECONDS SECONDS SECONDS SECONDS
MINUTES SECONDS * * SECONDS SECONDS SECONDS SECONDS
SECONDS SECONDS * * SECONDS SECONDS SECONDS SECONDS
MICROSECONDS SECONDS * * SECONDS SECONDS SECONDS SECONDS


Intervals can also be involved in addition, subtraction, multiplication, and division operations with numeric expressions as operands. The interval can appear on either side of an operator (+, , *, /) in all cases except one: a numeric expression cannot be divided by an interval. For example, "12/(3 days)" is an invalid arithmetic operation.

The following table lists the interval type resulting from the arithmetic interaction of a numeric expression and an interval of a given type (the result is the same for each operator):

Interval Result Type
DAYS DAYS
MONTHS MONTHS
YEARS MONTHS
HOURS SECONDS
MINUTES SECONDS
SECONDS SECONDS
MICROSECONDS SECONDS

 

Examples

Expression
Result
1 DAY + 1 HOUR
90000.000000 (seconds)
60 SECONDS + 500 MICROSECONDS
60.000500 (seconds)
12 MONTHS + 1 YEAR
24 (months)
1 YEAR * 2
24 (months)
28 DAYS / 7
4 (days)
8 * 2 HOURS
57600 (seconds)
20 MICROSECONDS + 2
2.000020 (seconds)
1 DAY - (30 + 30 MINUTES)
84570.000000 (seconds)

 

Previous Topic:
Intervals and Date/Time Values
Chapter Index
Next Topic:
Interval Aggregation