MySQL - How to show all days records in particular month?

Question

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a 
where a.Date between '2015-04-15' and '2015-05-15' ORDER BY Date

Above query working fine I am getting 30 records from selected date to selected date. But I want to show complete month record. If i choose February then all 28 days(29 if leap year) record will showing. Same as Mar = 31 records April = 30 records. etc.

EDIT : See screenshot. I want to show all days in a month.

enter image description here

If possible to PHP please post your answer.

`February` alone can't decide if its 28 or 29 you need to pass the year as well. How will you choose months ? — Abhik Chakraborty, May 18 '15 at 10:26
Yes I will add year leter. But not i want to show all days record. `28` or `29` doe's not matter. — Developer, May 18 '15 at 10:27
Yes I got it , but my question was what input would be supplied to mysql for the date calculation ? — Abhik Chakraborty, May 18 '15 at 10:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78081/discussion-between-chatfun-and-abhik-chakraborty). — Developer, May 18 '15 at 10:42

score 9 · Accepted Answer · answered May 18 '15 at 11:20

If you know your input year and month then you can always set the first day, say the input is Y = 2012 M=02 ,the first day would be always 2012-02-01 and using that date you can get the last day and then the dates in that range. Something as

select a.Date 
from (
    select last_day('2012-02-01') - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a 
where a.Date between '2012-02-01' and last_day('2012-02-01') order by a.Date;

+------------+
| Date       |
+------------+
| 2012-02-01 |
| 2012-02-02 |
| 2012-02-03 |
| 2012-02-04 |
| 2012-02-05 |
| 2012-02-06 |
| 2012-02-07 |
| 2012-02-08 |
| 2012-02-09 |
| 2012-02-10 |
| 2012-02-11 |
| 2012-02-12 |
| 2012-02-13 |
| 2012-02-14 |
| 2012-02-15 |
| 2012-02-16 |
| 2012-02-17 |
| 2012-02-18 |
| 2012-02-19 |
| 2012-02-20 |
| 2012-02-21 |
| 2012-02-22 |
| 2012-02-23 |
| 2012-02-24 |
| 2012-02-25 |
| 2012-02-26 |
| 2012-02-27 |
| 2012-02-28 |
| 2012-02-29 |
+------------+
29 rows in set (0.00 sec)

Gharbad The Weak · Answer 2 · 2020-03-13T19:31:34.740

Just came across this post while looking for an answer to the problem listed in the OP, Abhik Chakraborty's answer works fantastically.

I thought I would post an example of how to use the answer if you are looking to select data from a table and want to have every day in a month listed regardless if there is data in your table for a particular day in the month.

Basically what you have to do is to use the answer above to create a derived table that you can LEFT JOIN your existing table to so every day in the month will be listed and you can display data from your table for the days where it is present. This query is copy/paste ready, all you need to do is change the references to yourTable to be the table name you want to get the data from.

Here's the example query:

SELECT `dateList`.`Date`,
    CASE WHEN `yt`.`date` IS NULL THEN 0
    ELSE COUNT(`yt`.`id`) 
    END AS `amt`
FROM

-- this is the part you can copy/paste 
-- to be used to left join in a table of your choice 
-- ---------------------------------------------------------------------------------------------------------------------------
(
    SELECT `a`.`Date`
    FROM (
        SELECT LAST_DAY('2020-02-01') - INTERVAL (`a`.`a` + (10 * `b`.`a`) + (100 * `c`.`a`)) DAY AS `Date`
        FROM (SELECT 0 AS `a` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS `a`
        CROSS JOIN (SELECT 0 AS `a` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS `b`
        CROSS JOIN (SELECT 0 AS `a` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS `c`
    ) AS `a` 
    WHERE `a`.`Date` between '2020-02-01' and LAST_DAY('2020-02-01')
) AS `dateList` 
-- ---------------------------------------------------------------------------------------------------------------------------

LEFT JOIN `yourTable` AS `yt` ON `dateList`.`Date` = DATE(`yt`.`date`)
GROUP BY `dateList`.`Date`
ORDER BY `dateList`.`Date` ASC

Here is the example in sql fiddle.

Hope this helps, it sure helped me.

score 1 · Answer 3 · answered Dec 20 '21 at 09:02

Using user defined variables is more clean and simple:

    select a.Date
from (
    select last_day(@firstDay) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select @firstDay:='2012-02-01') var
) a
where a.Date between @firstDay and last_day(@firstDay) order by a.Date;

And if you want strict range between two dates just add @lastDay:

select a.Date
from (
    select @lastDay - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select @firstDay:='2012-02-04',@lastDay:='2012-08-15') var
) a
where a.Date between @firstDay and @lastDay order by a.Date;

score 0 · Answer 4 · edited May 18 '15 at 10:43

Below is an example of two queries which could be used to get all the records you need. The first query will give you records from 5months before the current date and the second query will give you records from the first day of the current month.

These are just examples, so you can adapt these queries to show the relevant information you need.

Timestamp/Date, this is can be either a date or either a timestamp depending on the format you've chosen to store the date into your datebase

Example 5 months before current date

select * from table where timestamp/date >= now() - interval 5 month;

Example 5 months before first day of month

select * from table where timestamp/date >= last_day(now()) + interval 1 day - interval 5 month;

MySQL - How to show all days records in particular month?

4 Answers4

Linked