MySQL $__timeFilter not filtering

Grafana MySQL
1

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

2

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

3

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…

4

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).