6

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?

6

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 ;
  • Thanks alot for your quick answer, i had to wrap this up in a stored procedure and i used SELECT DAY(LAST_DAY(CURDATE())) last_d; to get the current month's total days to populate the days table in a loop. But i immediately bumped into a problem, when two concurrent users are accessing the system and working on different months – indago Apr 14 '15 at 9:48
  • 1
    If the days table is fixed and has 31 rows, why would there be a problem (even if a million queries use it)? It (the days table as in the answer) should be created once and not every time, by every run of a stored procedure. – ypercubeᵀᴹ Apr 14 '15 at 9:51
  • i think that was my problem creating the table inside the stored proc. let me redo it then let you know – indago Apr 14 '15 at 10:00
  • Your solution works perfect, no hitches at all, quite simple and fast! But i need to compare it with @Ronalo's compact solution and use huge data to clearly benchmark the best solution. I have tested it on a table with only 4230 rows, i want to use a table with more than 500,000 records to have a clear picture. So far your solution is the best! thumbs up! – indago Apr 15 '15 at 6:19
7

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

  • I indeed tried out your solution, and it works fine thumbs up for that! Though it took me afew minutes to digest it, once i got it i had to smile! But compared to @ypercube 's solution yours takes slightly more millisecs to execute. – indago Apr 15 '15 at 6:01
  • am just curious, why did you have to put a full select on the left join on the second last statement instead of just using MY_TABLE BBB? – indago Apr 15 '15 at 7:37
  • Hi! Can you advise on how to adapt your answer to get the current QUARTER instead of the current MONTH? – Joum Jul 27 '17 at 15:51
  • i'm gonna use this to list all dates in current year – computingfreak Aug 30 '17 at 8:52
0
------ 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

  • I suppose you meant "SQL-Server" where you wrote "SQL" – ypercubeᵀᴹ Apr 14 '15 at 11:36
  • 1
    And I don't see the point of this answer. There are so many things that need to be changed so it works in mysql (EOMONTH, GETDATE, WITH, CONVERT, brackets, missing ;, ...) – ypercubeᵀᴹ Apr 14 '15 at 11:38
  • And casting to varchar using the mm/dd/yyyy format? Wow, ..., why? You seem to use a decent format in the (unneeded cast in the) INSERT statement – ypercubeᵀᴹ Apr 14 '15 at 12:08
  • Thanks for your effort but i needed a MySQL solution not an SQL-Server one. – indago Apr 15 '15 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.