Use graph with mysql and non time metrics

1

Hi there, I want to display a graph with mysql as data source, but as series.

In my example, I have a set of people, and the year when they got born. I want to construct a graph now, which shows how often people are born in year x. Currently I tried this via something like:

SELECT year,
COUNT(year)
FROM peoples
WHERE year IS NOT NULL
GROUP BY year;

This leads into a weird situation: Grafana says, “No field name specified to use for x-axis, check your axes settings”, but when this happens, I’m not able to change the X-Axis Mode. Any advice?

2

Non time series data in graph panel is not supported right now .

1 Like
3

I too have the same issue with elastic search.Is any solution to fix this issue ???

5

May I kindly ask when this great feature is going to be supported?
BTW: I’m using Postgres underneath Grafana.

6

If non-time-series data is not supported, why does the documentation say the following?

The Series option means that the data is grouped by series and not by time. The y-axis still represents the value.

Visualizations | Grafana documentation

I have data coming back from postgres as a resultset of ‘category’ (string): ‘count’ (numeric). How can I create a basic bar chart to show this data?

7

You can try this

SELECT
Count (year) as value,
Concat(‘year-’, year) as metric,
Unix_timestamp(now()) as time_sec
FROM peoples
Group by year

And config de x-axis as series…

IMG_20180702_082130
IMG_20180702_0821301080×411 45.4 KB

1 Like
8

Hi
i have below data into MySQL, still get error “No field name specified to use for x-axis, check your axes settings” Unable to select X-Axis Mode
Checked SQL query , it is converting below data into timestamp format. so query looks okay.

time Voltage
2018-07-28 01:00:00 42
2018-07-28 02:00:00 42
2018-07-28 03:00:00 43
2018-07-28 04:00:00 40
2018-07-28 05:00:00 40
2018-07-28 06:00:00 41
2018-07-28 07:00:00 43
2018-07-28 08:00:00 46
2018-07-28 09:00:00 52
2018-07-28 10:00:00 59
2018-07-28 11:00:00 61
2018-07-28 12:00:00 60
2018-07-28 13:00:00 59.9
2018-07-28 14:00:00 58
2018-07-28 15:00:00 50
2018-07-28 16:00:00 61
2018-07-28 17:00:00 60
2018-07-28 18:00:00 59.9
2018-07-28 19:00:00 54
2018-07-28 20:00:00 52
2018-07-28 21:00:00 50
2018-07-28 22:00:00 49
2018-07-28 23:00:00 45
2018-07-29 00:00:00 44

9

can you paste the query??

10

Here is the query

SELECT
UNIX_TIMESTAMP(time) as time_sec,
Voltage as value,
Voltage as metric
FROM 24hr_Energy
WHERE $__timeFilter(time)
ORDER BY time ASC

11

Here is the request thats gets generated. I see from & to values which goes from grafana are far too bigger than MySQL UNIX_TIMESTAMP values.

MySQL unix time stamp value = 1532719800
Grafana value (as part of request) = 1535394000000

Is this cause of issue ?
I formulated below query in MySQL based on Grafana request & its returning zero records.

SELECT UNIX_TIMESTAMP(time) as time_sec, Voltage as value, Voltage as metric FROM 24hr_Energy
WHERE time>=convert (1535394000000,datetime) and time <=convert(1535480400000,datetime) ORDER BY time ASC;

Grafana request–>
xhrStatus:“complete”
request:Object
method:“POST”
url:“api/tsdb/query”
data:Object
from:"1535394000000"
to:"1535480400000"
queries:Array[1]
0:Object
refId:“A”
intervalMs:120000
maxDataPoints:683
datasourceId:2
rawSql:"SELECT UNIX_TIMESTAMP(time) as time_sec, HVAC as value, HVAC as metric FROM 24hr_Energy WHERE $__timeFilter(time) ORDER BY time ASC "
format:“time_series”

12

Is there any way to plot non time series graph in grafana?

13

This shows a bar chart with the number of open work orders grouped by priority (Urgent, High, Medium, Low)

=======

So I was able to figure this out with the following sql statement:
SELECT UNIX_TIMESTAMP() as time_sec, COUNT(*) as value, P.PRIORITY_TYPE_NAMEas metric FROM JOB_TICKET J, PRIORITY_TYPE P WHERE J.DELETED !=1 AND J.STATUS_TYPE_ID IN (1,2) AND J.PRIORITY_TYPE_ID= P.PRIORITY_TYPE_ID group by J.PRIORITY_TYPE_ID

Time Series

Axes
Left Y show
Unit short
Scale linear
Label Number

Right Y show
Unit short
Scale linear

Display
Draw Modes Bars

Stacking & Null value
Null value connected

14

I solved by making a subquery with max time, but no using in the graphs and worked very well

15

like this:

select
(select max(time_msg) from message_tx) as time,
SUM(case when provider = ‘prov1’ then 1 else 0 end) as data1,
SUM(case when provider = ‘prov2’ then 1 else 0 end) as data2,
SUM(case when provider = ‘prov3’ then 1 else 0 end) as data3,
SUM(case when provider = ‘prov4’ then 1 else 0 end) as data4,
from
message_tx
where
date_part(‘year’,time_msg) = ‘[[year]]’ and date_part(‘month’,time_msg) = ‘[[month]]’

I mean, I give time variable to the graph, but in X-axis i select series and voilá