I am using the timepicker’s relative time range and trying to filter records using my DateTime column which is of type datetime.
Timepicker Last 3 Hours.
select * from table_name where $__timeFilter(DateTime) order by DateTime DESC;
The generated SQL looks like this
select * from table_name where DateTime BETWEEN FROM_UNIXTIME(1593627606) AND FROM_UNIXTIME(1593638406) order by DateTime DESC;
However this results in an empty set. How do I get this to work ?
I want to use relative time picker to use values such as 3 hours, 6 hours, 12 hours
Please advise. Thanks.
I use $__timeFilter in the same way with the same resulting SQL (FROM_UNIXTIME) and it works fine with MariaDB MySQL.
Have you looked at Grafana Query Inspector results - to see what objects/arrays Grafana is returning? In case it’s returning data but some problem means it’s not displayed.
Have you run tests on your database to ensure everything is as expected?
e.g. logon to database and run select FROM_UNIXTIME(1593627606) to check it’s the expected date/time. Checked the contents of the table for data within the times etc?
What panel type are you using, what version of MySQL?
I have found out the issue. SELECT FROM_UNIXTIME(1593627606) returns the time in the MySQL server time which is in UTC. But my data is in eastern.
Is there a way to convert the UNIX epoch to Eastern and send it using $__timeFilter?
Or should I convert another dummy column by converting the data of DateTime to UTC to use it as a filter ?
I am not 100% sure - do you have multiple datasources and are they all in Eastern?
What is your Grafana Timezone set to? Either in Grafana global Settings or can also be set at the Dashboard Settings. That may be all you need to configure.
MySQL has CONVERT_TZ function (but timezone tables need to be pre-populated for it to work). Also there is the MySQL function UNIX_TIMESTAMP(datetime) which returns a seconds since date format which may help.
I got to the bottom of this. Key points that helped me resolve the issue:
The data in MySQL database was in UTC Format.
Grafana’s timepicker now has a pelothra of timezones to choose from.
When I Grafana queries the database it sends out the $__timeFilter(DateTime) it get’s it’s result in UTC. Since the data also reflects UTC now the query result is right.
Given that the previous comments in this thread are at least a year old, I
think we ought to start this again by asking Andres Felipe Rave Velez “which
version of Grafana are you using, and what is the specific problem you are now
facing?”
I don’t think we can assume that any problems from a year ago are still
directly relevant to current versions of Grafana.
Hi Antony, in 1 year the timefilter variable does’nt change, so the problem stilling; and the problem is exactly the same; The point with my comment is to I solved it from the QUERY.
Anyway, this fix your problem:
select * from table_name where DATE BETWEEN DATETIME(FROM_UNIXTIME(${__from}), “America/California”) AND DATETIME(FROM_UNIXTIME(${__to}), “America/California”)
Basically, you should get the answer and convert it to your timezome.
Hope this helps!
NOTE: Case you need put your timezone variable, create a global variable to get the browser timezone
I am using KQL in Azure Managed Grafana and found that if I use the time filter in my KQL query directly, instead of allowing the filter to be applied automatically, the generated query is wildly incorrect. When I filter by last 7 days, I see the beginning datetime generated as only two days ago. My approach from here is only use the KQL query for visualizations that have a datetime column as an output, not aggregated data. For my aggregates, I’ll use one of the other tools available.
