Database Reference
In-Depth Information
tween 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 |
+----------+----------+----------------------+-------------------+
Date or date-and-time interval calculation using basic units
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 your
values determines which conversions are available:
•
DATE
,
DATETIME
, or
TIMESTAMP
values dating back to
1970-01-01
00:00:00
UTC—
the Unix epoch—can be converted to seconds elapsed since the epoch. With dates
in 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 con‐
verted 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 com‐
putations 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 in 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. For an interval in weeks, do the same thing
and divide the result by seven:
mysql>
SET @days = TO_DAYS('1884-01-01') - TO_DAYS('1883-06-05');
mysql>
SELECT @days AS days, @days/7 AS weeks;
+------+---------+
| days | weeks |
+------+---------+
| 210 | 30.0000 |
+------+---------+
You cannot convert days to months or years by simple division because those units vary
in length. To yield date intervals expressed in those units, use
TIMESTAMPDIFF()
, dis‐
cussed earlier in this recipe.
