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]>

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)

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!

May 21, 2019 0

Multi-WAN DNS in pfSense

Update: I later figured out there are many other places pfSense restarts Unbound, so this is simply not worth the effort. I reversed the changes & moved Unbound to another box and using just DNS forwarder on pfSense — which is used by the Unbound server.

Having multiple broadband connections at home, I have a pfSense which takes care of load balancing and firewalling. pfSense is pretty good in almost everything, except one thing that was annoying me a lot — That it restarted the DNS Resolver (Unbound) every time either of my WAN connections restarted (one of my ISPs restarts the connection periodically), and the traffic originating from the box itself cannot be load balanced across multiple connections due to a limitation in FreeBSD’s implementation of pf itself – it is unable to set the correct source address.

It’s quite annoying that – even when you use the forwarding mode of Unbound, your DNS still goes through a single WAN interface. Moreover, Unbound doesn’t seem to do parallel querying across DNS servers. So if you have listed multiple DNS servers as forwarders it will try them one by one as they fail. Suppose, the WAN interface from which DNS traffic is outgoing is running at full capacity – a download or somebody is streaming a video, then your browsing becomes slow as well – but the browsing itself may go through another WAN connection. Notably, for having a stable multi-WAN setup in pfSense – you have to use forwarding mode. The gateway switching for the box itself doesn’t work reliably in my experience, due to which I’ve had to face “host not found” error messages even when one of the connections was up.

Continue reading “Multi-WAN DNS in pfSense” →

May 6, 2018 0

The sad state of social media support among Indian companies

Use of social media by Indian companies have skyrocketed in the recent years due to many reasons, two to be specific –

It helps people connect directly with government – a lot has been done in this regard under the Digital India initiative.
Since social media is a public space, brands / companies are pressurized to correctly resolve the issue or it is a ticking time bomb for them in case the problem becomes viral, can damage reputation easily.

Now coming back to the support brands on Twitter, an example here:

BSNL is a telecom operator in India, and they have a Twitter account where they answer queries. My query was pretty simple – BSNL announced availability of 4G uSIMs nationally so I asked them if 4G service is available in Pune. Their response as seen above – they want contact details from me. Moreover, the reply says “Sorry for the inconvenience caused”. I didn’t face any inconvenience here. It’s just out of context! Why not just reply to the tweet with a simple yes or no? It doesn’t contain any private information.

Many such countless examples can be seen on the Tweet with replies page of their twitter profiles. Interestingly it’s a common trend, and I don’t know why. If you look at the twitter profiles of some international brands – they don’t reply in similar fashion, except in cases where private information (account number, mobile number, etc.) is involved.

Common general information can be shared publicly, no? If information involves private matters such as – someone has a specific problem for which details are needed – that conversation can happen in private.

April 10, 2018 0

A WAN monitor running on Google AppEngine written in Go language

As I stated in my earlier post, I have two WAN connections and of course, there’s a need to monitor them. The monitoring logic is pretty simple, it will send me a message on Telegram every time there’s a state change – UP or DOWN.

Initially this monitoring logic was built as OpenWrt hotplug script which used to trigger on interface UP / DOWN events as described in this article. But then I got a mini PC box and it runs Ubuntu and a pfsense virtual machine. While I could build the same logic by discovering hooks in the pfsense code, but it’s too complex and moreover it doesn’t really make sense to monitor the connection of a device using the same connections!

Continue reading “A WAN monitor running on Google AppEngine written in Go language” →

March 17, 2018 0

Supt Padangushtasana to recover after intense cycling

I started cycling again after a gap of 4-5 years. Bought Firefox Maximus D 29er last year and it’s been amazing in terms of fun and saving money as well because I ride to many places where I used to go driving previously.

Few days back I decided to improve on my average speed and the solution was to change my pedaling pattern – what I used to do was shift to highest possible gear on the road and pedal slowly but because of the gear ratios I used to get good average speeds.

The human body cannot deliver high amount of power for a long duration but can do low power for higher duration much, like a IC engine – which is why you have gearbox in it; the engine rotates at higher RPM on lower gears and vice-versa.

Continue reading “Supt Padangushtasana to recover after intense cycling” →

January 19, 2018 0

Monitoring your internet connections with OpenWRT and a Telegram Bot

For the past 5 years or so, I have been using a single ISP at home and mobile data for backup when it went down. But since last few months, the ISP service became a bit unreliable – this is more related to the rainy season. Mobile data doesn’t give fiber like constant speeds I get on the wire. It’s very annoying to browse at < 10 Mbps on mobile data when you are used to 100 Mbps on the wire.

I decided to get another fiber pipe from a local ISP. One needs to be very unlucky to have both going down at the same time – I hope that never happens. Now the question is how to monitor the two connections: Why do I need monitoring? – so that I can inform the ISP when it goes down, with the fail-over happening automatically thanks to OpenWRT’s mwan3 package, I won’t ever know when I am using which ISP (unless I am checking the public IP address, of course).

Continue reading “Monitoring your internet connections with OpenWRT and a Telegram Bot” →

August 25, 2017 2

Asterisk PJSIP wizard and phone provisioning

So after setting up Asterisk with a working DAHDI configuration for the PBX project, next was configuration for IP phones using PJSIP and provisioning them.

Asterisk has a built-in module called res_phoneprov which handles HTTP based phone provisioning but that didn’t work for me – I just couldn’t have it generate XML configuration for the phones that we had, i.e. Grandstream GXP1625.

The server on which I had configured PBX was multi-homed, as in it was part of multiple networks. But there was no reason to run the service on all interfaces except the VLAN on which we were going to connect the phones.

Continue reading “Asterisk PJSIP wizard and phone provisioning” →

July 20, 2017 5

Asterisk PBX with Reliance PRI Line using Digium TE131F

So I got an opportunity to set up Asterisk PBX with a Reliance Communications E1 line. I have worked with Asterisk PBX, but without PSTN interfacing. This post is about what all stuff I have done to get a Reliance E1 line with Digium TE131F card.

Having explored a lot of other distributions like Fedora, Arch, Gentoo, Sabayon, etc. since I ventured into Linux world and learning the internals of Linux and how different components are stitched together I settled on Ubuntu. It’s my favorite these days because everything seems to work out of the box… except when it doesn’t, then you have PPAs. 😛 For this project I have installed Ubuntu 16.04 server edition.

Continue reading “Asterisk PBX with Reliance PRI Line using Digium TE131F” →

July 14, 2017 0

Nilesh

Date range in a MariaDB query using the Sequence Engine

Here’s a sample of my use case:

Like this:

Multi-WAN DNS in pfSense

Like this:

A WAN monitor running on Google AppEngine written in Go language

Like this:

Supt Padangushtasana to recover after intense cycling

Like this:

Monitoring your internet connections with OpenWRT and a Telegram Bot

Like this:

Asterisk PJSIP wizard and phone provisioning

Like this:

Asterisk PBX with Reliance PRI Line using Digium TE131F

Like this:

Categories

Connect with me

Here’s a sample of my use case:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Categories

Tags

Connect with me