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

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.

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

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 .

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

1 Like

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.

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

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

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

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

ah… I see…
thank you