Getting missing dates from a database via MySQL

Question

So, I have a table where I collect data for the tasks that I do. Each time I create a task, I assign a date to it. The problem with this is the days when I do not have tasks, they are not stored in the database, so when I draw my data, I never see the days when I had zero tasks.

My current request looks like this:

SELECT job_data_date, SUM(job_data_invoice_amount) as job_data_date_income FROM job_data WHERE job_data_date >= '2010-05-05' GROUP BY job_data_date ORDER BY job_data_date; 

Conclusion:

 | job_data_date | job_data_date_income | | 2010-05-17 | 125 | | 2010-05-18 | 190 | | 2010-05-20 | 170 | 

As you can see from the example, the conclusion 2010-05-19 will not be displayed in the results, because it was never stored there.

Is there a way to show missing dates?

Thanks,

Marat

Answer 1

One idea is that you can have a table with all the dates in it that you want to show, and then do an outer join with that table.

So, if you have a table called alldates with one column (job_data_date):

 SELECT ad.job_data_date, SUM(job_data_invoice_amount) as job_data_date_income FROM alldates ad left outer join job_data jd on ad.job_data_date = jd.job_data_date WHERE ad.job_data_date >= '2010-05-05' GROUP BY ad.job_data_date ORDER BY ad.job_data_date; 

The downside is that you will need to populate this table with all the dates you want to show.

Answer 2

There is no reasonable way to do this using pure SQL, at least MySQL, without creating a table with every date ever developed. Your best option is to modify the application that uses the results of this query to fill the holes themselves. Instead of displaying only the received values, build your own set of values ​​with a simple loop; counting one day at a time, filling in the values ​​from the query, wherever they are.