MySQL Cookbook, 2nd Edition

Adding Date or Time Values

Problem

You want to add temporal values. For example, you want to add a given number of seconds to a time or determine what the date will be three weeks from today.

Solution

To add date or time values, you have several options:

• Use one of the temporal-addition functions.

• Use the + INTERVAL or - INTERVAL operator.

• Convert the values to basic units, and take the sum.

The allowable functions or operators depend on the types of the values that you want to add.

Discussion

The following discussion shows several ways to add temporal values.

Adding temporal values using temporal-addition functions or operators

To add a time or date-and-time value and a time value, use the ADDTIME() function:

mysql>SET @t1 = '12:00:00', @t2 = '15:30:00';
mysql> SELECT ADDTIME(@t1,@t2);
+------------------+
| ADDTIME(@t1,@t2) |
+------------------+
| 27:30:00         |
+------------------+
mysql> SET @dt = '1984-03-01 12:00:00', @t = '12:00:00';
mysql> SELECT ADDTIME(@dt,@t);
+---------------------+
| ADDTIME(@dt,@t)     |
+---------------------+
| 1984-03-02 00:00:00 |
+---------------------+

To add a date or date-and-time value and a time value, use the TIMESTAMP() function:

mysql>SET @d = '1984-03-01', @t = '15:30:00';
mysql> SELECT TIMESTAMP(@d,@t);
+---------------------+
| TIMESTAMP(@d,@t)    |
+---------------------+
| 1984-03-01 15:30:00 |
+---------------------+
mysql> SET @dt = '1984-03-01 12:00:00', @t = '12:00:00';
mysql> SELECT TIMESTAMP(@dt,@t); +---------------------+ | TIMESTAMP(@dt,@t) | +---------------------+ ...