+ (plus sign) operator

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

Returns the sum of expr1 and expr2.

Syntax 

expr1 + expr2

Arguments

  • expr1: A numeric, DATE, TIMESTAMP, or INTERVAL expression.

  • expr2: If expr1 is a numeric expr2 must be numeric expression, or an INTERVAL otherwise.

Returns

  • If expr1 is a numeric, the 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 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 add a year-month interval to a DATE, Databricks ensures that the resulting date is well formed.

If the result overflows the result type, Databricks raises an ARITHMETIC_OVERFLOW error.

Use try_add 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 1 + 2;
 3

> SELECT DATE'2021-03-20' + INTERVAL '2' MONTH
 2021-5-20

> SELECT TIMESTAMP'2021-03-20 12:15:29' + INTERVAL '3' SECOND
 2021-03-20 12:15:32

> SELECT typeof(INTERVAL '3' DAY + INTERVAL '2' HOUR)
 interval day to hour

> SELECT DATE'2021-03-31' + INTERVAL '1' MONTH;
 2021-04-30

> SELECT 127Y + 1Y;
 Error: ARITMETIC_OVERFLOW