MySQL Cookbook

Shifting Dates by a Known Amount

Problem

You want to shift a given date by a given amount to compute the resulting date.

Solution

Use DATE_ADD( ) or DATE_SUB( ).

Discussion

If you have a reference date and want to calculate another date from it that differs by a known interval, the problem generally can be solved by basic date arithmetic using DATE_ADD( ) and DATE_SUB( ). Some examples of this kind of question include finding anniversary dates, determining expiration dates, or finding records that satisfy “this date in history” queries. This section illustrates a couple of applications for date shifting.

Calculating Anniversary Dates

Suppose you’re getting married on August 6, 2003, and you don’t want to wait a year for your first anniversary to show your devotion to your sweetheart. Instead, you want to get her special gifts on your 1 week, 1 month, 3 month, and 6 month anniversaries. To calculate those dates, shift your anniversary date forward by the desired intervals, as follows:

mysql> SET @d = '2003-08-06';
mysql> SELECT @d AS 'start date',
    -> DATE_ADD(@d,INTERVAL 7 DAY) AS '1 week',
    -> DATE_ADD(@d,INTERVAL 1 MONTH) AS '1 month',
    -> DATE_ADD(@d,INTERVAL 3 MONTH) AS '3 months',
    -> DATE_ADD(@d,INTERVAL 6 MONTH) AS '6 months'; +------------+------------+------------+------------+------------+ | start date | 1 week | 1 month | 3 months | 6 months | +------------+------------+------------+------------+------------+ | 2003-08-06 | 2003-08-13 | 2003-09-06 | 2003-11-06 | 2004-02-06 | +------------+------------+------------+------------+------------+ ...