Database Reference
In-Depth Information
its seconds part is
00
, extract the hour and minute parts, and then recombine them with
CONCAT()
:
mysql>
SELECT t1,
->
CONCAT(LPAD(HOUR(t1),2,'0'),':',LPAD(MINUTE(t1),2,'0'),':00')
->
AS recombined
->
FROM time_val;
+----------+------------+
| t1 | recombined |
+----------+------------+
| 15:00:00 | 15:00:00 |
| 05:01:30 | 05:01:00 |
| 12:30:20 | 12:30:00 |
+----------+------------+
To produce a combined date-and-time value from separate date and time values, simply
concatenate them separated by a space:
mysql>
SET @d = '2014-02-28', @t = '13:10:05';
mysql>
SELECT @d, @t, CONCAT(@d,' ',@t);
+------------+----------+---------------------+
| @d | @t | CONCAT(@d,' ',@t) |
+------------+----------+---------------------+
| 2014-02-28 | 13:10:05 | 2014-02-28 13:10:05 |
+------------+----------+---------------------+
6.10. Converting Between Temporal Values and Basic
Units
Problem
You want to convert a temporal value such as a time or date to basic units such as seconds
or days. This is often useful or necessary for performing temporal arithmetic operations
(see Recipes
6.11
and
6.12
).
Solution
The conversion method depends on the type of value to be converted:
• To convert between time values and seconds, use the
TIME_TO_SEC()
and
SEC_TO_TIME()
functions.
• To convert between date values and days, use the
TO_DAYS()
and
FROM_DAYS()
functions.
• To convert between date-and-time values and seconds, use the
UNIX_TIME
STAMP()
and
FROM_UNIXTIME()
functions.
