MySQL Select all rows since the previous occurrence of a certain day of month

user_4613060 0
Viewed 42

So the idea is simple but i have no idea how to do it and i have not found any useful information online.

I have a system setup to track internet data usage using MariaDB. I have a data cap that resets on the 13th of each month. And i would like to be able to display all data usage for the current cycle.

Is this possible with MySQL? (I will be using grafana to display the data)

Edit: Sorry i did not think to include the table schema

create table network.data_usage
(
    time     timestamp default current_timestamp() not null on update current_timestamp(),
    mac      varchar(32)                           not null,
    download bigint    default 0                   not null,
    upload   bigint    default 0                   not null
);

obviously the time field is the most relevant to this question. the upload and download fields store the total bytes in/out for a specific mac address since the previous entry.

mariadb mysql
1 Answers

Basically you need to compute the appropriate 13th day of the month for the current date. You can get to the 13th day of the current month using

CURDATE() - INTERVAL DAY(CURDATE()) - 13 DAY

Then you just need to subtract a month if the current date is less than 13:

CURDATE() - INTERVAL DAY(CURDATE()) - 13 DAY - INTERVAL IF(DAY(CURDATE()) < 13, 1, 0) MONTH

Having computed that, you can then SUM all the download and upload values since that date:

SELECT SUM(download) AS downloads,
       SUM(upload) AS uploads,
       SUM(download + upload) AS total
FROM data_usage
WHERE time >= CURDATE() - INTERVAL DAY(CURDATE()) - 13 DAY - INTERVAL IF(DAY(CURDATE()) < 13, 1, 0) MONTH

You can also sum for each mac value using a GROUP BY clause e.g.

SELECT mac,
       SUM(download) AS downloads,
       SUM(upload) AS uploads,
       SUM(download + upload) AS total
FROM data_usage
WHERE time >= CURDATE() - INTERVAL DAY(CURDATE()) - 13 DAY - INTERVAL IF(DAY(CURDATE()) < 13, 1, 0) MONTH
GROUP BY mac

If you are grouping by mac, you can also generate a total row by adding WITH ROLLUP to the GROUP BY clause:

SELECT COALESCE(mac, 'Total') AS mac,
       SUM(download) AS downloads,
       SUM(upload) AS uploads,
       SUM(download + upload) AS total
FROM data_usage
WHERE time >= CURDATE() - INTERVAL DAY(CURDATE()) - 13 DAY - INTERVAL IF(DAY(CURDATE()) < 13, 1, 0) MONTH
GROUP BY mac WITH ROLLUP

Demo on dbfiddle

user_94737640
Related