MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL SUBDATE() Function



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

This function (version 1 syntax) is a synonym for the DATE_SUB() function.

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

Syntax

/* version 1 */
SUBDATE(datetime, INTERVAL value unit)

/* version 2 */
SUBDATE(datetime, days)

Parameters

datetime Required. Specify a date or datetime value to be modified.
days Required. Specify the number of days to subtract from the datetime. Both positive and negative values are allowed.
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 SUBDATE() function.

mysql> SELECT SUBDATE('2019-10-25', 10);
Result: '2019-10-15'

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

mysql> SELECT SUBDATE('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 query can be used.

INSERT INTO EmployeeLogin 
VALUES (6, 'Suresh', NOW(), SUBDATE(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

5