Date range in a MariaDB query using the Sequence Engine
One of my applications involved generating a date-wise report for items created on that day and we needed zeroes against the count of items on the date which had no entries.
User selects a date range and the application must generate this report. Not so easy if I had not come across the MariaDB Sequence Storage Engine!
Sequences have long been good features in databases like Oracle, PostgreSQL and the likes, I absolutely had no idea of it’s existence in MariaDB — just came across it while browsing the documentation of MariaDB.
Here’s a sample of my use case:
MariaDB [test]> create table items (id int unsigned primary key auto_increment, date_created datetime not null);
Query OK, 0 rows affected (0.061 sec)
MariaDB [test]> insert into items (date_created) values ('2019-01-01'), ('2019-01-05'), ('2019-01-06'), ('2019-01-06'), ('2019-01-01'), ('2019-01-10'), ('2019-01-09'), ('2019-01-09'), ('2019-01-09');
Query OK, 9 rows affected (0.032 sec)
Records: 9 Duplicates: 0 Warnings: 0
MariaDB [test]> select * from items;
+----+---------------------+
| id | date_created |
+----+---------------------+
| 1 | 2019-01-01 00:00:00 |
| 2 | 2019-01-05 00:00:00 |
| 3 | 2019-01-06 00:00:00 |
| 4 | 2019-01-06 00:00:00 |
| 5 | 2019-01-01 00:00:00 |
| 6 | 2019-01-10 00:00:00 |
| 7 | 2019-01-09 00:00:00 |
| 8 | 2019-01-09 00:00:00 |
| 9 | 2019-01-09 00:00:00 |
+----+---------------------+
9 rows in set (0.001 sec)
MariaDB [test]> select date(date_created), count(id) from items group by date(date_created);
+--------------------+-----------+
| date(date_created) | count(id) |
+--------------------+-----------+
| 2019-01-01 | 2 |
| 2019-01-05 | 1 |
| 2019-01-06 | 2 |
| 2019-01-09 | 3 |
| 2019-01-10 | 1 |
+--------------------+-----------+
5 rows in set (0.001 sec)
MariaDB [test]>
After a couple of attempts with the samples provided in the MariaDB documentation page, I managed to devise a query which provided me exactly what I needed, using SQL UNION:
MariaDB [test]> select dt, max(cnt) from ( select cast( date_add('2019-01-01', interval seq day) as date ) dt, 0 cnt from seq_0_to_11 union select cast( date(date_created) as date ) dt, count(id) cnt from items where date(date_created) between '2019-01-01' and '2019-01-11' group by date(date_created) ) t group by dt order by dt;
+------------+----------+
| dt | max(cnt) |
+------------+----------+
| 2019-01-01 | 2 |
| 2019-01-02 | 0 |
| 2019-01-03 | 0 |
| 2019-01-04 | 0 |
| 2019-01-05 | 1 |
| 2019-01-06 | 2 |
| 2019-01-07 | 0 |
| 2019-01-08 | 0 |
| 2019-01-09 | 3 |
| 2019-01-10 | 1 |
| 2019-01-11 | 0 |
| 2019-01-12 | 0 |
+------------+----------+
12 rows in set (0.001 sec)
Yeah, that’s basically filling in zero values for the dates on which there were no entries. Can this be done using RIGHT JOIN? I tried to but couldn’t form a JOIN condition. If you know drop a comment!
NileshGR 