DATE_SUB

Declaration

DATE_SUB(date,INTERVAL expr unit)

Description

Returns the value of the date and time date minus expr.

• date specifies the base date and time, which can be of the DATE, TIME, or DATETIME type.

• expr specifies the time interval, which can be negative.

• unit specifies the unit of the time interval.

The following table lists all the time interval units:

Unit	Type	Description	Format
MICROSECOND	Independent	Microsecond	MICROSECONDS
SECOND	Independent	Second	SECONDS
MINUTE	Independent	Minute	MINUTES
HOUR	Independent	Hour	HOURS
DAY	Independent	Day	DAYS
WEEK	Independent	Week	WEEKS
MONTH	Independent	Month	MONTHS
QUARTER	Independent	Quarter	QUARTERS
YEAR	Independent	Year	YEARS
SECOND_MICROSECOND	Combined	Second to microsecond	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	Combined	Minute to microsecond	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	Combined	Minute to second	'MINUTES:SECONDS'
HOUR_MICROSECOND	Combined	Hour to microsecond	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	Combined	Hour to second	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	Combined	Hour to minute	'HOURS:MINUTES'
DAY_SECOND	Combined	Day to second	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	Combined	Day to minute	'DDAYSD HOURS:MINUTES'
DAY_HOUR	Combined	Day to hour	'DAYS HOURS'
YEAR_MONTH	Combined	Year to month	'YEARS-MONTHS'

Examples

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): 2025-12-12 16:23:56
                       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 (0.002 sec)