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:

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:

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!

Have something to add? Do it here.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑

%d bloggers like this: