Database Reference
In-Depth Information
time_seen: 2013-10-02 07:39:44
HOUR(time_seen): 7
MINUTE(time_seen): 39
SECOND(time_seen): 44
These functions will allow you to use, assess, and compare each component of the time
for a column. You can break apart a date, as well.
To extract the year, month, and day, you could usethe
YEAR()
,
MONTH()
, and
DAY()
functions. You have to give a date value as the argument for each function. This can be a
column that contains a date, or a string value that contains a date (e.g., '
2014-02-14'
, in-
cluding the quotes). It cannot be a number, unless the number is properly ordered. For in-
stance, the numeric value
20140214
is acceptable, but not
2014-02-14
(without quotes) or
2014 02 14
(with spaces). Here's the same SQL statement as before, but using these func-
tions instead:
SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
time_seen, YEAR(time_seen), MONTH(time_seen), DAY(time_seen),
MONTHNAME(time_seen), DAYNAME(time_seen)
FROM bird_sightings JOIN humans USING(human_id)
WHERE bird_id = 309 \G
*************************** 1. row ***************************
Birdwatcher: Marie Dyer
time_seen: 2013-10-02 07:39:44
YEAR(time_seen): 2013
MONTH(time_seen): 10
DAY(time_seen): 2
MONTHNAME(time_seen): October
DAYNAME(time_seen): Wednesday
This example has a couple of other datefunctions:
MONTHNAME()
to get the name of the
month for the date;and
DAYNAME()
to get the name of the day of the week for the date.
Using all of these functions, you can put together nicer looking results or easily check
date information. Let's look at how you might use the date and time functions to re-order
date results. Here's an example that retrieves a list of endangered birds spotted by the
members of the site:
SELECT common_name AS 'Endangered Bird',
CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
CONCAT(DAYNAME(time_seen), ', ', MONTHNAME(time_seen), SPACE(1),
DAY(time_seen), ', ', YEAR(time_seen)) AS 'Date Spotted',
CONCAT(HOUR(time_seen), ':', MINUTE(time_seen),
IF(HOUR(time_seen) < 12, ' a.m.', ' p.m.')) AS 'Time Spotted'
FROM bird_sightings
