Database Reference
In-Depth Information
+-------------------+--------------------+
| Birdwatcher | Sightings Recorded |
+-------------------+--------------------+
| Richard Stringer | 1 |
| Rusty Osborne | 1 |
| Elena Bokova | 3 |
| Katerina Smirnova | 3 |
| Anahit Vanetsyan | 1 |
+-------------------+--------------------+
I indented this SQL statement plenty to make it easier to read. We're using
EXTRACT()
to extract the year and month fromthe
CURDATE()
and to put it in the format we need
for
PERIOD_ADD()
(i.e.,
yyyymm
). The first time we use
PERIOD_ADD()
, it's getting
the number of the previous quarter. The second time we use this function, it's getting the
year of that previous quarter. Weuse
STR_TO_DATE
toconvert the result of
PERIOD_ADD
to a date.
Then we're using
CONCAT()
to put the quarter and year together. We'll compare that to
the quarter and year we'll concatenate from
time_seen
. This process would be simpler
if
EXTRACT()
had an option of
YEAR_QUARTER
. Then we wouldn't need to determine
the date of the previous quarter twice, extract the year and month separately, and concat-
enate them. Sometimes we push the limits of MySQL and MariaDB. But they occasion-
ally add new features and options. For now, there are ways to accomplish what you want
with more complex SQLstatements.