Issues with stacked bar and $__timeGroup macro and Mysql

I wanna report some issues, tested with latest version 5 (nightly build), run with
Linux 2.6.32-431.11.2.el6.x86_64 #1 SMP Tue Mar 25 19:59:55 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
Data source used is Mysql latest version
I have two issues to report:

  • timeGroup macros:
  • cannot use ‘1d’ in the macro $__timeGroup(SubmissionTime, ‘1d’)
  • date displayed wrongly using macro timeGroup ( one day mismatch), I doubt that it’s because of ‘/’ operator used in the macro, i tried DIV keyword and dates display correctly
    Basically i wanna plot metric taken from Mysql db, with kind of 3 columns (time, instance_id, status)

select
cast(cast(UNIX_TIMESTAMP(time) DIV 86400 as signed)86400 as signed) as time_sec,
count() as value,
status as metric
from wq.as
where time > now() - interval 60 day
group by 1, status
;

time_sec|value|metric
1512691200 | 106 | SUCCESS
1512864000 | 11 | FAIL
1512864000 | 3027 | SUCCESS
1513468800 | 494 | FAIL
1513641600 | 3912 | FAIL
1513641600 | 92 | SUCCESS
1513900800 | 218 | FAIL
1514073600 | 10514 | FAIL
1514073600 | 2883 | SUCCESS
1514678400 | 1088 | FAIL

If I use $__timeGroup(SubmissionTime, ‘24h’) as time_sec, the date displayed is wrong (one day mismatch from 17/12 → 18/12)

  • fill (0) data points:
    in previous query result, due to sql group by, e.g. it misses data point on date 1513900800, there is no SUCCESS instances on that day, so it displays weird on the graph
    I tried searching for solutions but not succeed yet, if i fix it by sql query, the query will be fairly complicated. Can grafana add a feature like fill(0), similar to the one supported in influxDB
    we had a similar graph (using this query) running on Metabase dashboard and it displays well (think it has sth like fill(0) on missing data point)
    I also ried Null as zero in the Display tab but does not help

The wrong date seems to be from non-UTC timezone, $__timeGroup days align to date boundary in UTC. For the fill parameter there is already a pull request to implement that functionality:
See https://github.com/grafana/grafana/pull/10138

1 Like

here is my display setting

I tried timezone option default/UTC but that does not help
not sure if it’s a bug

With stacked bars you need to supply a value for every interval otherwise bars will bug out. So you either need the fill option or write your query so it returns values for every interval.

In PostgreSQL it is relatively straightforward but I dont think it could be easily modified to run on mysql but maybe you can use it as inspiration:
https://github.com/grafana/grafana/issues/10073

Depending on the mysql version you use you can achieve a similar result with common table expressions.

I tried with mysql query but it’s quite complicated, and i need to plot several stacked bar graphs later on so I think fill option is still much better.
Your pull request do u have any idea when it will be merged ? or any way i can try that out

About the one day mismatch, i think this one provide accurate result compared to timeGroup macro
cast(cast(UNIX_TIMESTAMP(time) DIV 86400 as signed)86400 as signed) as time_sec

@daniellee
I wanna ask about fill 0 feature for mysql data source, the pull request of svenklemm https://github.com/grafana/grafana/pull/10138
has some conflicts to resolve, this is from 2 months ago, is there any way to merge this feature for the upcoming version 5 ?
Thanks a lot

Hi @daniellee can u take a look please,
This fill(0) feature is the last blocker in my project :(,
is there any plan to add this feature for regional database like mysql in version 5
as mentioned above there is a pull request already, but it has conflict in a file
https://github.com/grafana/grafana/pull/10138

Many thanks

Hi,

We’re very busy at the moment working with the 5.0 release. Not sure we have time to merge this. That’s the most answer I can give you. Please subscribe to issue/PR on Github and you’ll get notified if there are any progress on there.

If you really need this I suggest that you build Grafana from source targeting above pull request until PR have been merged to master.

Marcus

1 Like

Thanks for informing Marcus, that’s all i need
I know you guys are busy, i will wait for your release then :slight_smile:

Hi,
i have a question regarding this timeGroup function, when i use

$__timeGroup(updatetime,‘7d’, 0) as time,

The data is group weekly but start from Thursday, can it be from Monday as most of users expect ?
Also from the Query Insector, it seems the time is deviated by 7 hours
I would expect something like
2020-06-22 00:00:00
2020-06-29 00:00:00