Syntax
DATE_SUB(date,INTERVAL expr unit)
Purpose
You can call this function to return the difference 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