I have a database where I have data that is posted every second for a length of time. An insert looks like this:
INSERT INTO running
(time, activityType, lapNumber, distance, speed, calories, lat, long, elevation, heartRate, cycles)
VALUES (‘2020-07-01 22:47:12’, 1, 1, 15.9, 9.68, 5,
50.85432, 5.475312, 103, 119, 60)
When I set the datepicker to ± a day before and a day after my data, the following query is generated:
The following query is generated:
SELECT time as time_sec, (speed * 3.6) as value, “Speed” as metric
FROM running s
WHERE time > FROM_UNIXTIME(1594087251)
AND DATE_FORMAT(time, ‘%Y-%m-%d’) IN(DATE_FORMAT(‘2020-07-07 23:38:03’, ‘%Y-%m-%d’))
ORDER BY s.time ASC
However, when I zoom in any further the query inspector returns this:
rowCount:0
series:null
tables:null
dataframes:null
And I get “No data” in my graph
@habib1 I’m not really sure what led you to the conclusion that it’s a bug. Is there a GitHub issue?
@jorandirkzwager I feel like something funny is happening with the time boundaries of your query, which makes it not return any data when the Grafana time boundaries are constrained. Could you share a screenshot of how it’s set up in Grafana? The time constraints in the (working) query you pasted don’t really look like what I’d expect to see (though I’m also not super-familiar with MySQL)
Can you clarify what the $date environment variable is meant to do, and say a couple of words about the intention of the condition: ?
AND DATE_FORMAT(time, '%Y-%m-%d') IN(DATE_FORMAT($date, '%Y-%m-%d'))
As a basis, if you simply have
WHERE $__timeFilter(time)
that should be sufficient to have Grafana query and display data from the correct time range. The condition
WHERE time > $__timeFrom()
which you currently have is also pretty good, as it should correctly set the lower end of the time range - though it won’t set an upper bound on the range, so may unintentionally query additional data that you’re not displaying.
But I strongly suspect that the additional date comparison you have introduced is leading to unintentional consequences. It’s hard to say more without understanding what its intended function is. As a test, you could perhaps also take out the DATE_FORMAT condition and see what you get then.
@svetb That condition you mentioned is a result of me experimenting to get sth working.
Anyway, time is saved as datetime in mysql so nothing fancy going on over there. speed is saved as a double.
I’ve tried using this simple query:
`SELECT time as time_sec, (speed * 3.6) as value, "Speed" as metric
FROM running s
WHERE $__timeFilter(time)
ORDER BY s.time ASC`
And it gives me the same result. When I zoom in any further, it shows me no data.
Ok got you. Thanks for simplifying it. That’s strange.
Just as a test, can you try removing the WHERE clause altogether? So that no filtering is being done.
The type of visualization shouldn’t matter by the way.
I suspect it’s something to do with timezones (there are a few other threads on this forum on this - specifically in the context of MySQL), but hard for me to say.
I’ve tried removing the WHERE altogether and now it seems to be working.
I will leave it like that, it doesn’t really make a difference to me so I think this thread can be closed now. Thanks for your help.
