Icon Arithmetic Operators

The following are the arithmetic operators in ElevateDB, ordered by their operator precedence:

OperatorDescription
*Multiplies the left numeric or interval expression by the right numeric or interval expression.
/Divides the left numeric or interval expression by the right numeric expression.
-Subtracts the right numeric, date, time, timestamp, or interval expression from the left numeric, date, time, timestamp, or interval expression.
+Adds the right numeric, date, time, timestamp, or interval expression to the left numeric, date, time, timestamp, or interval expression.
MODReturns the remainder derived from dividing the left numeric or interval expression by the right numeric or interval expression.

Date, Time, and Timestamp Subtraction
When subtracting dates, times, and timestamps, the result is always an interval value. The type of interval value depends upon what is being subtracted. The following details the interval type that you can expect with the various input types:

ExpressionInterval Type
DATE - DATEINTERVAL DAY
TIME - TIMEINTERVAL HOUR TO MSECOND
TIMESTAMP - TIMESTAMP
TIMESTAMP - DATE
INTERVAL DAY TO MSECOND

In addition, you can force the resulting interval value to a specific type by enclosing the expression in parentheses and appending the interval type to the expression:

(<Value> - <Value>) <Interval Type>

Information Unlike in most scenarios in SQL where an interval type is prefaced with the keyword INTERVAL, specifying an interval type for DATE/TIME/TIMESTAMP subtraction only requires the actual interval type without the INTERVAL keyword. It is done this way due to the fact that ElevateDB already knows that the resulting type is an interval. The only question is which type of interval is desired by the application.

The valid interval types for each type of expression are as follows:

ExpressionInterval Type
DATE - DATEYEAR
MONTH
YEAR TO MONTH
DAY
TIME - TIMEHOUR
HOUR TO MINUTE
HOUR TO SECOND
HOUR TO MSECOND
MINUTE
MINUTE TO SECOND
MINUTE TO MSECOND
SECOND
SECOND TO MSECOND
MSECOND
TIMESTAMP - TIMESTAMP
TIMESTAMP - DATE
YEAR
MONTH
YEAR TO MONTH
DAY
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
DAY TO MSECOND
HOUR
HOUR TO MINUTE
HOUR TO SECOND
HOUR TO MSECOND
MINUTE
MINUTE TO SECOND
MINUTE TO MSECOND
SECOND
SECOND TO MSECOND
MSECOND

Examples
-- The following uses the subtraction
-- operator to show all customers that
-- have not ordered within the last 12
-- months

SELECT *
FROM Customers
WHERE (CURRENT_DATE-LastOrderDate) MONTH > 12

SQL 2003 Standard Deviations
The following areas are where ElevateDB deviates from the SQL 2003 standard:

DeviationDetails
None
Image