Summary: in this tutorial, you will learn how to subtract a time from a date using the MySQL DATE_SUB()
function.
Introduction to MySQL DATE_SUB() function
The DATE_SUB()
function subtracts a time value (or an interval) from a DATE
or DATETIME
value.
Here’s the syntax of the DATE_SUB()
function:
DATE_SUB(date,INTERVAL expr unit)
Code language: SQL (Structured Query Language) (sql)
The DATE_SUB()
function accepts two arguments:
-
date
: This is the date that you want to subtract a value. -
expr
: This is a string that determines an interval value that you want to subtract from the date. Theunit
is the interval unit thatexpr
should be interpreted e.g.,DAY
,HOUR
, etc.
The DATE_SUB()
function returns NULL if the date is NULL.
The following statement uses the DATE_SUB()
function to subtract one day from the July-4th-2017
:
SELECT DATE_SUB('2017-07-04',INTERVAL 1 DAY) result;
Code language: SQL (Structured Query Language) (sql)
Output:
+------------+
| result |
+------------+
| 2017-07-03 |
+------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
In this example:
- The date is
2017-07-04
, which is in theyyyy-mm-dd
format. - The
INTERVAL 1 DAY
is interpreted as 1 day interval.
The DATE_SUB()
function returns a string value that represents the date July, 3rd 2017
Similar to the DATE_ADD()
function, the data type of the return value of the DATE_SUB()
function can be:
- a
DATETIME
value if the first argument is aDATETIME
or the interval has time elements such as the hour, minute, second, etc. - a string otherwise.
See the following example:
SELECT DATE_SUB('2017-07-04',INTERVAL 3 HOUR) result;
Code language: SQL (Structured Query Language) (sql)
Output:
+---------------------+
| result |
+---------------------+
| 2017-07-03 21:00:00 |
+---------------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
Because the interval is 3 hours, the result of the DATE_SUB
function is a DATETIME
value.
Negative interval
The expr
in the interval can be positive or negative. In case the expr
is negative, the DATE_SUB()
function behaves like the DATE_ADD()
function as shown in the following example:
SELECT DATE_SUB('2017-07-03',INTERVAL -1 DAY) result;
Code language: SQL (Structured Query Language) (sql)
Output:
+------------+
| result |
+------------+
| 2017-07-04 |
+------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
Invalid or malformed date
If the first argument of the DATE_SUB()
function is a malformed, invalid date, or NULL
, the DATE_SUB()
function returns NULL
.
SELECT DATE_SUB('2017-02-29', INTERVAL - 1 DAY) result;
Code language: SQL (Structured Query Language) (sql)
Output:
+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)
In this example, 2017-02-03
is an invalid date, therefore, the result is NULL
. In addition, MySQL produced a warning.
SHOW WARNINGS;
Output:
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2017-02-29' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
The following examples demonstrate the effects when passing a malformed date or NULL
to the DATE_SUB
function:
SELECT DATE_SUB('03/07/2017', INTERVAL 1 DAY) result;
Code language: JavaScript (javascript)
Output:
+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set, 1 warning (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
SELECT DATE_SUB(NULL, INTERVAL 1 DAY) result;
Code language: PHP (php)
Output:
+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
MySQL DATE_SUB: automatically adjusted day
If you subtract MONTH
, YEAR
, or YEAR_MONTH
from a date and the new date has a day that’s too big for the new month, the DATE_SUB()
will adjust the day to the latest day of the new month. For example:
SELECT DATE_SUB('2017-03-30', INTERVAL 1 MONTH) result;
Code language: SQL (Structured Query Language) (sql)
Output:
+------------+
| result |
+------------+
| 2017-02-28 |
+------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
In this example, we subtracted 1 month from March 30th 2017
, therefore, the result is February 28th 2017
.
However, the DATE_SUB()
function adjusted the day to 28 instead of 30 because February 2017
has 28 days only.
Summary
- Use the MySQL
DATE_SUB()
function to subtract an interval from aDATE
orDATETIME
value.