# Using sql timeFilter

**URL:** <https://community.grafana.com/t/using-sql-timefilter/1686>\
**Category:** MySQL\
**Tags:** time-selection, mysql, query-help\
**Created:** [July 6, 2017, 11:10am UTC](https://community.grafana.com/t/using-sql-timefilter/1686 "2017-07-06T11:10:37Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![pukseli](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@pukseli](https://community.grafana.com/u/pukseli)\
**Post date:** [July 6, 2017, 11:10am UTC](https://community.grafana.com/t/using-sql-timefilter/1686/1 "2017-07-06T11:10:37Z")

</div>

Hi! i’m using grafana with SQL server. My problem is that data in my database is in unix time and according help (“- $\_\_timeFilter(column) → UNIX\_TIMESTAMP(time\_date\_time) \> from AND UNIX\_TIMESTAMP(time\_date\_time) \< 1492750877”) data should be in date time.

Another question is that how do i alias data same way as with influxdb

Thanks,  
Akseli

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [July 6, 2017, 12:17pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/2 "2017-07-06T12:17:45Z")

</div>

The MySQL data source is still in alpha. This is a case we don’t seem to handle.

In the meantime, you will have to use a subquery and the `from_unixtime` function:

```auto
SELECT
  $__time(time_sec),
  value,
  metric
FROM (
  SELECT 
    from_unixtime(your_unix_time) AS time_sec,
    1 AS value,
    'test' AS metric
  FROM your_table
  ) your_table
where $__timeFilter(time_sec)

```

---

<div class="post-metadata">

**Author:** ![pukseli](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@pukseli](https://community.grafana.com/u/pukseli)\
**Post date:** [July 7, 2017, 7:22am UTC](https://community.grafana.com/t/using-sql-timefilter/1686/3 "2017-07-07T07:22:59Z")

</div>

HI!  
Thanks a lot! Graph works well.

Also does Grafana support any kind of non time based graphs? I’d like to made a graph about passed and failed tests where X-axis is test case and Y-axis is passed and failed test cases.

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [July 7, 2017, 10:49am UTC](https://community.grafana.com/t/using-sql-timefilter/1686/4 "2017-07-07T10:49:38Z")

</div>

Yes, see X-axis mode field on the Axes tab of the Graph panel. It has a series option that groups by series and not time.

Noticed it was missing from the docs and added it: [http://docs.grafana.org/features/panels/graph/#x-axis-mode](http://docs.grafana.org/features/panels/graph/#x-axis-mode)

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [August 8, 2017, 12:45pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/5 "2017-08-08T12:45:50Z")

</div>

I just merged to master a Pull Request with new macros which should be included in the next release of Grafana. With these macros the above query will be much simpler:

```auto
SELECT
  your_unix_time as time_sec,
  avalue as value,
  aname as metric
FROM your_table
WHERE $__unixEpochFilter(your_unix_time)
ORDER BY your_unix_time ASC

```

---

<div class="post-metadata">

**Author:** ![cparsons1221](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@cparsons1221](https://community.grafana.com/u/cparsons1221)\
**Post date:** [October 12, 2017, 10:05pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/6 "2017-10-12T22:05:26Z")

</div>

Hello,

Is the query posted above on July 6th accurate? I’m attempting to alter it to fit my needs because my database is also using Epoch time natively but it’s throwing an error for me stating “Cannot read property of length ‘undefined’.” I may be missing something obvious, just started using Grafana as an admin about 45 mins ago!

Here is the query:

SELECT  
$\_\_time(time\_sec),  
value,  
metric  
FROM (  
SELECT  
from\_unixtime(Reported) AS time\_sec,  
Count AS value,  
Node AS metric  
FROM AlarmHistory  
) AlarmHistory  
where $\_\_timeFilter(time\_sec)

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [October 16, 2017, 10:48am UTC](https://community.grafana.com/t/using-sql-timefilter/1686/7 "2017-10-16T10:48:46Z")

</div>

Yes, it should work (a similar example works for me) but that looks like a bug. Is there a stacktrace to go with that error? And what version of Grafana are you using?

> [@cparsons1221](#):
>
> Cannot read property of length ‘undefined’.”

Anyway, you can use a simpler query. We added unixEpoch macros to make this easier - see my last previous answer.

Assuming that `Reported` is of type int(11) and contains an epoch value:

```auto
SELECT
  Reported as time_sec,
  Count as value,
  Node as metric
FROM AlarmHistory
WHERE $__unixEpochFilter(Reported)
ORDER BY Reported ASC

```

---

<div class="post-metadata">

**Author:** ![cparsons1221](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@cparsons1221](https://community.grafana.com/u/cparsons1221)\
**Post date:** [October 16, 2017, 12:39pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/8 "2017-10-16T12:39:29Z")

</div>

We’re currently on V4.3.2 and looking to upgrade to the latest as soon as possible. However, last time we attempted the upgrade, some things broke due to the way our reverse proxy is set up I don’t have all of the details but we’re working on getting to the latest version as soon as we can.

In the meantime, the MySQL data source functionality carries a ton of potential and insight into where my team’s time is being spent.

Where can I find the stack trace? The UI flashes up the error mentioned above but no stack trace.

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [October 16, 2017, 2:00pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/9 "2017-10-16T14:00:27Z")

</div>

You can usually find the error and stack trace in the browser console (Chrome Dev Tools for example) as it looks like a JavaScript error.

---

<div class="post-metadata">

**Author:** ![newuan](https://avatars.discourse-cdn.com/v4/letter/n/48db29/32.png) [@newuan](https://community.grafana.com/u/newuan)\
**Post date:** [October 17, 2017, 11:59am UTC](https://community.grafana.com/t/using-sql-timefilter/1686/10 "2017-10-17T11:59:53Z")

</div>

Helo!

I have a similar question on this topic. I using postgresql with grafana 4.6.

when I run the query:  
SELECT  
$\_\_time(time\_sec),  
status  
FROM(  
SELECT  
to\_timestamp(start\_time, ‘YYYY.MM.DD HH24:MI:SS’) AS time\_sec,  
status  
FROM my\_table  
WHERE status IN (‘SUCCESS’)  
) main  
WHERE $\_\_timeFilter(time\_sec)

I get an error: value column must have numeric datatype, column: status type: string valuse: SUCCESS

if I change the request to your:  
SELECT  
$\_\_time(time\_sec),  
status  
FROM(  
SELECT  
to\_timestamp(start\_time, ‘YYYY.MM.DD HH24:MI:SS’) AS time\_sec,  
1 AS status  
FROM my\_table  
WHERE status IN (‘SUCCESS’)  
) main  
WHERE $\_\_timeFilter(time\_sec)

Then the graph does not look like it should. How to get the sum of rows with their values

Thanks

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [October 17, 2017, 12:31pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/11 "2017-10-17T12:31:41Z")

</div>

Your first query looks a bit strange to me. Is the status column returning ‘SUCCESS’? How would you sum that? The error is very logical. Timeseries is a list of time/value pairs. A timestamp and a numeric value. Having a string for the value makes no sense.

> [@newuan](#):
>
> Then the graph does not look like it should. How to get the sum of rows with their values

You will have to explain a bit more.

---

<div class="post-metadata">

**Author:** ![newuan](https://avatars.discourse-cdn.com/v4/letter/n/48db29/32.png) [@newuan](https://community.grafana.com/u/newuan)\
**Post date:** [October 17, 2017, 1:05pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/12 "2017-10-17T13:05:26Z")

</div>

Thanks for the quick response.  
I have a table with values  
Time | status  
17.10.2012 | successfully  
10/16/2017 | failed

I would like to get the parity on the chart, the combined status

For example, a column colored in green (success) and red (failure)

All I could do is do 3 different requests, but this results in 3 different columns

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [October 17, 2017, 1:15pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/13 "2017-10-17T13:15:24Z")

</div>

Is this for a graph panel or a singlestat or a table panel? Sounds like a table panel as you are talking about columns?

---

<div class="post-metadata">

**Author:** ![newuan](https://avatars.discourse-cdn.com/v4/letter/n/48db29/32.png) [@newuan](https://community.grafana.com/u/newuan)\
**Post date:** [October 17, 2017, 1:32pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/14 "2017-10-17T13:32:51Z")

</div>

Is this for a graph panel.

 ![grafana](https://us1.discourse-cdn.com/grafana/original/2X/1/194d85ae079247c3cc43bf127f87503b73b4086c.png)

---

<div class="post-metadata">

**Author:** ![daniellee](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/daniellee/32/41_2.png) [@daniellee](https://community.grafana.com/u/daniellee)\
**Post date:** [October 17, 2017, 3:29pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/15 "2017-10-17T15:29:25Z")

</div>

Aha - a graph panel in series mode. You will need to group by status (the string value). So you need to return 3 fields from your query: the time field, a value field (status sum) and a metric field (status name).

Something like this (pseudocode):

```auto
SELECT
  min(time_sec) as time,
  count(status) as value,
  status as metric
FROM my_table
WHERE $__timeFilter(time_sec)
group by status

```

---

<div class="post-metadata">

**Author:** ![thingwizard](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/thingwizard/32/4840_2.png) [@thingwizard](https://community.grafana.com/u/thingwizard)\
**Post date:** [September 29, 2018, 1:36pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/16 "2018-09-29T13:36:31Z")

</div>

Hi!

I have been struggling getting the time filtering to work now for a couple of days so I have to ask.  
I’m plotting a timeseries graph for my heat pump from MySQL with the query:

```
SELECT atimestamp as time, 
berfram as value, 
'Beräknad Framledning' as metric 
FROM nibejson 
order by time asc

```

This works great but I’m afraid I’m not timefiltering so I’m going to get problems as my database grows.

atimestamp is unix time, so i have tried changing it to  
… `FROM nibejson WHERE $__unixEpochFilter(atimestamp) order by time asc`

But if I do that i get no data at all.  
Does the unix timestamp need to be an int?

What am I missing here? I’m quite new to both Grafana and MySQL, so explain as to a 5 year old 🙂

---

<div class="post-metadata">

**Author:** ![thingwizard](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/thingwizard/32/4840_2.png) [@thingwizard](https://community.grafana.com/u/thingwizard)\
**Post date:** [September 30, 2018, 3:57pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/18 "2018-09-30T15:57:23Z")

</div>

Ah, I found out my database timestamp is 13 numbers long and unixEpochFilter is 10 numbers long, so it doesn’t match. Can I fix that in the query code without having to change my database setup and losing all previous data?

EDIT: WHERE $\_\_unixEpochFilter(atimestamp/1000) seem to do the trick!

---

<div class="post-metadata">

**Author:** ![johnzballad](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/johnzballad/32/7216_2.png) [@johnzballad](https://community.grafana.com/u/johnzballad)\
**Post date:** [August 30, 2019, 7:00pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/19 "2019-08-30T19:00:07Z")

</div>

> [@daniellee](#):
>
> SELECT your\_unix\_time as time\_sec, avalue as value, aname as metric FROM your\_table WHERE $\_\_unixEpochFilter(your\_unix\_time) ORDER BY your\_unix\_time ASC

How is your query like?

---

<div class="post-metadata">

**Author:** ![fibarojmd](https://avatars.discourse-cdn.com/v4/letter/f/6bbea6/32.png) [@fibarojmd](https://community.grafana.com/u/fibarojmd)\
**Post date:** [June 11, 2020, 2:22pm UTC](https://community.grafana.com/t/using-sql-timefilter/1686/20 "2020-06-11T14:22:58Z")

</div>

hello

I am looking for help

> [@GRFANA at DOLIBARR](https://community.grafana.com/t/grfana-at-dolibarr/31872):
>
> bonjour Je souhaite connecter mon logiciel commercial DOLIBARR à GRFANA. [gr2] is ok I want to connect my DOLIBARR commercial software to GRFANA. Comment récupéré les données de la table prjg\_propal How to retrieve data from the table prjg\_propa SELECT UNIX\_TIMESTAMP(\<time\_column\>) as time\_sec, as value, as metric FROM prjg\_propal WHERE $\_\_timeFilter(time\_column) ORDER BY \<time\_column\> ASC