Addition and Subtraction Functions

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

UnitDescription

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 )

Last updated

#930: Cross account hive GCP

Change request updated