Using sql timeFilter

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

2 Likes

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?

Is this for a graph panel.

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 :slight_smile:

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?

hello

I am looking for help

https://localhost:3000/t/grfana-at-dolibarr/31872?u=fibarojmd