Database Reference
In-Depth Information
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
JOIN rookery.conservation_status USING(conservation_status_id)
WHERE conservation_category = 'Threatened' LIMIT 3;
+---------------------+--------------+----------------------------+-----------+
| Endangered Bird | Birdwatcher | Date Spotted |
Time |
+---------------------+--------------+----------------------------+-----------+
| Eskimo Curlew | Elena Bokova | Tuesday, October 1, 2013 |
5:9 a.m. |
| Red-billed Curassow | Marie Dyer | Wednesday, October 2, 2013 |
7:39 a.m. |
| Red-billed Curassow | Elena Bokova | Wednesday, October 2, 2013 |
8:41 a.m. |
+---------------------+--------------+----------------------------+-----------+
This is a very cluttered SQL statement. Yes, because it involves using
JOIN
a few times,
it's lengthy as one would expect. But using
CONCAT()
twice with so many date and time
functions clutters it unnecessarily. Notice that
5:9
is displayed for the hours and minutes,
instead of
5:09
. That's because thefunction,
MINUTE()
doesn'tpad with zeroes. We
could fix that by usingthe
LPAD()
function, but that would be more clutter. We complic-
ated the statement even further by usingthe
IF()
function to label the time morning or
evening (i.e., a.m. or p.m.).
There's a cleaner, easier way to reformat dates and times using date and time formatting
functions, which are described in the next section. Meanwhile, you can reduce the number
of date and extraction functions to a singleone:
EXTRACT()
.
The
EXTRACT()
function can be used to extract any component of a date or time. The
syntax is simple and a little verbose:
EXTRACT(
interval
FROM
date_time
)
. The
intervals given are similar to the names of the date and time extraction functions we've
already reviewed:
MONTH
for month,
HOUR
for hour, and so on. There are also some com-
bined ones such as
YEAR_MONTH
and
HOUR_MINUTE
. For a list of intervals allowed
