You want to know how long it is between two dates or times. That is, you want to know the interval between two temporal values.
To calculate an interval, either use one of the temporal-difference functions, or convert your values to basic units and take the difference. The allowable functions depend on the types of the values for which you want to know the interval.
The following discussion shows several ways to perform interval calculations.
To calculate an interval in days between two date values, use
the
DATEDIFF()
function:
mysql>SET @d1 = '2010-01-01', @d2 = '2009-12-01';
mysql>SELECT DATEDIFF(@d1,@d2) AS 'd1 - d2', DATEDIFF(@d2,@d1) AS 'd2 - d1';
+---------+---------+ | d1 - d2 | d2 - d1 | +---------+---------+ | 31 | -31 | +---------+---------+
DATEDIFF()
also works
with date-and-time values, but it ignores the time part. This makes
it suitable for producing day intervals for DATE
, DATETIME
, or TIMESTAMP
values.
To calculate an interval between TIME
values as another TIME
value, use the
TIMEDIFF()
function:
mysql>SET @t1 = '12:00:00', @t2 = '16:30:00';
mysql>SELECT TIMEDIFF(@t1,@t2) AS 't1 - t2', TIMEDIFF(@t2,@t1) AS 't2 - t1';
+-----------+----------+ | t1 - t2 | t2 - t1 | +-----------+----------+ | -04:30:00 | 04:30:00 | +-----------+----------+
TIMEDIFF()
also works
for date-and-time values. That it, it accepts either time or
date-and-time values, but the types of the arguments must
match.
A time interval expressed as a TIME
value can be broken down into
components using the techniques shown in Extracting Parts of Dates or Times. For example, to express a time
interval in terms of its constituent hours, minutes, and seconds
values, calculate time interval subparts in SQL using the
HOUR()
,
MINUTE()
,
and SECOND()
functions. (Don’t forget that if your intervals may be negative, you
need to take that into account.) To determine the components of the
interval between the t1
and
t2
columns in the time_val
table, the following SQL
statement does the trick:
mysql>SELECT t1, t2,
->TIMEDIFF(t2,t1) AS 't2 - t1 as TIME',
->IF(TIMEDIFF(t2,t1) >= 0,'+','-') AS sign,
->HOUR(TIMEDIFF(t2,t1)) AS hour,
->MINUTE(TIMEDIFF(t2,t1)) AS minute,
->SECOND(TIMEDIFF(t2,t1)) AS second
->FROM time_val;
+----------+----------+-----------------+------+------+--------+--------+ | t1 | t2 | t2 - t1 as TIME | sign | hour | minute | second | +----------+----------+-----------------+------+------+--------+--------+ | 15:00:00 | 15:00:00 | 00:00:00 | + | 0 | 0 | 0 | | 05:01:30 | 02:30:20 | -02:31:10 | - | 2 | 31 | 10 | | 12:30:20 | 17:30:45 | 05:00:25 | + | 5 | 0 | 25 | +----------+----------+-----------------+------+------+--------+--------+
If you’re working with date or date-and-time values,
the
TIMESTAMPDIFF()
function provides
another way to calculate intervals, and it enables you to specify
the units in which intervals should be expressed. It has this
syntax:
TIMESTAMPDIFF(unit
,val1
,val2
)
unit
is the interval unit and
val1
and val2
are the values between which to calculate the interval. With
TIMESTAMPDIFF()
, you can
express an interval many different ways:
mysql>SET @dt1 = '1900-01-01 00:00:00', @dt2 = '1910-01-01 00:00:00';
mysql>SELECT
->TIMESTAMPDIFF(MINUTE,@dt1,@dt2) AS minutes,
->TIMESTAMPDIFF(HOUR,@dt1,@dt2) AS hours,
->TIMESTAMPDIFF(DAY,@dt1,@dt2) AS days,
->TIMESTAMPDIFF(WEEK,@dt1,@dt2) AS weeks,
->TIMESTAMPDIFF(YEAR,@dt1,@dt2) AS years;
+---------+-------+------+-------+-------+ | minutes | hours | days | weeks | years | +---------+-------+------+-------+-------+ | 5258880 | 87648 | 3652 | 521 | 10 | +---------+-------+------+-------+-------+
The allowable unit
specifiers are
FRAC_SECOND
, SECOND
, MINUTE
, HOUR
, DAY
, WEEK
, MONTH
, QUARTER
, or YEAR
. Note that each of these unit
specifiers is given in singular form, not plural.
Be aware of these properties of TIMESTAMPDIFF()
:
Its value is negative if the first temporal value is
greater then the second, which is opposite the order of the
arguments for DATEDIFF()
and TIMEDIFF()
.
Despite the TIMESTAMP
in its name, the TIMESTAMPDIFF()
function
arguments are not limited to the range of the TIMESTAMP
data type.
TIMESTAMPDIFF()
requires MySQL 5.0 or higher. For older versions of MySQL, use
one of the other interval-calculation techniques described in
this section.
Another strategy for calculating intervals is to work with basic units such as seconds or days using this strategy:
Convert the temporal values that you’re working with to basic units.
Take the difference between the values to calculate the interval, also in basic units.
If you want the result as a temporal value, convert it from basic units to the appropriate type.
The conversion functions involved in implementing this strategy depend on the types of the values between which you’re calculating the interval:
For more information about those conversion functions (and limitations on their applicability), see the discussion in Converting Between Temporal Data Types and Basic Units. The following material assumes familiarity with that discussion.
To calculate intervals in seconds between pairs of time
values, convert them to seconds with TIME_TO_SEC()
, and then take the
difference. To express the resulting interval as a TIME
value, pass it to SEC_TO_TIME()
. The following
statement calculates the intervals between the t1
and t2
columns of the time_val
table, expressing each interval
both in seconds and as a TIME
value:
mysql>SELECT t1, t2,
->TIME_TO_SEC(t2) - TIME_TO_SEC(t1) AS 't2 - t1 (in seconds)',
->SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1)) AS 't2 - t1 (as TIME)'
->FROM time_val;
+----------+----------+----------------------+-------------------+ | t1 | t2 | t2 - t1 (in seconds) | t2 - t1 (as TIME) | +----------+----------+----------------------+-------------------+ | 15:00:00 | 15:00:00 | 0 | 00:00:00 | | 05:01:30 | 02:30:20 | -9070 | -02:31:10 | | 12:30:20 | 17:30:45 | 18025 | 05:00:25 | +----------+----------+----------------------+-------------------+
When you calculate an interval between dates by converting both dates to a common unit in relation to a given reference point and take the difference, the range of values that you’re working with determines which conversions are available:
DATE
, DATETIME
, or TIMESTAMP
values dating back to
1970-01-01
00:00:00
UTC—the date of the Unix
epoch—can be converted to seconds elapsed since the epoch. If
both dates lie within that range, you can calculate intervals to
an accuracy of one second.
Older dates from the beginning of the Gregorian calendar (1582) on can be converted to day values and used to compute intervals in days.
Dates that begin earlier than either of these reference points present more of a problem. In such cases, you may find that your programming language offers computations that are not available or are difficult to perform in SQL. If so, consider processing date values directly from within your API language. (For example, the Date::Calc and Date::Manip modules are available from CPAN for use within Perl scripts.)
To calculate an interval in days between date or date-and-time
values, convert them to days with TO_DAYS()
, and take the
difference:
mysql>SELECT TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05') AS days;
+------+
| days |
+------+
| 210 |
+------+
For an interval in weeks, do the same thing and divide the result by seven:
mysql>SELECT (TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05')) / 7 AS weeks;
+---------+
| weeks |
+---------+
| 30.0000 |
+---------+
You cannot convert days to months or years by simple division,
because those units vary in length. For calculations to yield date
intervals expressed in those units, use the TIMESTAMPDIFF()
function discussed
earlier in this section.
For date-and-time values occurring within the TIMESTAMP
range of 1970 to 2037, you can
determine intervals to a resolution in seconds using the UNIX_TIMESTAMP()
function. For
example, the number of seconds between dates that lie two weeks
apart can be computed like this:
mysql>SET @dt1 = '1984-01-01 09:00:00';
mysql>SET @dt2 = @dt1 + INTERVAL 14 DAY;
mysql>SELECT UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1) AS seconds;
+---------+ | seconds | +---------+ | 1209600 | +---------+
To convert the interval in seconds to other units, perform the appropriate arithmetic operation. Seconds are easily converted to minutes, hours, days, or weeks:
mysql>SET @interval = UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1);
mysql>SELECT @interval AS seconds,
->@interval / 60 AS minutes,
->@interval / (60 * 60) AS hours,
->@interval / (24 * 60 * 60) AS days,
->@interval / (7 * 24 * 60 * 60) AS weeks;
+---------+------------+----------+---------+--------+ | seconds | minutes | hours | days | weeks | +---------+------------+----------+---------+--------+ | 1209600 | 20160.0000 | 336.0000 | 14.0000 | 2.0000 | +---------+------------+----------+---------+--------+
To produce integer values (no fractional part), use the
FLOOR()
function, as shown
in Converting Between Temporal Data Types and Basic Units. This applies to
several of the following examples as well.
For values that occur outside the TIMESTAMP
range, you can use an interval
calculation method that is more general (but messier):
Take the difference in days between the date parts of the values and multiply by 24 × 60 × 60 to convert to seconds.
Offset the result by the difference in seconds between the time parts of the values.
Here’s an example, using two date-and-time values that lie a week apart:
mysql>SET @dt1 = '1800-02-14 07:30:00';
mysql>SET @dt2 = @dt1 + INTERVAL 7 DAY;
mysql>SET @interval =
->((TO_DAYS(@dt2) - TO_DAYS(@dt1)) * 24*60*60)
->+ TIME_TO_SEC(@dt2) - TIME_TO_SEC(@dt1);
mysql>SELECT @interval AS seconds, SEC_TO_TIME(@interval) AS TIME;
+---------+-----------+ | seconds | TIME | +---------+-----------+ | 604800 | 168:00:00 | +---------+-----------+
3.15.214.155