I’m using Grafana v10.4.0 on Debian 12
I have mysql database with 2 tables:
1st table contain timestamps
2nd table contains fields: ‘prefix’, ‘path’, and foreign key timestamp that points to timestamp table.
It means that each timestamp can correspond to multiple measurements:
To get values for the graph I use query:
SELECT ep.prefix, pt.timestamp
FROM tb_prefix ep
INNER JOIN processing_time pt ON ep.processing_time_id = pt.id
WHERE ep.path = 1
ORDER BY pt.timestamp DESC
LIMIT 50;
This gives me 1 graph all timestamps and prefix for path = 1
Is there a way to draw graphs for all path values in the table?
Let’s say path can be in range from 1 to 20, in every timestamp not always all values from 1 to 20 will be present
SELECT ep.prefix as metric, --for labels
pt.timestamp as time , --for x axis
numeric_field as value --for y axis
FROM tb_prefix ep
INNER JOIN processing_time pt
ON ep.processing_time_id = pt.id
WHERE ep.path = 1
ORDER BY pt.timestamp DESC
LIMIT 50;
so ep.prefix is the value that should be plotted.
and is used to show x axis
If I add 3rd value (for example ep.path)
SELECT ep.path as metric, --for labels
pt.timestamp as time , --for x axis
ep.prefix as value --for y axis
FROM tb_prefix ep
INNER JOIN processing_time pt
ON ep.processing_time_id = pt.id
WHERE ep.path = 1
ORDER BY pt.timestamp DESC
LIMIT 50;
I will get 2nd graph plotted that is always 0
You can see it in the bottom of the graph.
Now to plot several graphs I use several requests:
SELECT ep.prefix, pt.timestamp
FROM tb_prefix ep
INNER JOIN processing_time pt ON ep.processing_time_id = pt.id
WHERE ep.path = 1 --<<<<<<<<<<<<<< first graph
ORDER BY pt.timestamp DESC
LIMIT 50;
SELECT ep.prefix, pt.timestamp
FROM tb_prefix ep
INNER JOIN processing_time pt ON ep.processing_time_id = pt.id
WHERE ep.path = 2 --<<<<<<<<<<<<<< second graph
ORDER BY pt.timestamp DESC
LIMIT 50;
again you need to specify proper column names for time series to work
SELECT ep.prefix as value, pt.timestamp as time
--you need a metric string value, like name or something like that
FROM tb_prefix ep
INNER JOIN processing_time pt
ON ep.processing_time_id = pt.id
--no need for where clause. if you had 1500 items, you wont have 1500 queries with where filter
ORDER BY pt.timestamp DESC
LIMIT 50;
I agree that WHERE clause has to be removed, in fact ep.path has about 1K variations of
but if I put ep.path as metric - it is plotted as 0 value line
what metric should I use?
Ok, then if I understand correct -
Prefix is value
Timestamp is a time
path will be the metric
I need for every value of the metric new graph
But when I add metric to the sql request it’s plotted as a straight line with value 0
well some metric will plot as 0 because you might not have any values for that metric at certain times. the wider your time series the more none 0 data you will see
Thank you. I think this is the closest solution to what I was searching for.
I didn’t know about this feature.
The best would be to have these graphs on one plot.
But SQL returns an error in that case.
