Mysql dates of the last 30 days

Question

I need help with some dates in mysql

I have this query that brings me the count of the messages of the corresponding dates of every message from the last 30 days.

SELECT DATE_FORMAT(time, '%m/%d/%Y') AS Dates, count(*) as count 
FROM ma_messages 
WHERE time BETWEEN NOW() - INTERVAL 30 DAY AND NOW() 
AND usersid_send = 110 
Group by Dates 
ORDER BY Dates ASC

query image

But I need that also bring me the other dates of the last 30 days with a value in the count of 0, for example that in the query also bring me 05/26/2021 date and the dates before 05/25/2021 and after 05/28/2021.

I don't know if this is possible, but I will apreciate any help.

Thanks.

Generate dates list (calendar) and leftjoin your data to it. — Akina, Jun 08 '21 at 18:12
Hello, I have a table with dates but I'm new in this and I don't know how to do that leftjoin but thank you anyways — Alexis Murillo, Jun 08 '21 at 18:21
*I have a table with dates* It includes all dates? including the dates which are absent in data table? — Akina, Jun 08 '21 at 18:23
Does this answer your question? [MySQL how to fill missing dates in range?](https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) — pilcrow, Jun 08 '21 at 20:03

score 0 · Answer 1 · answered Jun 08 '21 at 18:24

0

try this

SELECT 
DATE_FORMAT(time, '%m/%d/%Y') AS Dates, 
count(if(usersid_send='110',true,null)) as count 
FROM ma_messages 
WHERE time BETWEEN NOW() - INTERVAL 30 DAY AND NOW() 
Group by Dates 
ORDER BY Dates ASC

answered Jun 08 '21 at 18:24

kilin

1

Ok, it bring me like I said the 05/26/2021 date and also the 06/07/2021 date but the rest of the days of the last 30 days is not bring it, thank you anyways, maybe I can do something with this – Alexis Murillo Jun 08 '21 at 18:29

score 0 · Answer 2 · answered Jun 08 '21 at 18:31

0

I have a table with dates .. with all the dates of the year – Alexis Murillo

SELECT d.`date`, COUNT(m.`time`) AS `count`
FROM dates_table d
LEFT JOIN ma_messages m ON d.`date` = DATE(m.`time`)
WHERE d.`date` BETWEEN NOW() - INTERVAL 30 DAY AND NOW() 
  AND m.usersid_send = 110 
GROUP BY d.`date`
ORDER BY d.`date` ASC

answered Jun 08 '21 at 18:31

Akina

39,301
5
14
25

It doesn't bring me the other dates of the last 30 days with count 0, but at least it has the left join with the dates table, I appreciate anyways – Alexis Murillo Jun 08 '21 at 20:01

score 0 · Answer 3 · answered Jun 09 '21 at 13:52

Using your table "with all the dates of the year":

WITH
message_counts AS (  SELECT DATE_FORMAT("time", '%m/%d/%Y') AS "day", 
                            COUNT(*) AS "count" 
                       FROM ma_messages 
                      WHERE "time" BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
                            AND
                            usersid_send = 110 
                   GROUP BY Dates)                         
   SELECT all_dates."day", COALESCE(message_counts."count", 0)
     FROM all_dates
LEFT JOIN message_counts
          ON all_dates."day" = message_counts."day"
    WHERE all_dates."day" BETWEEN BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
 ORDER BY all_dates."day" ASC

Mysql dates of the last 30 days

3 Answers3