Database Reference
In-Depth Information
Do You Want an Interval or a Span?
When you take a difference between dates (or times), consider whether you want an
interval or a span. Taking a difference between dates gives you the interval from one
date to the next. To determine the range spanned by the two dates, you must add a unit.
For example, it's a three-day interval from
2002-01-01
to
2002-01-04
, but together they
span a range of four days. If you don't get the results you expect from a difference-of-
values calculation, consider whether an “off-by-one” correction is needed.
6.12. 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 applicable functions or operators depend on the types of the values.
Discussion
The following discussion shows several ways to add temporal values.
Adding temporal values using temporal-addition functions or operators
To add a time to a time or date-and-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);
+---------------------+
