Declaration
DATE_SUB(date,INTERVAL expr unit)
Description
Return the value of date minus expr.
date: the date and time base, which can be of theDATE,TIME, orDATETIMEtype.exprindicates the time interval, which can be negative.unitindicates the unit of time interval.
The following table lists all time interval units:
unit |
Type |
Meaning |
Format |
|---|---|---|---|
MICROSECOND |
standalone | microsecond | MICROSECONDS |
SECOND |
standalone | Seconds | SECONDS |
MINUTE |
standalone | minutes | MINUTES |
HOUR |
standalone | Hour | HOURS |
DAY |
standalone | day | DAYS |
WEEK |
standalone | Week | WEEKS |
MONTH |
standalone | Month | MONTHS |
QUARTER |
standalone | Quarter | QUARTERS |
YEAR |
standalone | year | YEARS |
SECOND_MICROSECOND |
combination | Seconds to microseconds | 'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND |
combination | minutes to microseconds | 'MINUTES:SECONDS.MICROSECONDS' |
MINUTE_SECOND |
combination | Minutes to Seconds | 'MINUTES:SECONDS' |
HOUR_MICROSECOND |
combination | hours to microseconds | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
HOUR_SECOND |
combination | hours to seconds | 'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE |
combination | Hour to Minute | 'HOURS:MINUTES' |
DAY_SECOND |
combination | Days to Seconds | 'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE |
combination | Days to Minutes | 'DDAYSD HOURS:MINUTES' |
DAY_HOUR |
combination | Day to Hour | 'DAYS HOURS' |
YEAR_MONTH |
combination | Year to Month | 'YEARS-MONTHS' |
Examples
obclient> SELECT
DATE_SUB(NOW(), INTERVAL 5 DAY),
DATE_SUB('2014-01-10', INTERVAL 5 MICROSECOND),
DATE_SUB('2014-01-10', INTERVAL 5 SECOND),
DATE_SUB('2014-01-10', INTERVAL 5 MINUTE),
DATE_SUB('2014-01-10', INTERVAL 5 HOUR),
DATE_SUB('2014-01-10', INTERVAL 5 DAY),
DATE_SUB('2014-01-10', INTERVAL 5 WEEK),
DATE_SUB('2014-01-10', INTERVAL 5 MONTH),
DATE_SUB('2014-01-10', INTERVAL 5 QUARTER),
DATE_SUB('2014-01-10', INTERVAL 5 YEAR),
DATE_SUB('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND),
DATE_SUB('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND),
DATE_SUB('2014-01-10', INTERVAL '05:05' MINUTE_SECOND),
DATE_SUB('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND),
DATE_SUB('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND),
DATE_SUB('2014-01-10', INTERVAL '05:05' HOUR_MINUTE),
DATE_SUB('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND),
DATE_SUB('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND),
DATE_SUB('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE),
DATE_SUB('2014-01-10', INTERVAL '01 05' DAY_HOUR),
DATE_SUB('2014-01-10', INTERVAL '1-01' YEAR_MONTH)
\G
*************************** 1. row ***************************
DATE_SUB(NOW(), INTERVAL 5 DAY): 2021-08-18 14:56:32
DATE_SUB('2014-01-10', INTERVAL 5 MICROSECOND): 2014-01-09 23:59:59.999995
DATE_SUB('2014-01-10', INTERVAL 5 SECOND): 2014-01-09 23:59:55
DATE_SUB('2014-01-10', INTERVAL 5 MINUTE): 2014-01-09 23:55:00
DATE_SUB('2014-01-10', INTERVAL 5 HOUR): 2014-01-09 19:00:00
DATE_SUB('2014-01-10', INTERVAL 5 DAY): 2014-01-05
DATE_SUB('2014-01-10', INTERVAL 5 WEEK): 2013-12-06
DATE_SUB('2014-01-10', INTERVAL 5 MONTH): 2013-08-10
DATE_SUB('2014-01-10', INTERVAL 5 QUARTER): 2012-10-10
DATE_SUB('2014-01-10', INTERVAL 5 YEAR): 2009-01-10
DATE_SUB('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND): 2014-01-09 23:59:54.999995
DATE_SUB('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND): 2014-01-09 23:54:54.999995
DATE_SUB('2014-01-10', INTERVAL '05:05' MINUTE_SECOND): 2014-01-09 23:54:55
DATE_SUB('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND): 2014-01-09 18:54:54.999995
DATE_SUB('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND): 2014-01-09 18:54:55
DATE_SUB('2014-01-10', INTERVAL '05:05' HOUR_MINUTE): 2014-01-09 18:55:00
DATE_SUB('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND): 2014-01-08 18:54:54.999995
DATE_SUB('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND): 2014-01-08 18:54:55
DATE_SUB('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE): 2014-01-08 18:55:00
DATE_SUB('2014-01-10', INTERVAL '01 05' DAY_HOUR): 2014-01-08 19:00:00
DATE_SUB('2014-01-10', INTERVAL '1-01' YEAR_MONTH): 2012-12-10
1 row in set