How to include missing days into my query?

Question

below is the mySQL query I currently use

SELECT Date( timestamp ) AS day, Count(id) AS total 
FROM goals_data 
WHERE company = 1
AND Date( timestamp ) BETWEEN Subdate( Curdate( ) , 10 ) AND Curdate( ) 
GROUP BY day desc

in order to get the output from the mySQL as:

2018-04-09 ------ 3
2018-04-08 ------ 6
2018-04-07 ------ 2
2018-04-05 ------ 4

The problem is that, as you can see from the example output above, there is no 2018-04-06 because there were no any entries that day.

What I want to achieve is to have a consequence of days, even if there are no entries found.

So the optimum is:

2018-04-09 ------ 3
2018-04-08 ------ 6
2018-04-07 ------ 2
2018-04-06 ------ 0
2018-04-05 ------ 4

How can I do this by editing my query?

Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). — Strawberry, Apr 11 '18 at 12:31
Also, if you can think of a way to remove the `Date( )` bit in Date( timestamp )`, the query will be much more efficient (assuming timestamp is indexed) — Strawberry, Apr 11 '18 at 12:35
Why? The alternative, is faster, and more scalable - and why mention PHP otherwise? — Strawberry, Apr 11 '18 at 12:36
You might want to use a separate table that keeps the dates and do a join with your `goals_data` table. This could be a good example to look at http://www.brianshowalter.com/calendar_tables — Rico Chen, Apr 11 '18 at 12:41
@Strawberry the reason I dont want to use PHP is because I use this query in numerous ways, and one of this is "insert". The PHP was suggested by the question thread. — EnexoOnoma, Apr 11 '18 at 12:41
If you absolutely want to do it at DB level, then. 1. Find a way to generate dates between your `min` and `max` dates as a subquery. 2. Do a left join from this subquery to your existing query. — Utsav, Apr 11 '18 at 12:42
Possible duplicate of [generate days from date range](https://stackoverflow.com/questions/2157282/generate-days-from-date-range) — Will B., Apr 11 '18 at 12:51
In general MySQL would need some form of generating the date range, which is not easily achievable or as maintainable, as shown in the question I marked as a duplicate. Alternatively as Stawberry suggested, you could define the date range using [`DatePeriod`](http://php.net/manual/en/class.dateperiod.php) and `DaeTime` and query them specifically in your between. This way you retain the desired data, where the reporting logic (date range) is controlled by PHP and issued as a query. Allowing the query to be expanded upon easily: https://3v4l.org/iF2u7 — Will B., Apr 11 '18 at 12:53

Will B. · Accepted Answer · 2018-04-11T20:02:48.327

As mentioned in my comments and as suggested by others, you would need to generate the date range in MySQL.

Using the answer I marked as a duplicate to generate the date range, you would then add your query as a LEFT JOIN to retrieve the result set totals, matching on the generated date to the goals_data timestamp, moving the company criteria to a subquery.

SELECT a.Date as day, COUNT(gd.id) AS total
FROM (
    SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    FROM (SELECT 0 as a union all SELECT 1 union all SELECT 2 union all SELECT 3 union all SELECT 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9) as a
    CROSS JOIN (SELECT 0 as a union all SELECT 1 union all SELECT 2 union all SELECT 3 union all SELECT 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9) as b
    CROSS JOIN (SELECT 0 as a union all SELECT 1 union all SELECT 2 union all SELECT 3 union all SELECT 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9) as c
) a
LEFT JOIN (SELECT * FROM goals_data WHERE company = 1) AS gd
ON DATE(gd.`timestamp`) = DATE(a.Date)
WHERE DATE(a.Date) BETWEEN SUBDATE(CURDATE(), INTERVAL 10 DAY) AND CURDATE()
GROUP BY day DESC;

Which produces: http://www.sqlfiddle.com/#!9/17f03f/2

|        day | total |
|------------|-------|
| 2018-04-11 |     0 |
| 2018-04-10 |     0 |
| 2018-04-09 |     3 |
| 2018-04-08 |     6 |
| 2018-04-07 |     2 |
| 2018-04-06 |     0 |
| 2018-04-05 |     4 |
| 2018-04-04 |     0 |
| 2018-04-03 |     0 |
| 2018-04-02 |     0 |
| 2018-04-01 |     0 |

Alternatively as I suggested in the comments, you can use PHP to generate the desired date range for the report to query against.

$interval = new \DateInterval('P10D');
$currentDate = new \DateTime;
$previousDate = clone $currentDate;
$previousDate->sub($interval);
/*
$query = 'SELECT Date( timestamp ) AS day, Count(id) AS `count`
FROM goals_data 
WHERE company = 1
AND Date( timestamp ) BETWEEN :previous_date AND :current_date
GROUP BY day desc'
*/
$dbDates = array_column($dbRows, null, 'day');
$period = new \DatePeriod($currentDate, DateInterval::createFromDateString('-1 day'), $interval->d);
foreach ($period as $date) {
    $key = $date->format('Y-m-d');
    if (!array_key_exists($key, $dbDates)) {
        $dbDates[$key] = ['day' => $key, 'count' => 0];
    }
    echo $dbDates[$key]['day'] . ' ----- ' . $dbDates[$key]['count'] . \PHP_EOL;
}

Producing the same results: https://3v4l.org/YrpU8

2018-04-11 ----- 0
2018-04-10 ----- 0
2018-04-09 ----- 3
2018-04-08 ----- 6
2018-04-07 ----- 2
2018-04-06 ----- 0
2018-04-05 ----- 4
2018-04-04 ----- 0
2018-04-03 ----- 0
2018-04-02 ----- 0
2018-04-01 ----- 0

Hi there, thank you for your great answer! Can you please explain me the edit you made in the query, as opposed to your first attempt? I have tested both, and both running perfectly fine. — EnexoOnoma, Apr 11 '18 at 20:08
The initial company criteria would not be filtered out as it is a left join. So I added multiple dates under different companies to test. Thereby requiring the criteria to be filtered in a subquery. This is because your base SELECT FROM is generating dates to which you're joining on. — Will B., Apr 11 '18 at 20:09

score 0 · Answer 2 · answered Apr 11 '18 at 13:44

0

I've previously achieved this by using a separate table/query already containing the date range. You can then left join along the date table so all of the dates were included along with matching values from the right table.

answered Apr 11 '18 at 13:44

Chris

17
5

Hi, can you provide a solution to my question? – EnexoOnoma Apr 11 '18 at 14:43

How to include missing days into my query?

2 Answers2