SQL Tutorial SQL Advanced SQL Database SQL References

MySQL DATE_SUB() Function



The MySQL DATE_SUB() function subtracts a specified time/date interval from a date/datetime value and then returns the modified date/datetime value.

Using this function with a negative value as a parameter is equivalent to using the DATE_ADD() function.

Syntax

DATE_SUB(datetime, INTERVAL value unit)

Parameters

datetime Required. Specify a date or datetime value to be modified.
value Required. Specify the value of the time/date interval to subtract. Both positive and negative values are allowed.
unit Required. Specify the unit type of interval to subtract. It can be one of the following values:
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

Return Value

Returns the modified date/datetime value.

Example 1:

The example below shows the usage of DATE_SUB() function.

mysql> SELECT DATE_SUB('2019-10-25', INTERVAL 10 DAY);
Result: '2019-10-15'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL 2 HOUR);
Result: '2019-10-25 06:10:25'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL -2 HOUR);
Result: '2019-10-25 10:10:25'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL 30 MINUTE);
Result: '2019-10-25 07:40:25'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL -10 SECOND);
Result: '2019-10-25 08:10:35'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL 1 WEEK);
Result: '2019-10-18 08:10:25'

mysql> SELECT DATE_SUB('2019-10-25', INTERVAL -2 MONTH);
Result: '2019-12-25'

mysql> SELECT DATE_SUB('2019-10-25', INTERVAL 2 QUARTER);
Result: '2019-04-25'

mysql> SELECT DATE_SUB('2019-10-25', INTERVAL 2 YEAR);
Result: '2017-10-25'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL 10 MICROSECOND);
Result: '2019-10-25 08:10:24.999990'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '5.0000010' SECOND_MICROSECOND);
Result: '2019-10-25 08:10:19.999990'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '10:5.0000010' MINUTE_MICROSECOND);
Result: '2019-10-25 08:00:19.999990'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '10:5' MINUTE_SECOND);
Result: '2019-10-25 08:00:20'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '2:10:5.0000010' HOUR_MICROSECOND);
Result: '2019-10-25 06:00:19.999990'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '2:10:5' HOUR_SECOND);
Result: '2019-10-25 06:00:20'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '2:10' HOUR_MINUTE);
Result: '2019-10-25 06:00:25'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '3 2:10:5.0000010' DAY_MICROSECOND);
Result: '2019-10-22 06:00:19.999990'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '3 2:10:5' DAY_SECOND);
Result: '2019-10-22 06:00:20'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '3 2:10' DAY_MINUTE);
Result: '2019-10-22 06:00:25'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '3 2' DAY_HOUR);
Result: '2019-10-22 06:10:25'

mysql> SELECT DATE_SUB('2019-10-25 08:10:25', INTERVAL '2 5' YEAR_MONTH);
Result: '2017-05-25 08:10:25'

Example 2:

Consider a database table called EmployeeLogin with the following records:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:50:38
2Marry2019-10-25 09:21:052019-10-25 17:51:05
3Jo2019-10-25 09:24:352019-10-25 17:54:35
4Kim2019-10-25 09:25:242019-10-25 17:55:24
5Ramesh2019-10-25 09:27:162019-10-25 17:57:16

To insert a new record in this table, the following statement can be used.

INSERT INTO EmployeeLogin 
VALUES (6, 'Suresh', NOW(), DATE_SUB(NOW(), INTERVAL '-8:30' HOUR_MINUTE));

-- see the result
SELECT * FROM EmployeeLogin;

This will produce a result similar to:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:50:38
2Marry2019-10-25 09:21:052019-10-25 17:51:05
3Jo2019-10-25 09:24:352019-10-25 17:54:35
4Kim2019-10-25 09:25:242019-10-25 17:55:24
5Ramesh2019-10-25 09:27:162019-10-25 17:57:16
6Suresh2019-10-25 09:28:192019-10-25 17:58:19

❮ MySQL Functions