MySQL: DATE_SUB Function

This tutorial explains how to use the MySQL DATE_SUB function with syntax and examples.

Description

The MySQL DATE_SUB function returns a date after which a certain time/date interval has been subtracted.

Syntax

The syntax for the DATE_SUB function in MySQL is:

DATE_SUB( date, INTERVAL value unit )

Parameters or Arguments

date
value
unit

unit	Compatibility
MICROSECOND	4.1.1+
SECOND	3.2.3+
MINUTE	3.2.3+
HOUR	3.2.3+
DAY	3.2.3+
WEEK	5+
MONTH	3.2.3+
QUARTER	5+
YEAR	3.2.3+
SECOND_MICROSECOND	4.1.1+
MINUTE_MICROSECOND	4.1.1+
MINUTE_SECOND	4.1.1+
HOUR_MICROSECOND	4.1.1+
HOUR_SECOND	4.1.1+
HOUR_MINUTE	3.2.3+
DAY_MICROSECOND	4.1.1+
DAY_SECOND	3.2.3+
DAY_MINUTE	3.2.3+
DAY_HOUR	3.2.3+
YEAR_MONTH	3.2.3+

Note

If you specify an interval value that is too short for the unit that you have specified, the DATE_SUB function will assume that the left-most portion of the interval value was not provided.
Using the DATE_SUB function with a negative value as a parameter is equivalent to using the DATE_ADD function.
See also the DATE_ADD, ADDDATE, SUBDATE, ADDTIME, and SUBTIME functions.

Applies To

The DATE_SUB function can be used in the following versions of MySQL:

MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Example

Let's look at some MySQL DATE_SUB function examples and explore how to use the DATE_SUB function in MySQL.

For example:

mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL 4 MICROSECOND);
Output: '2014-02-13 08:44:20.999997'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL 20 SECOND);
Output: '2014-02-13 08:44:01'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL 25 MINUTE);
Output: '2014-02-13 08:19:21'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL 2 HOUR);
Output: '2014-02-13 06:44:21'

mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 10 DAY);
Output: '2014-02-03'

mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 12 WEEK);
Output: '2013-11-21'

mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 3 MONTH);
Output: '2013-11-13'

mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 3 QUARTER);
Output: '2013-05-13'

mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 5 YEAR);
Output: '2009-02-13'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '12.000001' SECOND_MICROSECOND);
Output: '2014-02-13 08:44:09'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '3:12.000001' MINUTE_MICROSECOND);
Output: '2014-02-13 08:41:09'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '3:12' MINUTE_SECOND);
Output: '2014-02-13 08:41:09'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '1:03:12.000001' HOUR_MICROSECOND);
Output: '2014-02-13 07:41:09'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '1:03:12' HOUR_SECOND);
Output: '2014-02-13 07:41:09'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '1:03' HOUR_MINUTE);
Output: '2014-02-13 07:41:21'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '7 1:03:12.000001' DAY_MICROSECOND);
Output: '2014-02-06 07:41:09'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '7 1:03:12' DAY_SECOND);
Output: '2014-02-06 07:41:09'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '7 1:03' DAY_MINUTE);
Output: '2014-02-06 07:41:21'

mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '7 1' DAY_HOUR);
Output: '2014-02-06 07:44:21'

mysql> SELECT DATE_SUB('2014-02-13', INTERVAL '5-3' YEAR_MONTH);
Output: '2008-11-13'

Mysql Date_sub Function