Time range query

Grafana Configuration
1

Hello,
I installed grafana, but my sql skills are way below the level of the presentation in the documentation regarding my “issue”. Please excuse me if there happens to be a dedicated feature/chapter which exactly would do this. I simply can’t understand/find it there.
I connected my sqlite db file through the sqlite datasource and doing a simple single query of my table like;
SELECT DateTimeStamp, Temperature FROM sensordata
It’s a timeseries table and the DateTimeStamp column is set as a time formatted column.

image

The table is starting to be big and the query time is taking longer and longer, which is normal because i am doing a complete query of the table.
image

My question is if there is a way to connect or corelate the relative or absolute timerange
dropdown list elements to query only the selected time range and don’t do a full query then jump to that time range.

image

I searched for keywords like dynamic, time, range, query on google and here but nothing that i would understand came up.
Thank you kindly and have a nice evening if applies.

2

Hi @szbiro ,

The kind of functionality you’re talking about – creating dynamic queries based on a time range – is totally possible in other, more robust SQL databases like MySQL and Postgres. The typical method uses macros, I believe. Here are the macros for the MySQL datasource plugin.

For instance, here is a query I have using that plugin:

SELECT
  UNIX_TIMESTAMP(TIMEST) as time_sec,
  VARIABLE_VALUE+0 as value,
  VARIABLE_NAME as metric
FROM my2.status
WHERE $__timeFilter(TIMEST)
AND variable_name in ('THREADS_CONNECTED', 'THREADS_RUNNING')
ORDER BY TIMEST ASC

note the line WHERE $__timeFilter(TIMEST).

Unfortunately, according to the github repo for the SQLite datasource, these macros are not supported.

The author does list two supported macros:

$__unixEpochGroupSeconds(unixEpochColumnName, intervalInSeconds)
and
$__unixEpochGroupSeconds(unixEpochColumnName, intervalInSeconds, NULL)

So, this might be totally possible with SQLite, but you will find more more functionality and supporting documentation if you switch to a DB like MySQL or Postgres. Here’s a nice article detailing the differences between the three major relational databases.

I hope this helps a bit!

3

my god, i envy people like you (decoding “kitchen language” and solving the problem after reading the documentation, not to mention you dont even use sqlite i assume).
thank you btw i already changed from sqlite to influxdb for this exact reason. Since influx natively does dynamic query after i link my db everything works like a charm.

1 Like
4

Wise decision, switching to influx. I think you’ll find much more support and functionality using that db!

There are some influx wizards (not me, unfortunately!) lurking in this forum, so as you continue to explore that product, don’t hesitate to keep posting questions :+1:

1 Like