Skip to main content

Subtract time interval

Subtract time interval from a date or datetime, return the result of date or datetime type.

Syntax​

subtractYears(exp0, expr1)
subtractMonths(exp0, expr1)
subtractDays(exp0, expr1)
subtractHours(exp0, expr1)
subtractMinutes(exp0, expr1)
subtractSeconds(exp0, expr1)

Return Type​

Date, Timestamp depends on the input.

Examples​

SELECT toDate(18875), subtractYears(toDate(18875), 2);
+---------------+---------------------------------+
| toDate(18875) | subtractYears(toDate(18875), 2) |
+---------------+---------------------------------+
| 2021-09-05    | 2019-09-05                      |
+---------------+---------------------------------+

SELECT toDate(18875), subtractMonths(toDate(18875), 2);
+---------------+----------------------------------+
| toDate(18875) | subtractMonths(toDate(18875), 2) |
+---------------+----------------------------------+
| 2021-09-05    | 2021-07-05                       |
+---------------+----------------------------------+

SELECT toDate(18875), subtractDays(toDate(18875), 2);
+---------------+--------------------------------+
| toDate(18875) | subtractDays(toDate(18875), 2) |
+---------------+--------------------------------+
| 2021-09-05    | 2021-09-03                     |
+---------------+--------------------------------+

SELECT toDateTime(1630833797), subtractHours(toDateTime(1630833797), 2);
+------------------------+------------------------------------------+
| toDateTime(1630833797) | subtractHours(toDateTime(1630833797), 2) |
+------------------------+------------------------------------------+
| 2021-09-05 09:23:17    | 2021-09-05 07:23:17                      |
+------------------------+------------------------------------------+

SELECT toDateTime(1630833797), subtractMinutes(toDateTime(1630833797), 2);
+------------------------+--------------------------------------------+
| toDateTime(1630833797) | subtractMinutes(toDateTime(1630833797), 2) |
+------------------------+--------------------------------------------+
| 2021-09-05 09:23:17    | 2021-09-05 09:21:17                        |
+------------------------+--------------------------------------------+

SELECT toDateTime(1630833797), subtractSeconds(toDateTime(1630833797), 2);
+------------------------+--------------------------------------------+
| toDateTime(1630833797) | subtractSeconds(toDateTime(1630833797), 2) |
+------------------------+--------------------------------------------+
| 2021-09-05 09:23:17    | 2021-09-05 09:23:15                        |
+------------------------+--------------------------------------------+