SUBTRACT TIME INTERVAL
Subtract time interval function
Subtract time interval from a date or timestamp, return the result of date or timestamp type.
Analyze Syntax
func.subtract_years(<exp0>, <expr1>)
func.subtract_quarters(<exp0>, <expr1>)
func.subtract_months(<exp0>, <expr1>)
func.subtract_days(<exp0>, <expr1>)
func.subtract_hours(<exp0>, <expr1>)
func.subtract_minutes(<exp0>, <expr1>)
func.subtract_seconds(<exp0>, <expr1>)
Analyze Examples
func.to_date(18875), func.subtract_years(func.to_date(18875), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_date(18875) | func.subtract_years(func.to_date(18875), 2) |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 | 2019-09-05 |
+---------------------------------+--------------------------------------------------------+
func.to_date(18875), func.subtract_quarters(func.to_date(18875), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_date(18875) | subtract_quarters(func.to_date(18875), 2) |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 | 2021-03-05 |
+---------------------------------+--------------------------------------------------------+
func.to_date(18875), func.subtract_months(func.to_date(18875), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_date(18875) | func.subtract_months(func.to_date(18875), 2) |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 | 2021-07-05 |
+---------------------------------+--------------------------------------------------------+
func.to_date(18875), func.subtract_days(func.to_date(18875), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_date(18875) | func.subtract_days(func.to_date(18875), 2) |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 | 2021-09-03 |
+---------------------------------+--------------------------------------------------------+
func.to_datetime(1630833797), func.subtract_hours(func.to_datetime(1630833797), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_datetime(1630833797) | func.subtract_hours(func.to_datetime(1630833797), 2) |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 07:23:17.000000 |
+---------------------------------+--------------------------------------------------------+
func.to_datetime(1630833797), func.subtract_minutes(func.to_datetime(1630833797), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_datetime(1630833797) | func.subtract_minutes(func.to_datetime(1630833797), 2) |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 09:21:17.000000 |
+---------------------------------+--------------------------------------------------------+
func.to_datetime(1630833797), func.subtract_seconds(func.to_datetime(1630833797), 2)
+---------------------------------+--------------------------------------------------------+
| func.to_datetime(1630833797) | func.subtract_seconds(func.to_datetime(1630833797), 2) |
+---------------------------------+--------------------------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 09:23:15.000000 |
+---------------------------------+--------------------------------------------------------+
SQL Syntax
SUBTRACT_YEARS(<exp0>, <expr1>)
SUBTRACT_QUARTERS(<exp0>, <expr1>)
SUBTRACT_MONTHS(<exp0>, <expr1>)
SUBTRACT_DAYS(<exp0>, <expr1>)
SUBTRACT_HOURS(<exp0>, <expr1>)
SUBTRACT_MINUTES(<exp0>, <expr1>)
SUBTRACT_SECONDS(<exp0>, <expr1>)
Return Type
DATE, TIMESTAMP depends on the input.
SQL Examples
SELECT to_date(18875), subtract_years(to_date(18875), 2);
+----------------+-----------------------------------+
| to_date(18875) | subtract_years(to_date(18875), 2) |
+----------------+-----------------------------------+
| 2021-09-05 | 2019-09-05 |
+----------------+-----------------------------------+
SELECT to_date(18875), subtract_quarters(to_date(18875), 2);
+----------------+--------------------------------------+
| to_date(18875) | subtract_quarters(to_date(18875), 2) |
+----------------+--------------------------------------+
| 2021-09-05 | 2021-03-05 |
+----------------+--------------------------------------+
SELECT to_date(18875), subtract_months(to_date(18875), 2);
+----------------+------------------------------------+
| to_date(18875) | subtract_months(to_date(18875), 2) |
+----------------+------------------------------------+
| 2021-09-05 | 2021-07-05 |
+----------------+------------------------------------+
SELECT to_date(18875), subtract_days(to_date(18875), 2);
+----------------+----------------------------------+
| to_date(18875) | subtract_days(to_date(18875), 2) |
+----------------+----------------------------------+
| 2021-09-05 | 2021-09-03 |
+----------------+----------------------------------+
SELECT to_datetime(1630833797), subtract_hours(to_datetime(1630833797), 2);
+----------------------------+--------------------------------------------+
| to_datetime(1630833797) | subtract_hours(to_datetime(1630833797), 2) |
+----------------------------+--------------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 07:23:17.000000 |
+----------------------------+--------------------------------------------+
SELECT to_datetime(1630833797), subtract_minutes(to_datetime(1630833797), 2);
+----------------------------+----------------------------------------------+
| to_datetime(1630833797) | subtract_minutes(to_datetime(1630833797), 2) |
+----------------------------+----------------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 09:21:17.000000 |
+----------------------------+----------------------------------------------+
SELECT to_datetime(1630833797), subtract_seconds(to_datetime(1630833797), 2);
+----------------------------+----------------------------------------------+
| to_datetime(1630833797) | subtract_seconds(to_datetime(1630833797), 2) |
+----------------------------+----------------------------------------------+
| 2021-09-05 09:23:17.000000 | 2021-09-05 09:23:15.000000 |
+----------------------------+----------------------------------------------+
Last modified June 12, 2024 at 9:53 PM EST: updating datetime functions (f087f54)