How MySQL performs date arithmetic with addition and subtraction operators?

MySQLMySQLi Database

MySQL can perform date arithmetic with addition and subtraction operators by adding together INTERVAL keyword with a unit of time, date or datetime.

Example1

Adding 2 days to a particular date.

mysql> Select '2017-05-20' + INTERVAL 2 day;
+-------------------------------+
| '2017-05-20' + INTERVAL 2 day |
+-------------------------------+
| 2017-05-22                    |
+-------------------------------+
1 row in set (0.00 sec)

Example2

Subtracting 2 days from a particular date.

mysql> Select '2017-05-20' - INTERVAL 2 day;
+-------------------------------+
| '2017-05-20' - INTERVAL 2 day |
+-------------------------------+
| 2017-05-18                    |
+-------------------------------+
1 row in set (0.00 sec)

Example 3

Adding 2 hours in time.

mysql> Select '2017-05-20 05:04:35' + INTERVAL 3 hour;
+-----------------------------------------+
| '2017-05-20 05:04:35' + INTERVAL 3 hour |
+-----------------------------------------+
| 2017-05-20 08:04:35                     |
+-----------------------------------------+
1 row in set (0.00 sec)

Example 4

Adding one month to a particular date

mysql> Select '2017-05-20 05:04:35' + INTERVAL 1 month;
+------------------------------------------+
| '2017-05-20 05:04:35' + INTERVAL 1 month |
+------------------------------------------+
| 2017-06-20 05:04:35                      |
+------------------------------------------+
1 row in set (0.00 sec)

In this way with the help of INTERVAL keyword, we can perform date arithmetic.

raja
Updated on 19-Jun-2020 13:45:14

Advertisements