Addition and Subtraction Functions

Perform arithmetic operations on numerical and date values.

The table below contains the supported unit for the following functions.

Unit	Description
second	Seconds
minute	Minutes
hour	Hours
day	Days
week	Weeks
month	Months
quarter	Quarters
year	Years

DATE_ADD( [ <unit>, ] value, <date_expr> ) or DATEADD( [ <unit>, ] value, <date_expr> )

Returns a date expression after adding an interval value of type unit to the input date expression.

> select date_add('year', 5, cast('2000-08-05' as date));
2005-08-05

DATE_DIFF( <date expr1>, <date_expr2> [, <unit> ] )

Returns the number of units between two date expressions.

unit - optional - The unit used to calculate the difference. Default value: day.

> select date_diff(cast('2006-01-12' as date), cast('2006-01-21' as date));
-9

> select date_diff(cast('2005-10-12' as date), cast('2005-03-12' as date), 'month');
7

DATE_DIFF( [ <unit>, ] <date expr1>, <date_expr2> )

Returns the number of units between two date expressions.

unit - optional - The unit used to calculate the difference. Default value: day.

> select date_diff(cast('2006-01-12' as date), cast('2006-01-21' as date));
-9

> select date_diff('month', cast('2005-03-12' as date), cast('2005-10-12' as date));
7

DATEDIFF( [ <unit> ] , <date expr1>, <date_expr2> )

Returns the number of units between two date expressions.

unit - optional - The unit used to calculate the difference. Default value: day.

> select datediff(cast('2006-01-12' as date), cast('2006-01-21' as date));
9

> select datediff('month', cast('2005-10-12' as date), cast('2005-03-12' as date));
- 7

TIMESTAMP_ADD( unit, value, <timestamp_expr> )

Returns a timestamp expression after adding an interval value of type unit to the input timestamp expr.

> select timestamp_add('hour', 4, cast('2005-10-12 05:10:20' as timestamp));
2005-10-12 09:10:20

TIMESTAMP_DIFF( <timestamp_expr1>, <timestamp_expr2>, unit )

Returns the difference of timestamp expr1 and timestamp expr2 with respect to unit.

> select timestamp_diff(cast('2005-10-12 01:25:20' as timestamp), cast('2005-10-12 02:04:13' as timestamp), 'minute');
-38

Date Time Operators

The following operators can be used to perform mathematical operations on date-time expressions.

+ - Addition Operator

> select current_timestamp + interval 8 minute
2022-09-13 03:13:10

> select current_date + interval 8 day
2022-09-21

> select cast('2022-01-02' as date) + interval 24 hour + interval 11 year

- - Subtraction Operator

> select current_timestamp - interval 2 hour
2022-09-13 03:13:10

> select current_date - interval 8 month
2022-09-21

Combined Operators

> select (cast('2022-01-02' as date)  + interval 10 hour) - interval 11 year
2011-01-02

Limits -

• Week and quarter are not supported in interval (addition/subtraction )