Database Reference
In-Depth Information
->
TIMESTAMPDIFF(YEAR,@dt1,@dt2) AS years;
+---------+-------+------+-------+-------+
| minutes | hours | days | weeks | years |
+---------+-------+------+-------+-------+
| 5258880 | 87648 | 3652 | 521 | 10 |
+---------+-------+------+-------+-------+
Permitted
unit
specifiers are
MICROSECOND
,
SECOND
,
MINUTE
,
HOUR
,
DAY
,
WEEK
,
MONTH
,
QUARTER
, or
YEAR
. Note that each is singular, not plural.
Be aware of these properties of
TIMESTAMPDIFF()
:
• Its value is negative if the first temporal value is greater than the second, which is
opposite the order of the arguments for
DATEDIFF()
and
TIMEDIFF()
.
• Despite the
TIMESTAMP
in its name,
TIMESTAMPDIFF()
arguments are not limited to
the range of the
TIMESTAMP
data type.
Calculating intervals using basic units
Another strategy for calculating intervals is to work with basic units such as seconds or
days:
1. Convert your temporal values to basic units.
2. Take the difference between the values to calculate the interval, also in basic units.
3. If you want the result as a temporal value, convert it from basic units to the appro‐
priate type.
The conversion functions involved in implementing this strategy depend on the types
of the values between which you calculate the interval:
• To convert between time values and seconds, use
TIME_TO_SEC()
and
SEC_TO_TIME()
.
• To convert between date values and days, use
TO_DAYS()
and
FROM_DAYS()
.
• To convert between date-and-time values and seconds, use
UNIX_TIMESTAMP()
and
FROM_UNIXTIME()
.
Recipe 6.10
discusses those conversion functions (and limitations on their applicability).
The following material assumes familiarity with that discussion.
Time interval calculation using basic units
To calculate intervals in seconds between pairs of time values, convert them to seconds
with
TIME_TO_SEC()
and take the difference. To express the resulting interval as a
TIME
value, pass it to
SEC_TO_TIME()
. The following statement calculates the intervals be‐
