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!

The post Date range in a MariaDB query using the Sequence Engine appeared first on Nilesh.