Database Reference
In-Depth Information
| 2014-06-15 | 09:00:00 | 2014-06-15 09:00:00 |
+------------+------------+---------------------+
The UPDATE worked fine. Let's try now to get the formatting we want for the time re-
maining until the event, but from the new column. Enter the following:
SELECT NOW(), event_datetime,
CONCAT(DATEDIFF(event_datetime, NOW() ), ' Days, ',
TIME_FORMAT( TIMEDIFF( TIME(event_datetime), CURTIME() ),
'%k hours, %i minutes') )
AS 'Time to Event'
FROM birding_events;
+---------------------+---------------------+-------------------------------+
| NOW() | event_datetime | Time to
Event |
+---------------------+---------------------+-------------------------------+
| 2014-02-14 05:48:55 | 2014-06-15 09:00:00 | 121 Days, 3 hours, 11
minutes |
+---------------------+---------------------+-------------------------------+
That looks fine and it's much better than having the date and time in separate columns.
We can now alter birding_events to drop the two columns for date and time that we
no longer need:
ALTER TABLE birding_events
DROP COLUMN event_date ,
DROP COLUMN start_time ;
We've successfully completed the process of migrating the date and time from two
columns into one. You probably would have initially chosen to create one column instead
of two, as we did in these examples. But you won't always choose though the best tem-
poral data type for a column. That's why I wanted to walk you through the process of how
to migrate between temporal data types: to prepare you for what to do when you don't
make the best choice the firsttime.
Search WWH ::




Custom Search