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:
|
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:
EmpID | Name | Login Stamp | Expected Logout Stamp |
---|---|---|---|
1 | John | 2019-10-25 09:20:38 | 2019-10-25 17:50:38 |
2 | Marry | 2019-10-25 09:21:05 | 2019-10-25 17:51:05 |
3 | Jo | 2019-10-25 09:24:35 | 2019-10-25 17:54:35 |
4 | Kim | 2019-10-25 09:25:24 | 2019-10-25 17:55:24 |
5 | Ramesh | 2019-10-25 09:27:16 | 2019-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(), SUBDATE(NOW(), INTERVAL '-8:30' HOUR_MINUTE)); -- see the result SELECT * FROM EmployeeLogin;
This will produce a result similar to:
EmpID | Name | Login Stamp | Expected Logout Stamp |
---|---|---|---|
1 | John | 2019-10-25 09:20:38 | 2019-10-25 17:50:38 |
2 | Marry | 2019-10-25 09:21:05 | 2019-10-25 17:51:05 |
3 | Jo | 2019-10-25 09:24:35 | 2019-10-25 17:54:35 |
4 | Kim | 2019-10-25 09:25:24 | 2019-10-25 17:55:24 |
5 | Ramesh | 2019-10-25 09:27:16 | 2019-10-25 17:57:16 |
6 | Suresh | 2019-10-25 09:28:19 | 2019-10-25 17:58:19 |
❮ MySQL Functions