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
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:
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)
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.
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
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:
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
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)
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?
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:
SELECT
Reported as time_sec,
Count as value,
Node as metric
FROM AlarmHistory
WHERE $__unixEpochFilter(Reported)
ORDER BY Reported ASC
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.
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.
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
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.
You will have to explain a bit more.
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
Is this for a graph panel or a singlestat or a table panel? Sounds like a table panel as you are talking about columns?
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):
SELECT
min(time_sec) as time,
count(status) as value,
status as metric
FROM my_table
WHERE $__timeFilter(time_sec)
group by status
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 
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!
How is your query like?