Database Reference
In-Depth Information
| 12:30:20 | 17:30:45 | 108065 | 30:01:05 |
+----------+----------+----------------------+-------------------+
It's important to recognize that MySQL
TIME
values represent elapsed time, not time of
day, so they don't reset to 0 after reaching 24 hours. You can see this in the first and third
output rows from the previous statement. To produce time-of-day values, enforce a 24-
hour wraparound using a modulo operation before converting the seconds value back
to a
TIME
value. The number of seconds in a day is 24 × 60 × 60, or 86,400. To convert
any seconds value
s
to lie within a 24-hour range, use the
MOD()
function or the
%
modulo
operator like this:
MOD(s,86400)
s % 86400
s MOD 86400
The three expressions are equivalent. Applying the first of them to the time calculations
from the preceding example produces the following result:
mysql>
SELECT t1, t2,
->
MOD(TIME_TO_SEC(t1) + TIME_TO_SEC(t2), 86400)
->
AS 't1 + t2 (in seconds)',
->
SEC_TO_TIME(MOD(TIME_TO_SEC(t1) + TIME_TO_SEC(t2), 86400))
->
AS 't1 + t2 (as TIME)'
->
FROM time_val;
+----------+----------+----------------------+-------------------+
| t1 | t2 | t1 + t2 (in seconds) | t1 + t2 (as TIME) |
+----------+----------+----------------------+-------------------+
| 15:00:00 | 15:00:00 | 21600 | 06:00:00 |
| 05:01:30 | 02:30:20 | 27110 | 07:31:50 |
| 12:30:20 | 17:30:45 | 21665 | 06:01:05 |
+----------+----------+----------------------+-------------------+
The permitted range of a
TIME
column is
-838:59:59
to
838:59:59
(that is,
-3020399
to
3020399
seconds). However, the range of
TIME
expressions
can be greater, so when you add time values, you can easily
produce a result that lies outside this range and cannot be stored as
is into a
TIME
column.
Adding to date or date-and-time values using basic units
Date or date-and-time values converted to basic units can be shifted to produce other
dates. For example, to shift a date forward or backward a week (seven days), use
TO_DAYS()
and
FROM_DAYS()
:
mysql>
SET @d = '1980-01-01';
mysql>
SELECT @d AS date,
->
FROM_DAYS(TO_DAYS(@d) + 7) AS 'date + 1 week',
->
FROM_DAYS(TO_DAYS(@d) - 7) AS 'date - 1 week';
+------------+---------------+---------------+
| date | date + 1 week | date - 1 week |
