MySQL: DATE_SUB Function
This MySQL 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
- The date to which the interval should be subtracted.
- value
- The value of the time/date interval that you wish to subtract. You can specify positive and negative values for this parameter.
- unit
The unit type of the interval such as DAY, MONTH, MINUTE, HOUR, and so on. It can be one of the following:
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); Result: '2014-02-13 08:44:20.999997' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL 20 SECOND); Result: '2014-02-13 08:44:01' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL 25 MINUTE); Result: '2014-02-13 08:19:21' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL 2 HOUR); Result: '2014-02-13 06:44:21' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 10 DAY); Result: '2014-02-03' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 12 WEEK); Result: '2013-11-21' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 3 MONTH); Result: '2013-11-13' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 3 QUARTER); Result: '2013-05-13' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL 5 YEAR); Result: '2009-02-13' mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '12.000001' SECOND_MICROSECOND); Result: '2014-02-13 08:44:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '3:12.000001' MINUTE_MICROSECOND); Result: '2014-02-13 08:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '3:12' MINUTE_SECOND); Result: '2014-02-13 08:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21.000001', INTERVAL '1:03:12.000001' HOUR_MICROSECOND); Result: '2014-02-13 07:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '1:03:12' HOUR_SECOND); Result: '2014-02-13 07:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '1:03' HOUR_MINUTE); Result: '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); Result: '2014-02-06 07:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '7 1:03:12' DAY_SECOND); Result: '2014-02-06 07:41:09' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '7 1:03' DAY_MINUTE); Result: '2014-02-06 07:41:21' mysql> SELECT DATE_SUB('2014-02-13 08:44:21', INTERVAL '7 1' DAY_HOUR); Result: '2014-02-06 07:44:21' mysql> SELECT DATE_SUB('2014-02-13', INTERVAL '5-3' YEAR_MONTH); Result: '2008-11-13'
Advertisements