How to SELECT the last 30 days records from SQL, including days with zero?

Question

I want to SELECT from my table the last 30 day records. My queries looks like this:

SELECT DATE(o_date) as date, count(id) AS sum FROM customers WHERE o_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW() GROUP BY o_date

Or this:

SELECT DATE(o_date) AS date, COUNT(id) AS sum FROM customers WHERE o_date >= DATE(NOW()) + INTERVAL -30 DAY GROUP BY DATE(o_date)

I want to create a list with dates and count of id-s.

But where I dont have any records in exact day, the query just skip that date. But I want to insert there a zero.

Example:

id	o_date
1	2021-11-23
2	2021-11-22
3	2021-11-20
4	2021-11-20
5	2021-11-19
6	2021-11-18
7	2021-11-18

The result will be this:

date	sum
2021-11-23	1
2021-11-22	1
2021-11-20	2
2021-11-19	1
2021-11-18	2

But where I dont have records like in this example in 2021-11-21 how can I insert to the sum 0?

Thank you!

UPDATE: I need this query for MariaDB.

Does this answer your question? [MySQL Select last 7 days](https://stackoverflow.com/questions/24272335/mysql-select-last-7-days) — Yannick, Nov 23 '21 at 12:21
A related question: https://stackoverflow.com/questions/24272335/mysql-select-last-7-days — Rick James, Nov 28 '21 at 07:32
You need a number generator to generate date series for the missing dates on which you can left join and use sum on — Raymond Nijland, Nov 28 '21 at 07:38

Codemaker2015 · Answer 1 · 2021-11-23T16:39:51.747

1

For MariaDB,

SELECT DATE(o_date) AS date, COUNT(id) AS sum FROM customers WHERE o_date BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY)
AND NOW();

For SQL,

SELECT DATE(o_date) AS date, COUNT(id) AS sum FROM customers WHERE DATEDIFF(day,o_date,GETDATE()) < 31

or

SELECT DATE(o_date) AS date, COUNT(id) AS sum FROM customers WHERE DATEDIFF(day,o_date,GETDATE()) between 0 and 30

edited Nov 23 '21 at 16:39

answered Nov 23 '21 at 16:10

Codemaker2015

12,190
6
97
81

I just updated the question. I need this query for MariaDB so the DATEDIFF only takes 2 two arguments. I tried this query: SELECT DATE(o_date) AS date, COUNT(id) AS sum FROM customers WHERE DATEDIFF(o_date,CURDATE()) between 0 and 30 But in this case the result is only one row with today-s date. – viktor1230 Nov 23 '21 at 16:36

score 0 · Answer 2 · edited Nov 23 '21 at 14:32

0

From what I could gather, it should be :

SELECT * FROM customers WHERE o_date BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();

Link to almost 10 year old post: MySQL Query - Records between Today and Last 30 Days

edited Nov 23 '21 at 14:32

ouflak

2,458
10
44
49

answered Nov 23 '21 at 12:30

Explodeonyou

39
7

Asad Hashmi · Answer 3 · 2021-11-24T07:12:19.063

0

Try this query:

SELECT DATE(o_date) AS date, COUNT(id) AS sum FROM customers WHERE o_date >= DATE_ADD(NOW(), INTERVAL -30 DAY)

edited Nov 24 '21 at 07:12

answered Nov 23 '21 at 13:18

Asad Hashmi

21
5

FUNCTION xxxx.DATEADD does not exist :/ – viktor1230 Nov 23 '21 at 15:01
Sorry it was for sql server. In mysql, it is DATE_ADD(NOW(), INTERVAL -30 DAY). Then the query will become SELECT DATE(o_date) AS date, COUNT(id) AS sum FROM customers WHERE o_date >= DATE_ADD(NOW(), INTERVAL -30 DAY). I have edited the comment – Asad Hashmi Nov 24 '21 at 07:09

score 0 · Answer 4 · answered Nov 28 '21 at 07:39

Your real question seems to be about how to show all 30 days, even days with a zero value.

Since you are using MariaDB 10.0 or newer, there is a nifty trick to give all the days in a range:

MariaDB [test]> SELECT '2019-01-01' + INTERVAL seq-1 DAY  AS dates   FROM seq_1_to_31;
+-----------------------------------+
| dates                             |
+-----------------------------------+
| 2019-01-01                        |
| 2019-01-02                        |
| 2019-01-03                        |
| 2019-01-04                        |
| 2019-01-05                        |
| 2019-01-06                        |  etc.

So, what you do is

SELECT ...
    FROM ( select using seq table ) AS dates
    JOIN ( your table ) AS yours ON dates.dy = yours.o_date
    WHERE ...

Your secondary question about how to ask for a date range -- both of your attempts give the same result with the same performance.

How to SELECT the last 30 days records from SQL, including days with zero?

4 Answers4