Database Reference
In-Depth Information
DATE_FORMAT(TIMEDIFF(start_time, TIME(NOW())), '%k hours, %i
minutes'))
AS 'Time to Event'
FROM birding_events;
+---------------------+------------+----------+-------------------------------+
| NOW() | event_date |start_time| Time to
Event |
+---------------------+------------+----------+-------------------------------+
| 2014-02-14 06:46:25 | 2014-06-15 | 09:00:00 | 121 Days, 2 hours,
13 minutes |
+---------------------+------------+----------+-------------------------------+
You have to carefully check the parentheses on that statement to execute it successfully.
We embed
NOW()
inthe
DATE()
and
TIME()
functions. These in turn are embedded in
DATEDIFF()
and
TIMEDIFF()
to get the difference from the date and time stored in
the database.
TIMEDIFF()
is embedded in
DATE_FORMAT()
, and all those functions
are embedded in
CONCAT()
.
After looking at these results, we decide that it would be much simpler if we change the
table to use a single column to record the date and time of the event. I said in the first sec-
tion of this chapter that we would cover some examples of how to change temporal data
types for a column. Let's do that now. Let's create a new column,
event_datetime
,
using the
DATETIME
data type:
ALTER TABLE
birding_events
ADD COLUMN
event_datetime
DATETIME
;
That adds the new column to contain the date and time. Now let's update the table to com-
bine them into
event_datetime
:
UPDATE
birding_events
SET
event_datetime
=
CONCAT
(
event_date
,
SPACE
(
1
),
start_time
);
The
CONCAT()
function merges the date and time together as a string. MySQL will auto-
matically convert that string into a date, and then set the value of
event_datetime
to
a date and time value. Let's execute a
SELECT
statement to see how the data looks now:
SELECT event_date, start_time, event_datetime
FROM birding_events;
+------------+------------+---------------------+
| event_date | start_time | event_datetime |
+------------+------------+---------------------+