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 |
+------------+------------+---------------------+
Search WWH ::




Custom Search