MySQL Graph Displaying missing dates as connected instead of zero

So I’m using a graph panel in order to illustrate the number of packages sent from a provider every day, however on the time-span where there were no packages sent, the graph is not putting these values to zero as I would prefer it to. Instead it connects them as if it were obeying the “if NULL: connected” parameter, however it obviously isn’t NULL, it just doesn’t exist… I’ll have a picture and a snippet of the query in order to illustrate this below:

SELECT DATE(do_timestamp) as time, 
count(do_timestamp) as value,
'Packages Sent' as metric
FROM <data table>
WHERE $__timeFilter(do_timestamp)
GROUP BY DATE(do_timestamp)
ORDER BY DATE(do_timestamp) ASC;

If anyone knows how to send nonexistent date values to zero that’d be awesome!

I agree, I hate this! :slight_smile:

I use second table ‘calendar’ which I join to ‘main_table’. ‘Calendar’ contains all daites and then you got date/(hours in your case) whit zero value…

Unfortunately I only have SELECT/SHOW permissions so altering the database or adding anything as far as I’m aware is not possible. I’m pretty new to MySQL and SQLin general, so if I’m missing something here, do let me know.

You can try this:

SELECT $__timeGroup(do_timestamp, ‘5m’,0) as time,
count(do_timestamp) as value,
‘Packages Sent’ as metric
FROM
WHERE $__timeFilter(do_timestamp)
GROUP BY DATE(do_timestamp)
ORDER BY DATE(do_timestamp) ASC;

I think that can serve you

1 Like

This did! Thank you! I feel so dumb, I was using the __timeGroup() macro to begin with but for some reason opted to not use it at some point…

Yes! This is a good tip! Thank you!

I used it on daily graphs with it:

SELECT $__timeGroup(day, '1440m', 0) AS time_sec,
...
1 Like

Using $__timeGroup(do_timestamp, ‘5m’,0) has one issue though. This way Grafana is the one deciding when should be the starting time and end time of aggregation. Which for instance in $__timeGroup(do_timestamp, ‘43800m’,0) it it very likely that it starts e.g. from 2019-01-20 till 2019-02-19 instead of from 2019-02-01 till 2019-02-28.