Database Reference
In-Depth Information
CREATE TABLE
birding_events
(
event_id
INT
AUTO_INCREMENT
KEY
,
event_name
VARCHAR
(
255
),
event_description
TEXT
,
meeting_point
VARCHAR
(
255
),
event_date
DATE
,
start_time
TIME
);
For the examples in this section, the column in this table with which we're mostly con-
cerned is
start_time
. Let's add a birding event to
birding_events
by entering the
following:
INSERT INTO
birding_events
VALUES
(
NULL
,
'Sandpipers in San Diego'
,
"Birdwatching Outing in San Diego to look for Sandpipers,
Curlews, Godwits, Snipes and other shore birds.
Birders will walk the beaches and surrounding area in groups of six.
A light lunch will be provided."
,
"Hotel del Coronado, the deck near the entrance to the restaurant."
,
'2014-06-15'
,
'09:00:00'
);
Now we can try using
TIMEDIFF()
. Enter the following to determine how many days
and how much time until the start of the event:
SELECT NOW(), event_date, start_time,
DATEDIFF(event_date, DATE(NOW())) AS 'Days to Event',
TIMEDIFF(start_time, TIME(NOW())) AS 'Time to Start'
FROM birding_events;
+---------------------+------------+------------+-------------+---------------+
| NOW() | event_date | start_time |Days to Event| Time
to Start |
+---------------------+------------+------------+-------------+---------------+
| 2014-02-14 06:45:24 | 2014-06-15 | 09:00:00 | 121 |
02:14:36 |
+---------------------+------------+------------+-------------+---------------+
The event will start in 121 days, 2 hours, 14 minutes, and 36 seconds from the time this
SQL statement was executed. That's correct, but the results displayed for
Time to Start
seem more like a time of day, rather than a count of hours, minutes, and seconds remain-
ing. Let's use
DATE_FORMAT()
fora nicer display. Let's alsouse
CONCAT()
to put the
number of days together with the time remaining:
SELECT NOW(), event_date, start_time,
CONCAT(
DATEDIFF(event_date, DATE(NOW())), ' Days, ',
