Database Reference
In-Depth Information
year as either two or four digits, followed by a month as two digits (e.g., April 2014 could
be either 1404 or 201404). Let's try out this function with the
birdwatchers
database.
Suppose we want a count of bird sightings recorded by each member, but just for the pre-
vious quarter. This seems like it would be simple to do, just byusing
QUARTER()
in the
WHERE
clauseof a
SELECT
statement. Such an SQL statement might look like this:
SELECT
CONCAT
(
name_first
,
SPACE
(
1
),
name_last
)
AS
'Birdwatcher'
,
COUNT
(
time_seen
)
AS
'Sightings Recorded'
FROM
bird_sightings
JOIN
humans
USING
(
human_id
)
WHERE
QUARTER
(
time_seen
) = (
QUARTER
(
CURDATE
()) -
1
)
AND
YEAR
(
time_seen
) = (
YEAR
(
CURDATE
( )) -
1
)
GROUP BY
human_id
LIMIT
5
;
Empty
set
(
0
.
14
sec
)
An empty set was returned. This is because the result of
QUARTER(CURDATE())
is 1,
because I happened to execute this example during the first quarter of the year. So,
QUARTER(CURDATE()) - 1
equals 0. Because all of the rows will have a date in
quarters 1 through 4 (i.e.,
QUARTER(time_seen)
), none will match. If I entered this
statement during a different quarter, it would return results for the wrong quarter (the pre-
vious one).
Therefore, we have to adjust this SQL statement. We can do this by using
PERIOD_ADD()
a couple of times, along with a few other date functions we covered
earlier. Here's how we could get the list of people and the number of sightings they recor-
ded for last quarter, regardless of the quarter in which it's executed:
SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
COUNT(time_seen) AS 'Sightings Recorded'
FROM bird_sightings
JOIN humans USING(human_id)
WHERE CONCAT(QUARTER(time_seen), YEAR(time_seen)) =
CONCAT(
QUARTER(
STR_TO_DATE(
PERIOD_ADD( EXTRACT(YEAR_MONTH FROM CURDATE()), -3),
'%Y%m') ),
YEAR(
STR_TO_DATE(
PERIOD_ADD( EXTRACT(YEAR_MONTH FROM CURDATE()), -3),
'%Y%m') ) )
GROUP BY human_id LIMIT 5;
