Date and Time Functions - Learning MySQL and MariaDB - page 249

Let's look at a simple example of this function by redoing the example that queried for the
bird-watchers who saw the Black Guineafowl . Here it is again with EXTRACT() :
SELECT time_seen,
EXTRACT(YEAR_MONTH FROM time_seen) AS 'Year & Month',
EXTRACT(MONTH FROM time_seen) AS 'Month Only',
EXTRACT(HOUR_MINUTE FROM time_seen) AS 'Hour & Minute',
EXTRACT(HOUR FROM time_seen) AS 'Hour Only'
FROM bird_sightings JOIN humans USING(human_id)
LIMIT 3;
+---------------------+--------------+------------+---------------+-----------+
| time_seen | Year & Month | Month Only | Hour & Minute |
Hour Only |
+---------------------+--------------+------------+---------------+-----------+
| 2013-10-01 04:57:12 | 201310 | 10 | 457
| 4 |
| 2013-10-01 05:09:27 | 201310 | 10 | 509
| 5 |
| 2013-10-01 05:13:25 | 201310 | 10 | 513
| 5 |
+---------------------+--------------+------------+---------------+-----------+
As you can see, when you use EXTRACT() with single intervals, it works fine as a con-
sistent substitute for the other temporal extraction functions. Asking for HOUR_MINUTE
doesn't produce very nice results, because there is no colon between the hour and minute
(for instance, 4:57 is shown as 457). When you use EXTRACT() with combined inter-
vals, it returns results combined together with no formatting. That may be what you want
sometimes, but other times you might want to format a date or time. Once again, you'll
need the date and time formatting functions in thenext section.