0

I have created a variable named $mypair in Grafana, I have added some options manually and I can view those in dashboard. I select an option (example: AUDCAD).

In query part of Graph settings I am entering the below query:

SELECT UNIX_TIMESTAMP(time) AS time, LEFT([[mypair]], 3) FROM allpairs

What I am trying to accomplish from the above query is actually below query

SELECT UNIX_TIMESTAMP(time) AS time, AUD FROM allpairs

I am trying to get first 3 letters from the $mypair variable so I can construct the graph. But I am not successfull. When I set the first query on the query box of graph settings, it gives a table in following format

2020-09-04 02:00:00    AUD
2020-09-04 03:00:00    AUD
2020-09-04 04:00:00    AUD
2020-09-04 05:00:00    AUD
2020-09-04 06:00:00    AUD
2020-09-04 07:00:00    AUD

But what I need is use first 3 letters of $mypair variable selecting the column so the output is as follows

2020-09-04 02:00:00    63
2020-09-04 03:00:00    63
2020-09-04 04:00:00    62
2020-09-04 05:00:00    62
2020-09-04 06:00:00    62
2020-09-04 07:00:00    60

I would appriciate feedback.

Mr.Eboy
  • 45
  • 9
  • The query output you see is the correct behaviour of the MySQL query. When we say `SELECT LEFT('AUDCAD', 3) FROM allpairs` MySQL will select the first three characters of `AUDCAD` string, ie `AUD`. It will not use use `AUD` as a column name. To achieve what you want to do use MySQL prepared statements. – robert Oct 09 '20 at 16:02
  • @robert thank you for your example can you please give some pointers or example to mysql prepared statements please – Mr.Eboy Oct 09 '20 at 20:43
  • You can check this link https://dev.mysql.com/doc/refman/5.7/en/sql-prepared-statements.html. – robert Oct 10 '20 at 02:28

1 Answers1

0

Actually I have used Views in mysql and accomplished what I want.

In my case I have created a view as follows

CREATE VIEW `AUDCAD` AS SELECT UNIX_TIMESTAMP(time) AS "time", `AUD`, `CHF` AS FROM `allpairs` ORDER by time ASC;

Then used the below query in Grafana

SELECT * FROM $mypair
Mr.Eboy
  • 45
  • 9