Database Reference
In-Depth Information
| ADDTIME(@dt,@t) |
+---------------------+
| 1984-03-02 00:00:00 |
+---------------------+
To add a time to a date or date-and-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) |
+---------------------+
| 1984-03-02 00:00:00 |
+---------------------+
MySQL also provides
DATE_ADD()
and
DATE_SUB()
functions for adding intervals to
dates and subtracting intervals from dates. Each function takes a date (or date-and-time)
value
d
and an interval, expressed using the following syntax:
DATE_ADD(d,INTERVAL
val unit
)
DATE_SUB(d,INTERVAL
val unit
)
The
+
INTERVAL
and
-
INTERVAL
operators are similar:
d + INTERVAL
val unit
d - INTERVAL
val unit
unit
is the interval unit and
val
is an expression indicating the number of units. Some
common unit specifiers are
SECOND
,
MINUTE
,
HOUR
,
DAY
,
MONTH
, and
YEAR
. Note that each
is singular, not plural. (Check the
MySQL Reference Manual
for the full list.)
Use
DATE_ADD()
or
DATE_SUB()
to perform date arithmetic operations such as these:
• Determine the date three days from today:
mysql>
SELECT CURDATE(), DATE_ADD(CURDATE(),INTERVAL 3 DAY);
+------------+------------------------------------+
| CURDATE() | DATE_ADD(CURDATE(),INTERVAL 3 DAY) |
+------------+------------------------------------+
| 2014-02-20 | 2014-02-23 |
+------------+------------------------------------+
• Find the date a week ago:
mysql>
SELECT CURDATE(), DATE_SUB(CURDATE(),INTERVAL 1 WEEK);
+------------+-------------------------------------+
| CURDATE() | DATE_SUB(CURDATE(),INTERVAL 1 WEEK) |
+------------+-------------------------------------+
