- (minus sign) operator

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the subtraction of expr2 from expr1.

Syntax

expr1 - expr2

Arguments

  • expr1: A numeric, DATE, TIMESTAMP, or INTERVAL expression.
  • expr2: The accepted type depends on the type of expr:
    • If expr1 is a numeric expr2 must be numeric expression
    • If 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 and expr2 is a day-time interval the result is a TIMESTAMP.
  • If expr1 is a TIMESTAMP and expr2 is an interval the result is a TIMESTAMP.
  • If expr1 and expr2 are DATEs the result is an INTERVAL DAYS.
  • If expr1 or expr2 are TIMESTAMP the result is an INTERVAL DAY TO SECOND.
  • If expr1 and expr2 are year-month intervals the result is a year-month interval of sufficiently wide units to represent the result.
  • If expr1 and expr2 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