-
(minus sign) operator
October 10, 2023
Applies to: Databricks SQL Databricks Runtime
Returns the subtraction of expr2
from expr1
.
Arguments
expr1
: A numeric, DATE, TIMESTAMP, or INTERVAL expression.expr2
: The accepted type depends on the type ofexpr
:If
expr1
is a numericexpr2
must be numeric expressionIf
expr1
is a year-month or day-time interval,expr2
must be of the matching class of interval.Otherwise
expr2
must be a DATE or TIMESTAMP.
Returns
The result type is determined in the following order:
If
expr1
is a numeric, the result is common maximum type of the arguments.If
expr1
is a DATE andexpr2
is a day-time interval the result is a TIMESTAMP.If
expr1
is a TIMESTAMP andexpr2
is an interval the result is a TIMESTAMP.If
expr1
andexpr2
are DATEs the result is anINTERVAL DAYS
.If
expr1
orexpr2
are TIMESTAMP the result is anINTERVAL DAY TO SECOND
.If
expr1
andexpr2
are year-month intervals the result is a year-month interval of sufficiently wide units to represent the result.If
expr1
andexpr2
are day-time intervals the result is a day-time interval of sufficiently wide units to represent the result.Otherwise, the result type matches
expr1
.
If both expressions are interval they must be of the same class.
When you subtract a year-month interval from a DATE, Databricks SQL ensures that the resulting date is well-formed.
If the result overflows the result type, Databricks SQL raises an ARITHMETIC_OVERFLOW error.
Use try_subtract to return NULL
on overflow.
Warning
In Databricks Runtime, if spark.sql.ansi.enabled is false
, an overflow does not cause an error but “wraps” the result instead.
Examples
> SELECT 2 - 1;
1
> SELECT DATE'2021-03-20' - INTERVAL '2' MONTH
2021-1-20
> SELECT TIMESTAMP'2021-03-20 12:15:29' - INTERVAL '3' SECOND
2021-03-20 12:15:26
> SELECT typeof(INTERVAL '3' DAY - INTERVAL '2' HOUR);
interval day to hour
> SELECT typeof(current_date - (current_date + INTERVAL '1' DAY));
interval day
> SELECT typeof(current_timestamp - (current_date + INTERVAL '1' DAY));
interval day to second
> SELECT DATE'2021-03-31' - INTERVAL '1' MONTH;
2021-02-28
> SELECT -100Y - 100Y;
Error: ARITHMETIC_OVERFLOW