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.
Search WWH ::




Custom Search