What is wrong with my query - Getting inaccurate results

Grafana Time Series Panel
1

I’m excited to have just got this installed and working but I am not getting accurate results. I am trying to count the number of occurrences of each provider’s name in a table. Can you please tell me what I am doing wrong? Thanks

Details:
5.0 Beta
Graph
Source = MySQL
Time Range:
Override relative time Last = 10000h
Add time shift Amount = 0h
Display:
Bars
Mode = Series
X-Axes = Total, Count

This is not show accurate results
Metrics:

select UNIX_TIMESTAMP(datestamp) as time_sec,
– count(*) as ‘value’,
id as ‘value’,
Provider as ‘metric’
FROM report
WHERE $__timeFilter(datestamp)
– group by datestamp
– ORDER BY datestamp ASC

This is showing accurate results
MySQL Query:

select
count(*) as ‘value’,
Provider as ‘metric’
FROM report
group by Provider
ORDER BY datestamp ASC;

2

Hi,

You probably need to group by both time and metric, i.e.

select UNIX_TIMESTAMP(datestamp) as time_sec,
count(*) as value,
Provider as metric
FROM report
WHERE $__timeFilter(datestamp)
group by 1, 3
ORDER BY 1

Please note that I would strongly suggest against using last 10000h as time if you have a lot of data. If you have lots of data I would suggest that you create a query that only returns one row per each value and metric where you have to hard set timestamp to datetime of now for example (just to get a timestamp).

Good luck

Marcus

3

Thanks Marcus,

It gave me the same results. This is for a demo and has 3100 rows with about 30 different providers. I change all of the dates in the table to today while I am testing but thanks for letting me know about 10000h. I bet that would cause some issues on real data.

Thanks,
Kevin

4

I found the problem. It was my fault. The current time is only 16:41 and I had the end time set to 21:00.

SET @MIN = ‘2018-02-19 00:30:00’;
SET @MAX = ‘2018-02-19 21:00:00’;
update report
set datestamp = TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN);