Syntax
DATE_SUB(date,INTERVAL expr unit)
Purpose
DATE_SUB() returns the value of date minus expr.
datespecifies the starting date, which can be theDATE,TIME, orDATETIMEtype.exprspecifies the time interval, which can be a negative value.unitspecifies the unit of the time interval.
The following table lists all time interval units.
| Unit | Type | Description | Format |
|---|---|---|---|
MICROSECOND |
Independent | Microseconds | MICROSECONDS |
SECOND |
Independent | Seconds | SECONDS |
MINUTE |
Independent | Minutes | MINUTES |
HOUR |
Independent | Hours | HOURS |
DAY |
Independent | Days | DAYS |
WEEK |
Independent | Weeks | WEEKS |
MONTH |
Independent | Months | MONTHS |
QUARTER |
Independent | Quarters | QUARTERS |
YEAR |
Independent | Years | YEARS |
SECOND_MICROSECOND |
Combined | Seconds and microseconds | 'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND |
Combined | Minutes, seconds, and microseconds | 'MINUTES:SECONDS.MICROSECONDS' |
MINUTE_SECOND |
Combined | Minutes and seconds | 'MINUTES:SECONDS' |
HOUR_MICROSECOND |
Combined | Hours, minutes, seconds, and microseconds | 'HOURS:MINUTES:SECONDS.MICROSECONDS' |
HOUR_SECOND |
Combined | Hours, minutes, and seconds | 'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE |
Combined | Hours and minutes | 'HOURS:MINUTES' |
DAY_SECOND |
Combined | Days, hours, minutes, and seconds | 'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE |
Combined | Days, hours, and minutes | 'DDAYSD HOURS:MINUTES' |
DAY_HOUR |
Combined | Days and hours | 'DAYS HOURS' |
YEAR_MONTH |
Combined | Years and months | '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
