MySQL Cookbook

5.21 Shifting Dates by a Known Amount

5.21.1 Problem

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

5.21.2 Solution

Use DATE_ADD( ) or DATE_SUB( ).

5.21.3 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.

5.21.4 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 |
+------------+------------+------------+------------+------------+

If you're interested only in part of an anniversary date, you may be able to dispense with date arithmetic altogether. For example, if you graduated from school on June 4, 2000, and you want to know the years on which your 10th, 20th, and 40th class reunions will be, it's unnecessary to use DATE_ADD( ). Just extract the year part of the reference date and use normal arithmetic to add 10, 20, and 40 to it:

mysql> SET @y = YEAR('2000-06-04');
mysql> SELECT @y + 10, @y + 20, @y + 40;
+---------+---------+---------+
| @y + 10 | @y + 20 | @y + 40 |
+---------+---------+---------+
|    2010 |    2020 |    2040 |
+---------+---------+---------+

5.21.5 Time Zone Adjustments

A MySQL server returns dates using the time zone of the host on which the server runs. If you're running a client program in a different time zone, you can adjust values to client local time with DATE_ADD( ). To convert times for a server that is two hours ahead of the client, subtract two hours:

mysql> SELECT dt AS 'server time',
    -> DATE_ADD(dt,INTERVAL -2 HOUR) AS 'client time'
    -> FROM datetime_val;
+---------------------+---------------------+
| server time         | client time         |
+---------------------+---------------------+
| 1970-01-01 00:00:00 | 1969-12-31 22:00:00 |
| 1987-03-05 12:30:15 | 1987-03-05 10:30:15 |
| 1999-12-31 09:00:00 | 1999-12-31 07:00:00 |
| 2000-06-04 15:45:30 | 2000-06-04 13:45:30 |
+---------------------+---------------------+

Note that the server has no idea what time zone the client is in, so you are responsible for determining the amount of shift between the client and the server time zones. Within a script, you may be able to do this by getting the current local time and comparing it to the server's idea of its local time. In Perl, the localtime( ) function comes in handy for this:

my ($sec, $min, $hour, $day, $mon, $year) = localtime (time ( ));
my $now = sprintf ("%04d-%02d-%02d %02d:%02d:%02d",
                        $year + 1900, $mon + 1, $day, $hour, $min, $sec);
my ($server_now, $adjustment) = $dbh->selectrow_array (
        "SELECT NOW( ), UNIX_TIMESTAMP(?) - UNIX_TIMESTAMP(NOW( ))",
        undef, $now);
print "client now: $now\n";
print "server now: $server_now\n";
print "adjustment (secs): $adjustment\n";