"Data does not have a time field"; what do I have to do to get Grafana recognize 'time' attribute?

Grafana Time Series Panel
1

Hi, I got my Grafana connected to my MySQL DB and visualize one table using a panel.

This is the sample data I want to see as a graph on hourly basis:

SELECT server_name, stat_dtm, cpu_max_usage, memory_max_usage FROM mytable where stat_type = 60;

캡처

So I wrote the following query in Grafana panel page as below. I will set server_name value as the names of the servers I want to show.

select *
from (
	select unix_timestamp(str_to_date(stat_dtm,'%Y%m%d%H%i%s')) 'time', server_name, CPU_MAX_USAGE, MEMORY_MAX_USAGE
	from mytable
  where stat_type = 60 and server_name = 'servername'
) as t
where $__timeFilter(time)
ORDER BY time

But what I see where the graph has to show up is ‘Data does not have a time field’, even though I set the name of timestamp as time. What am I missing to properly show the data? Thanks in advance.

2

have you tried changing this option
image
from time series to table

btw why do you need nested query for that?
cant you use this?
and even if I need to use nested query on that
I would love to avoid SELECT * and use column names instead

SELECT STR_TO_DATE(stat_dtm,'%Y-%m-%d %H:%i:%s') AS 'time', 
  server_name, 
  CPU_MAX_USAGE, 
  MEMORY_MAX_USAGE
FROM mytable
WHERE $__timeFilter(STR_TO_DATE(stat_dtm,'%Y-%m-%d %H:%i:%s'))
  AND stat_type = 60 
  AND server_name = 'servername'
2 Likes
3

The reason why I nested the query was that the panel said there was no time field even though I set the column name of timestamp as ‘time’; so I tried to make the table itself in FROM clause have ‘time’ column .

4

hmm hmm…
let me know if my query above doesnt work

1 Like
5

The query that Grafana generated using yours is as below; checked the log and it said "Error 1582: Incorrect parameter count in the call to native function 'STR_TO_DATE'", the problem is that Grafana did not parse $__timeFilter() correctly. This is where I had nested my weird query, to pass ‘time’ as the parameter of $__timeFilter() not STR_TO_DATE(). Think I have to work on this more…

SELECT STR_TO_DATE(stat_dtm,'%Y-%m-%d %H:%i:%s') AS 'time', 
  server_name, 
  CPU_MAX_USAGE, 
  MEMORY_MAX_USAGE
FROM mytable
WHERE STR_TO_DATE(stat_dtm BETWEEN FROM_UNIXTIME(1624303700) AND FROM_UNIXTIME(1624325300))
  AND stat_type = 60 
  AND server_name = 'servername'

Thank you though, I really appreciate your comment.

6

try casting it as DATETIME

WHERE $__timeFilter(CAST(STR_TO_DATE(stat_dtm,'%Y-%m-%d %H:%i:%s') AS DATETIME))

or you can use this

WHERE STR_TO_DATE(stat_dtm,'%Y-%m-%d %H:%i:%s') BETWEEN FROM_UNIXTIME($__unixEpochFrom()) AND FROM_UNIXTIME($__unixEpochTo())

$__unixEpochFrom() and $__unixEpochTo() are functions to get date selection

the str to date format will depend on your string,
maybe yours fit more than mine: ‘%Y%m%d%H%i%s’

2 Likes
7

Buddy I appreciate it so much! I tested the second one you proposed, and I tried $__timeFrom() and $__timeTo() instead. I did not have to use $__timeFilter() which cannot convert to the actual query correctly.

WHERE CAST(STR_TO_DATE(stat_dtm,‘%Y-%m-%d %H:%i:%s’) AS DATETIME) between $__timeFrom() AND $__timeTo()

So all I have to do now is just to set the time range and it is not a hard job at all. Your comment help me solve this problem. Hope you to enjoy your day.

For those who read this:
If you put some functions in $__timeFilter() like $__timeFilter(fn(param)), Grafana might convert it to fn(param BETWEEN FROM_UNIXTIME(<from>) AND FROM_UNIXTIME(<to>)), not fn(param) BETWEEN FROM_UNIXTIME(<from>) AND FROM_UNIXTIME(<to>). So I recommend to use $__timeFrom() and $__timeTo() instead.

1 Like
8

if you look at the generated SQL
what is the result of $__timeFrom() AND $__timeTo() ?

*I didnt know that function haha - thanks for this new tips
I always use $__unixEpochFrom()
and then convert it to normal date haha

9

This is the WHERE clause I entered,

(STR_TO_DATE(stat_dtm,‘%Y%m%d%H%i%s’) between $__timeFrom() AND $__timeTo())

and this the converted one I get in the query inspector.

(STR_TO_DATE(stat_dtm,‘%Y%m%d%H%i%s’) between FROM_UNIXTIME(1619794800) AND FROM_UNIXTIME(1622473199))

Each function retrieves starting and finishing timestamps in the selected time range. The unix times above are 2021-05-01 00:00:00 and 2021-05-31 23:59:59, respectively.

1 Like
10

ah… I see…
thank you