# MySQL $\_\_timeFilter not filtering

**URL:** <https://community.grafana.com/t/mysql-timefilter-not-filtering/14654>\
**Category:** MySQL\
**Tags:** mysql\
**Created:** [February 21, 2019, 4:36am UTC](https://community.grafana.com/t/mysql-timefilter-not-filtering/14654 "2019-02-21T04:36:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![hisfullarmor](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/hisfullarmor/32/6137_2.png) [@hisfullarmor](https://community.grafana.com/u/hisfullarmor)\
**Post date:** [February 21, 2019, 4:36am UTC](https://community.grafana.com/t/mysql-timefilter-not-filtering/14654/1 "2019-02-21T04:36:07Z")

</div>

Hi there, I’m new to Grafana, and I’m having issues with my queries not honoring the $\_\_timeFilter macro. Hopefully I’m just misunderstanding something, but it seems to not be working for me. I have a very simple database table:

```
mysql> DESC impacts;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(255) | NO | | NULL | |
| mass | int(11) | NO | | NULL | |
| recclass | varchar(255) | YES | | NULL | |
| reclat | decimal(8,6) | YES | | NULL | |
| reclong | decimal(9,6) | YES | | NULL | |
| year | datetime | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+

```

The year field is obviously the dates I’m attempting to filter based on, and the field I’m mapping is the mass field. I have two panels on my dashboard, one graph and one single stat. The single stat is an average of the data from the mass field while the graph charts them across time. When I have no filtering going on both panels show data as I would expect, but as soon as I attempt to add a $\_\_timeFilter macro the single stat becomes N/A and the graph ceases to plot any points along the X axis. Here’s my beginning query (which works) from the single stat panel:

```
SELECT
  year AS "time",
  mass
FROM impacts
ORDER BY year

```

It’s pretty straight forward, and shows 50.9 kg which matches the 50864.7143 value (grams) I get when I select avg(mass) directly in MySQL. As soon as I change my query to the following I get N/A in the single stat panel:

```
SELECT
  year AS "time",
  mass
FROM impacts
WHERE $__timeFilter(year)
ORDER BY year

```

I have also tried:

> WHERE year \>= $\_\_timeFrom() AND year \<= $\_\_timeTo()

or

> WHERE year BETWEEN $\_\_timeFrom() AND $\_\_timeTo()

Am I misunderstanding something here? The query doesn’t throw any errors, but it’s not getting data. This appears to line up with examples I’ve seen so I’m scratching my head trying to figure out why that would not work.

Thanks a lot for any help that can be offered. It is very much appreciated!

Ryan

---

<div class="post-metadata">

**Author:** ![torkel](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/torkel/32/20_2.png) [@torkel](https://community.grafana.com/u/torkel)\
**Post date:** [February 21, 2019, 7:29am UTC](https://community.grafana.com/t/mysql-timefilter-not-filtering/14654/2 "2019-02-21T07:29:43Z")

</div>

When you try the generated sql in MySQL directly and error ? Or so you see anything wrong?

---

<div class="post-metadata">

**Author:** ![hisfullarmor](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/hisfullarmor/32/6137_2.png) [@hisfullarmor](https://community.grafana.com/u/hisfullarmor)\
**Post date:** [February 21, 2019, 12:23pm UTC](https://community.grafana.com/t/mysql-timefilter-not-filtering/14654/3 "2019-02-21T12:23:38Z")

</div>

When I use the generated sql directly in mysql it returns no results which makes no sense. Here’s an example:

`SELECT year AS "time", mass FROM impacts WHERE year BETWEEN FROM_UNIXTIME(-2813388552) AND FROM_UNIXTIME(96021660) ORDER BY year;`

When I convert these unix timestamps I get ‘1880-11-05 15:10:48’ and ‘1973-01-16 08:41:00’. If I then translate that to this query I get 497 results:

`SELECT year AS "time", mass FROM impacts WHERE year BETWEEN '1880-11-05 15:10:48' AND '1973-01-16 08:41:00' ORDER BY year;`

Could it be that for some reason my MySQL installation does not support FROM\_UNIXTIME? It seems like that should be a pretty standard thing…

---

<div class="post-metadata">

**Author:** ![hisfullarmor](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/hisfullarmor/32/6137_2.png) [@hisfullarmor](https://community.grafana.com/u/hisfullarmor)\
**Post date:** [February 21, 2019, 5:04pm UTC](https://community.grafana.com/t/mysql-timefilter-not-filtering/14654/4 "2019-02-21T17:04:29Z")

</div>

Well, I found an answer, but no solution as far as I can see. The problem lies in the fact that the FROM\_UNIXTIME function does not support dates before the UNIX epoch, Jan 1, 1970. When I filter the date range in Grafana to 1/1/70 - 12/31/70 it works as expected. If I then click back a page to 1/1/1969 - 12/31/70, bam, no data (because FROM\_UNIXTIME for a date before the UNIX epoch evaluates to null).