MySQL - DATE_SUB() Function

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL DATE_SUB() function is used to subtracts the specified interval to a date value.

Syntax

Following is the syntax of the above function –

DATE_SUB(date, INTERVAL expr unit);

where,

  • date is the value representing the date it can be of the type String, DATE (YEAR, MONTH, and DAY), DATETIME (HOURS, MINUTES or, SECONDS) or, TIMESTAMP.

  • expr is the value representing the interval value.

  • unit is the interval type represented by the expr value which can be DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND.

The unit can be mixed values as − SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.

Example 1

Following example demonstrates the usage of the DATE_SUB() function –

mysql> SELECT DATE_SUB('2015-09-05', INTERVAL 20 DAY);
+-----------------------------------------+
| DATE_SUB('2015-09-05', INTERVAL 20 DAY) |
+-----------------------------------------+
|                              2015-08-16 |
+-----------------------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT DATE_SUB('2008-01-02', INTERVAL 4 YEAR);
+-----------------------------------------+
| DATE_SUB('2008-01-02', INTERVAL 4 YEAR) |
+-----------------------------------------+
|                              2004-01-02 |
+-----------------------------------------+
1 row in set (0.00 sec)

Example 3

In the following example we are passing DATETIME value for date –

mysql> SELECT DATE_SUB('2018-05-23 20:40:32.88558', INTERVAL 3 WEEK);
+--------------------------------------------------------+
| DATE_SUB('2018-05-23 20:40:32.88558', INTERVAL 3 WEEK) |
+--------------------------------------------------------+
|                             2018-05-02 20:40:32.885580 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Example 4

We can also pass negative values as arguments to this function –

mysql> SELECT DATE_SUB('2015-09-05', INTERVAL -20 DAY);
+------------------------------------------+
| DATE_SUB('2015-09-05', INTERVAL -20 DAY) |
+------------------------------------------+
|                               2015-09-25 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('1995-11-15', INTERVAL -3355 WEEK);
+---------------------------------------------+
| DATE_SUB('1995-11-15', INTERVAL -3355 WEEK) |
+---------------------------------------------+
|                                  2060-03-03 |
+---------------------------------------------+
1 row in set (0.00 sec)

Example 5

You can also pass the column name as an argument to this function. Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql>CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query subtracts 14 years to the entities of the Date_Of_Birth column —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country,
DATE_SUB(Date_Of_Birth, INTERVAL 14 YEAR) FROM MyPlayers;
+------------+------------+---------------+-------------+-------------------------------------------+
| First_Name |  Last_Name | Date_Of_Birth |     Country | DATE_SUB(Date_Of_Birth, INTERVAL 14 YEAR) |
+------------+------------+---------------+-------------+-------------------------------------------+
|    Shikhar |     Dhawan |    1981-12-05 |       India |                                1967-12-05 |
|   Jonathan |      Trott |    1981-04-22 | SouthAfrica |                                1967-04-22 |
|     Kumara | Sangakkara |    1977-10-27 |    Srilanka |                                1963-10-27 |
|      Virat |      Kohli |    1988-11-05 |       India |                                1974-11-05 |
|      Rohit |     Sharma |    1987-04-30 |       India |                                1973-04-30 |
|    Ravindra|     Jadeja |    1988-12-06 |       India |                                1974-12-06 |
|      James |   Anderson |    1982-06-30 |     England |                                1968-06-30 |
+------------+------------+---------------+-------------+-------------------------------------------+
7 rows in set (0.00 sec)

Example 6

Let us create another table with name Sales in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DispatchTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in Sales table using INSERT statements −

insert into sales values (1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following query subtracts 25 QUARTER to the entities of the DispatchDate column —

mysql> SELECT ProductName, CustomerName, DispatchDate, Price,
DATE_SUB(DispatchDate, INTERVAL -25 QUARTER) FROM sales;
+-------------+--------------+--------------+-------+----------------------------------------------+
| ProductName | CustomerName | DispatchDate | Price | DATE_SUB(DispatchDate, INTERVAL -25 QUARTER) |
+-------------+--------------+--------------+-------+----------------------------------------------+
|   Key-Board |         Raja |   2019-09-01 |  7000 |                                   2025-12-01 |
|   Earphones |         Roja |   2019-05-01 |  2000 |                                   2025-08-01 |
|       Mouse |         Puja |   2019-03-01 |  3000 |                                   2025-06-01 |
|      Mobile |       Vanaja |   2019-03-01 |  9000 |                                   2025-06-01 |
|     Headset |       Jalaja |   2019-04-06 |  6000 |                                   2025-07-06 |
+-------------+--------------+--------------+-------+----------------------------------------------+
5 rows in set (0.00 sec)

Example 7

Suppose we have created a table named dispatches_data with 5 records in it using the following queries –

mysql> CREATE TABLE dispatches_data(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp timestamp,
	Price INT,
	Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');

Following query subtracts 365 days to the dates of the DispatchTimeStamp column —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price,
DATE_SUB(DispatchTimeStamp, INTERVAL 365 DAY) FROM dispatches_data;
+-------------+--------------+---------------------+-------+-----------------------------------------------+
| ProductName | CustomerName |   DispatchTimeStamp | Price | DATE_SUB(DispatchTimeStamp, INTERVAL 365 DAY) |
+-------------+--------------+---------------------+-------+-----------------------------------------------+
|   Key-Board |         Raja | 2019-05-04 15:02:45 |  7000 |                           2018-05-04 15:02:45 |
|   Earphones |         Roja | 2019-06-26 14:13:12 |  2000 |                           2018-06-26 14:13:12 |
|       Mouse |         Puja | 2019-12-07 07:50:37 |  3000 |                           2018-12-07 07:50:37 |
|      Mobile |       Vanaja | 2018-03-21 16:00:45 |  9000 |                           2017-03-21 16:00:45 |
|     Headset |       Jalaja | 2018-12-30 10:49:27 |  6000 |                           2017-12-30 10:49:27 |
+-------------+--------------+---------------------+-------+-----------------------------------------------+
5 rows in set (0.00 sec)

Example 8

Following example demonstrates the usage of various units available in the DATE_SUB() function –

mysql> SELECT DATE_SUB('2021-03-22', INTERVAL '5-6 ' YEAR_MONTH );
+-----------------------------------------------------+
| DATE_SUB('2021-03-22', INTERVAL '5-6 ' YEAR_MONTH ) |
+-----------------------------------------------------+
|                                          2015-09-22 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2021-03-22', INTERVAL '3' WEEK );
+--------------------------------------------+
| DATE_SUB('2021-03-22', INTERVAL '3' WEEK ) |
+--------------------------------------------+
|                                 2021-03-01 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2021-03-22', INTERVAL '3' QUARTER );
+-----------------------------------------------+
| DATE_SUB('2021-03-22', INTERVAL '3' QUARTER ) |
+-----------------------------------------------+
|                                    2020-06-22 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2021-03-22', INTERVAL '10:09.2362191' MINUTE_MICROSECOND);
+---------------------------------------------------------------------+
| DATE_SUB('2021-03-22', INTERVAL '10:09.2362191' MINUTE_MICROSECOND) |
+---------------------------------------------------------------------+
|                                          2021-03-21 23:49:48.637809 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2021-03-22', INTERVAL '10.2362191' SECOND_MICROSECOND);
+------------------------------------------------------------------+
| DATE_SUB('2021-03-22', INTERVAL '10.2362191' SECOND_MICROSECOND) |
+------------------------------------------------------------------+
|                                       2021-03-21 23:59:47.637809 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2021-03-22', INTERVAL '10.21' MINUTE_SECOND);
+--------------------------------------------------------+
| DATE_SUB('2021-03-22', INTERVAL '10.21' MINUTE_SECOND) |
+--------------------------------------------------------+
|                                    2021-03-21 23:49:39 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql-date-time-functions.htm
Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements