How to select last 30 days dates in MySQL even the dates are not present in mysql?

Question

I have a situation here where i need to grab data from past 30 days and get all the dates even if the data is not present in mysql for given dates.

my current query is something like this. lastUpdated is a timestamp column

SELECT Date(a.lastUpdated), count(*)
FROM table1 a
LEFT JOIN table2 b on (a.pgid = b.prod_id)
WHERE Date(lastUpdated) BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY Date(a.lastUpdated);

This return result like this

Date(a.lastUpdated)   count(*)
2016-03-23              1
2016-03-24             14
2016-03-30             65
2016-03-31              1
2016-04-02              1

My question is if its possible to list out all the dates even if the data is not in the mysql. Or any other workaround. I am using PHP 7.0.

I looked around SO but didn't find anything related to my requirements.

there's no easy way IMHO. You might find this one useful, allthough not directly related : http://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present — Gogol, Apr 02 '16 at 19:46
if your data does not have information that can be used for selection and filtering, then it does not have information that can be used for selection and filtering. Imagine you know nothing about what the data is *about*, and you've been asked to manually go through it and find records from the last 30 days. You find a record without a date. What do you do? Unless your instructor tells you how to treat dateless entries, you're going to have to ignore them. Same here - either say what needs to happen, or SQL ignores them. — Mike 'Pomax' Kamermans, Apr 02 '16 at 19:46
If the data is not being saved on the database, there's no way to get it. You *can* see what the last time a table was updated, but MySQL doesn't store this information by row. — andrechalom, Apr 02 '16 at 19:47
Ah. Read it again, and I think I understand you now. You need to SELECT blah blah WHERE blah blah blah **OR lastUpdate IS NULL**. — andrechalom, Apr 02 '16 at 19:50
@CharlotteDunois I added more records from my result. You see that it doesnt list out dates not present in table. Looks like my only option is to create a calendar table — Sameer Kumar Jain, Apr 02 '16 at 19:55

Guido · Accepted Answer · 2016-04-05T21:08:30.597

2

Assuming a simplified example table table1 with the following contents

CREATE TABLE table1 (lastUpdated datetime);
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-23');
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-24');
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-30');
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-31');
INSERT INTO table1 (lastUpdated) VALUES ('2016-04-02');
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-31');

Then the following MySQL statement will return all dates in the past 30 days, with a count of 0 where no entry is found in table1:

SELECT lastUpdated, count(*)-1 FROM (
    SELECT date(lastUpdated) as lastUpdated FROM table1 as t1
    UNION ALL 
    SELECT curdate() - interval a day AS lastUpdated FROM (
        select 0 as a 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 union select 10 union select 11 union
        select 12 union select 13 union select 14 union select 15 union 
        select 16 union select 17 union select 18 union select 19 union
        select 20 union select 21 union select 22 union select 23 union
        select 24 union select 25 union select 26 union select 27 union
        select 28 union select 29
    ) as t2
) as t3 GROUP BY lastUpdated;

edited Apr 05 '16 at 21:08

answered Apr 02 '16 at 20:38

Guido

876
5
14

1

You can always use http://sqlfiddle.com/ which is an online test for some DBs including MySQL :) – Prix Apr 02 '16 at 21:06
@Prix Thanks a lot, and I already used it to amend the answer I was aware of jsfiddle but somehow completely missed out on phpfiddle and sqlfiddle… – Guido Apr 02 '16 at 21:27
@Guido Thank you. I will try this. Would this work better if I have thousands of rows. Just wanted to have best option available. Keep it positive. – Sameer Kumar Jain Apr 03 '16 at 08:34
@Guido This wont work because it will still return 0 if I have 1 record for a date. Do you have a solution for this. Also it return 32 rows and it return 2 result set for current date. – Sameer Kumar Jain Apr 04 '16 at 12:47
@SameerJain Can you provide more example data? The query returned 30 rows and the expected counts for the example in your question – see http://sqlfiddle.com/#!9/bb0a9/1 – Guido Apr 04 '16 at 19:20
@SameerJain Your sqlfiddle data contains dates+times, while my answer only assumed dates. I've updated my answer by replacing `SELECT lastUpdated FROM table1 as t1` with `SELECT date(lastUpdated) as lastUpdated FROM table1 as t1` to make the query only operate on the date part of the `lastUpdated` field. Now, this returns 3 counts for April 2 and 2 counts for April 4. – Guido Apr 05 '16 at 21:11

How to select last 30 days dates in MySQL even the dates are not present in mysql?

1 Answers1

Linked