get all dates in the current month

Question

i have a query in MySQL which serves me very well by getting all the records within the current month;

SELECT date_field,val FROM MY_TABLE WHERE date_field>=(CURDATE()-INTERVAL 1 MONTH);

The above query works well. so if this month we only had two records and 28 days it will bring only two records.

date_field | val
========================
2015-02-08 | 567
2015-02-09 | 345

But i want the number of records returned to be exactly the same as the number of days of the current month. if the current month has 28 days and only had two records it should bring;

date_field | val
========================
2015-02-01 | 0
2015-02-02 | 0
2015-02-03 | 0
2015-02-04 | 0
2015-02-05 | 0
2015-02-06 | 0
2015-02-07 | 0
2015-02-08 | 567
2015-02-09 | 345
2015-02-10 | 0
2015-02-11 | 0
2015-02-12 | 0
2015-02-13 | 0
2015-02-14 | 0
2015-02-15 | 0
2015-02-16 | 0
2015-02-17 | 0
2015-02-18 | 0
2015-02-19 | 0
2015-02-20 | 0
2015-02-21 | 0
2015-02-22 | 0
2015-02-23 | 0
2015-02-24 | 0
2015-02-25 | 0
2015-02-26 | 0
2015-02-27 | 0
2015-02-28 | 0

How can i modify my query to achieve the above result?

Answer 1

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 ;

Answer 2

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 !!!

Answer 3

------ Create Exam Table


DECLARE @MyTable TABLE
(date_field DATETIME
,val INT )

INSERT INTO @MyTable
        ( date_field, val )
VALUES  ( CAST('2015-04-05' AS VARCHAR), -- date_field - datetime
          270  -- val - int
          )
INSERT INTO @MyTable
        ( date_field, val )
VALUES  ( CAST( EOMONTH ( GETDATE() ) AS VARCHAR), -- date_field - datetime
          500  -- val - int
          )



DECLARE @startDate DATETIME=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy
DECLARE @endDate DATETIME= CAST( EOMONTH ( GETDATE() ) AS VARCHAR) -- mm/dd/yyyy
;WITH Calender AS 
(
    SELECT @startDate AS CalanderDate
    UNION ALL
    SELECT CalanderDate + 1 FROM Calender
    WHERE CalanderDate + 1 <= @endDate
)
SELECT CONVERT(VARCHAR(10),CalanderDate,25) AS [DATE], ISNULL(MT.val,0) val
FROM Calender
LEFT JOIN @MyTable MT ON Calender.CalanderDate = MT.date_field

You Can Use LAST_DAY() Function in MySQL Instead EOMONTH() in SQL

Answer 4

For the accurate answer and count you have to set date in group by function and counted value in count function :

SELECT
    AAA.EventDate,
    IFNULL(count(BBB.EventId),0) EventId
FROM
(
    SELECT EventDate
    FROM
    (
        SELECT MAKEDATE(YEAR(NOW()),1) +
        INTERVAL (MONTH(NOW())-1) MONTH +
        INTERVAL daynum DAY EventDate
        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(EventDate) = MONTH(NOW())
) AAA LEFT JOIN (SELECT EventDate,EventId FROM calander_events) BBB
USING (EventDate) Group by EventDate