How do I perform date calculations using MySQL?

[chinmay]

When performing queries, it’s not uncommon to find the need for date range specification. You may, for example, need to retrieve all blog posts created within the last 30 days. Date calculations are a breeze in MySQL; let’s have a look at them.

Solution

You can perform complex date math using the MySQL date functions. We can add and subtract time intervals that are identified using the INTERVAL keyword via the DATE_ADD and DATE_SUB functions. Thus, we use DATE_ADD to add one day:

mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
+---------------------------------+
| DATE_ADD(NOW(), INTERVAL 1 DAY) |
+---------------------------------+
| 2007-10-09 21:32:20 |
+---------------------------------+
Likewise, we use DATE_SUB to subtract one day:
mysql> SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);
+---------------------------------+
| DATE_SUB(NOW(), INTERVAL 1 DAY) |
+---------------------------------+
| 2007-10-07 21:32:26 |
+---------------------------------+
We can also add or subtract months and years:
mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);
+-----------------------------------+
| DATE_ADD(NOW(), INTERVAL 1 MONTH) |
+-----------------------------------+
| 2007-11-08 21:31:05 |
+-----------------------------------+
mysql> SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
+-----------------------------------+
| DATE_SUB(NOW(), INTERVAL 1 MONTH) |
+-----------------------------------+
| 2007-09-08 21:31:55 |
+-----------------------------------+
mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
+----------------------------------+
| DATE_ADD(NOW(), INTERVAL 1 YEAR) |
+----------------------------------+
| 2008-10-08 21:32:31 |
+----------------------------------+
mysql> SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);
+----------------------------------+
| DATE_SUB(NOW(), INTERVAL 1 YEAR) |
+----------------------------------+
| 2006-10-08 21:32:37 |
+----------------------------------+

We can use more human-friendly terms when writing SQL queries in MySQL—such as 1 DAY, 1 MONTH, and 1 YEAR—than when we deal with Unix timestamps, which are measured in milliseconds. With MySQL, we can use the DATE_SUBand DATE_ADD functions to retrieve database records within a certain date range. Here, we get all the data with an updated_date within the last 30 days:

SELECT * FROM my_table WHERE
➥ DATE_SUB(NOW(), INTERVAL 30 DAYS) >= updated_date;

Similarly, the following will yield the rows with an updated_datethat’s more than one week old, but no more than 14 days old:

SELECT * FROM my_table WHERE
➥
updated_date BETWEEN(DATE_SUB(NOW(), INTERVAL 14 DAYS),
➥
DATE_SUB(NOW(), INTERVAL 7 DAYS);

[mikechan12]

Wow, I'm looking for this for years