Database Reference
In-Depth Information
Extracting Date and Time Components
Temporal data typesstore more information than you may sometimes want. There will be
situations in which you don't want a full date or a time to the second. Because of this, there
are functions that will extract any component of a temporal value you may want, as well as
some common permutations. Let's look first at some basic functions for extracting just the
date and just the time, then we'll look at ones for each component.
A
DATETIME
column, asthe name implies, contains both the date and the time. If you
want to extract just the date from such a value, you can usethe
DATE()
function. To ex-
tract just the time, use
TIME()
. Let's look at an example of these two. We'll again select
the
time_seen
value for sightings of a
Black Guineafowl
:
SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
time_seen, DATE(time_seen), TIME(time_seen)
FROM bird_sightings
JOIN humans USING(human_id)
WHERE bird_id = 309;
+-------------+---------------------+-----------------+-----------------+
| Birdwatcher | time_seen | DATE(time_seen) |
TIME(time_seen) |
+-------------+---------------------+-----------------+-----------------+
| Marie Dyer | 2013-10-02 07:39:44 | 2013-10-02 |
07:39:44 |
+-------------+---------------------+-----------------+-----------------+
That was easy:
DATE()
returned just the date from
time_seen
and
TIME()
just the
time. However, you may want to extract just one component of a date or time. You can do
this with all of the temporal data types, as long as the column contains the component you
want — you can't get the hour from a
YEAR
column.
To extract only the hour of a time saved in a column,the
HOUR()
function could be used.
For the minute and second,there's
MINUTE()
and
SECOND()
. These may be used with
DATETIME
,
TIME
, and
TIMESTAMP
columns. Let's see how the results from them might
look. Enter the following in
mysql
:
SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
time_seen, HOUR(time_seen), MINUTE(time_seen), SECOND(time_seen)
FROM bird_sightings JOIN humans USING(human_id)
WHERE bird_id = 309 \G
*************************** 1. row ***************************
Birdwatcher: Marie Dyer
