get all dates in the current month
You can create a dynamic table of dates for the current month
SELECT date_field
FROM
(
SELECT
MAKEDATE(YEAR(NOW()),1) +
INTERVAL (MONTH(NOW())-1) MONTH +
INTERVAL daynum DAY date_field
FROM
(
SELECT t*10+u daynum
FROM
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) B
ORDER BY daynum
) AA
) AAA
WHERE MONTH(date_field) = MONTH(NOW());
NOTE: If you cut-and-paste the above query as is, it will generate the whole month for you
You then LEFT JOIN this to your original query
SELECT
AAA.date_field,
IFNULL(BBB.val,0) val
FROM
(
SELECT date_field
FROM
(
SELECT MAKEDATE(YEAR(NOW()),1) +
INTERVAL (MONTH(NOW())-1) MONTH +
INTERVAL daynum DAY date_field
FROM
(
SELECT t*10+u daynum FROM
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
) AA
) AA WHERE MONTH(date_field) = MONTH(NOW())
) AAA LEFT JOIN (SELECT date_field,val FROM MY_TABLE) BBB
USING (date_field);
Give it a Try !!!
First, the condition WHERE date_field >= (CURDATE()-INTERVAL 1 MONTH)
will not restrict your results to the current month. It will fetch all dates from 30-31 days ago up to the current date (and to the future, if there are rows with future dates in the table).
It should be:
WHERE date_field >= LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH
AND date_field < LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY
Now, to the main question, to create 28-31 dates, even if the table has not rows for all the dates, you could use a Calendar
table (with all dates, say for years 1900 to 2200) or create them on the fly, with something like this (the days
table can be either a temporary table or you can even make it a derived table, with a somewhat more complicated query than this one):
CREATE TABLE days
( d INT NOT NULL PRIMARY KEY ) ;
INSERT INTO days
VALUES (0), (1), (2), ....
..., (28), (29), (30) ;
SELECT
cal.my_date AS date_field,
COALESCE(t.val, 0) AS val
FROM
( SELECT
s.start_date + INTERVAL (days.d) DAY AS my_date
FROM
( SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH
AS start_date,
LAST_DAY(CURRENT_DATE)
AS end_date
) AS s
JOIN days
ON days.d <= DATEDIFF(s.end_date, s.start_date)
) AS cal
LEFT JOIN my_table AS t
ON t.date_field >= cal.my_date
AND t.date_field < cal.my_date + INTERVAL 1 DAY ;
The above should work for any type of the date_field
column (date, datetime, timestamp). If the date_field
column is of type DATE
, the last join can be simplified to:
LEFT JOIN my_table AS t
ON t.date_field = cal.my_date ;