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

**URL:** <https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229>\
**Category:** Time Series Panel\
**Created:** [June 21, 2021, 9:32am UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229 "2021-06-21T09:32:29Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![jeonyc217](https://avatars.discourse-cdn.com/v4/letter/j/49beb7/32.png) [@jeonyc217](https://community.grafana.com/u/jeonyc217)\
**Post date:** [June 21, 2021, 9:32am UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/1 "2021-06-21T09:32:29Z")

</div>

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;`

![캡처](https://us1.discourse-cdn.com/grafana/original/3X/8/7/872fb418a0a7b328a411a1ea25701be065fc73ce.png)

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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![bferdinandes](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/bferdinandes/32/16960_2.png) [@bferdinandes](https://community.grafana.com/u/bferdinandes)\
**Post date:** [June 21, 2021, 9:39pm UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/2 "2021-06-21T21:39:27Z")

</div>

have you tried changing this option  
 ![image](https://us1.discourse-cdn.com/grafana/original/3X/6/e/6e3de46c9b503328067bae11f256da559e399b7c.png)  
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

```auto
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'

```

---

<div class="post-metadata">

**Author:** ![jeonyc217](https://avatars.discourse-cdn.com/v4/letter/j/49beb7/32.png) [@jeonyc217](https://community.grafana.com/u/jeonyc217)\
**Post date:** [June 22, 2021, 12:38am UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/3 "2021-06-22T00:38:08Z")

</div>

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 .

---

<div class="post-metadata">

**Author:** ![bferdinandes](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/bferdinandes/32/16960_2.png) [@bferdinandes](https://community.grafana.com/u/bferdinandes)\
**Post date:** [June 22, 2021, 1:19am UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/4 "2021-06-22T01:19:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jeonyc217](https://avatars.discourse-cdn.com/v4/letter/j/49beb7/32.png) [@jeonyc217](https://community.grafana.com/u/jeonyc217)\
**Post date:** [June 22, 2021, 1:40am UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/5 "2021-06-22T01:40:25Z")

</div>

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…

```auto
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.

---

<div class="post-metadata">

**Author:** ![bferdinandes](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/bferdinandes/32/16960_2.png) [@bferdinandes](https://community.grafana.com/u/bferdinandes)\
**Post date:** [June 22, 2021, 2:01am UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/6 "2021-06-22T02:01:36Z")

</div>

try casting it as DATETIME

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

```

or you can use this

```auto
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’

---

<div class="post-metadata">

**Author:** ![jeonyc217](https://avatars.discourse-cdn.com/v4/letter/j/49beb7/32.png) [@jeonyc217](https://community.grafana.com/u/jeonyc217)\
**Post date:** [June 22, 2021, 4:41am UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/7 "2021-06-22T04:41:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bferdinandes](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/bferdinandes/32/16960_2.png) [@bferdinandes](https://community.grafana.com/u/bferdinandes)\
**Post date:** [June 22, 2021, 8:50pm UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/8 "2021-06-22T20:50:04Z")

</div>

> [@jeonyc217](#):
>
> $\_\_timeFrom() AND $\_\_timeTo()

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

---

<div class="post-metadata">

**Author:** ![jeonyc217](https://avatars.discourse-cdn.com/v4/letter/j/49beb7/32.png) [@jeonyc217](https://community.grafana.com/u/jeonyc217)\
**Post date:** [June 23, 2021, 12:25am UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/9 "2021-06-23T00:25:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bferdinandes](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/bferdinandes/32/16960_2.png) [@bferdinandes](https://community.grafana.com/u/bferdinandes)\
**Post date:** [June 23, 2021, 11:23pm UTC](https://community.grafana.com/t/data-does-not-have-a-time-field-what-do-i-have-to-do-to-get-grafana-recognize-time-attribute/49229/10 "2021-06-23T23:23:29Z")

</div>

ah… I see…  
thank you