Grafana Influxdb time query with comparison from field in the DB

Grafana Support InfluxDB
#1

Hi,

I am using influxdb and in the select query I read time and newTs as db field represents epoch in ns as below.
But when I add “where time >= newT” clause it does not work.
This throws “ERR: invalid operation: time and *influxql.VarRef are not compatible”

As I understand time is special field, if I add value of newT like where “time >= 1619195400000000000” then it works.
So how can I use variable in the where clause, I have added newTs as integer variable in the database, even I tried newTs::integer in the select query but it fails.
How to solve this problem?

Reference query output

select time, newTs, * from test order by DESC limit 5;

time newTs

1619186823299972608 1619195400000000000
1619185623382094445 1619195400000000000
1619184423460671274 1619195400000000000
1619183223530383327 1619195400000000000
1619182023608563099 1619195400000000000

#2

Can any expert please guide me on this, it would be of great help?
I searched a lot on Google and Influx Documentation but failed to find this precise information.

1 Like
#3

I’m not sure I qualify as an expect, but I’m fairly sure that what you’re trying to do isn’t possible in Influx. There’s only a limited set of operations you can do on the time metric in Influx, and comparing it to another field is not one of them (as you’ve found).

There may be a workaround, but I suspect your best bet would be trying to run the query in Flux instead.

Either way, since this is purely an Influx (and not a Grafana) question, the Influx forum may be a better place to find a solution.

1 Like
#4

Thanks @svetb for reply. I am running query under grafana itself.
So what are the ways by which I can workaround this problem.

  1. By running flux query → how can I do this? Is this completely different than influx?
  2. Does grafana support flux?
  3. Using influxdb query can we not solve this problem?

Thanks

#5

One more question I have, I can compare time with ${__from} variable from Grafana, then why can not I compare time with my variable extracted from InfluxDB? Any reason for this kind of restriction?

Also one more question I had, can I create new variable say TimeCopy from Time and use this as integer for comparison with my variable? I tried this as well, but TimeCopy and Time field both can not co-exists in influx, afaik.

#6

I understand. I didn’t mean that you should not have posted here to begin with. Just pointing out that since you have a fairly niche question that’s about Influx queries, and really not much to do with Grafana itself, you may be able to get more relevant input in the Influx forum.

To your questions:

  1. By running flux query → how can I do this? Is this completely different than influx? – It’s another way to query an Influx database. If you google “Flux query” you get this Get started with Flux | InfluxDB OSS 2.0 Documentation, which is a good starting point.
  2. Does grafana support flux? – yes. If you google “Grafana Flux” you’ll get some useful results.
  3. Using influxdb query can we not solve this problem? – Unfortunately not, to my knowledge. On the Influx forum there are posts like this one Math operations on field value and time - influxdb - InfluxData Community that seem to confirm this

Because ${__from} gets converted to a constant (e.g. 1619195400000000000) by Grafana when it runs the query, and Influx is happy to filter timestamps against a constant like that. Your fields on the other hand are obviously not constants, and Influx doesn’t support that kind of “row by row” logic. It’s a structural limitation of the way the database is set up, since it’s optimized for other query forms.

You should certainly be able to do that, yes. If you have two integer fields, you can run a query that does comparisons on them.

Not sure what the problem could have been. Happy to help if you can tell me more.

#7

Thanks @svetb for your detailed reply.
It really helped me to update my understanding about influx timestamp handling.

My requirement is that I have two timestamp fields in the database namely time, newTime, entryTime. Out of which time is maintained by influxdb, and I insert newTime and entryTime with integer seconds value of Epoch.
I wanted to compare the time against these fields when I fire a query to limit the data which is overlapping with few over hours of next day.

So I am planning to run the query for 2 days and then filter the data upto newTime variable value as I do not need the whole data of 2nd day. So some data from 1st day and some from 2nd day.

I tried to create new variable from time in influx query but new entry does not show any data :frowning:

select time, time/1000 as “newField” from test;

time newField

1618770600000000000
1618857000000000000
1618943400000000000

So, any other workaround I can try to solve this problem?

Also is it possible to add time shift as variable in the query, if that’s possible I can try it, but not sure if the query field itself can be used for the comparison.

#8

Yes, that won’t work since (as discussed) you can’t do arithmetic on the time index.

When you say

I tried to create new variable from time in influx query

I thought you meant actually create the field. I.e. modify the code that writes into Influx in order to also save the timestamp in a field called newField. Are you able to do that?

Note that SELECT "X" as "Y" doesn’t create a field called “Y” anyway, it just exposes the field called “X” under a different name in your query result.

All that said, reading the description of what you’re trying to do, I wonder if there isn’t a fundamentally simpler way to do what you need to do. To be honest your objective is not that clear to me based on the description. Can you not apply a simple filter based on either time OR newTs, maybe with some offset? I don’t understand the need for filtering based on a “dynamic” comparison like what you’re trying to do.

Yes, that’s possible: Time range controls | Grafana Labs. It might be all you need.

#9

Thanks @svetb for your help and quick response. I found out the workaround. I created variable with date as string and compared with influxdb field of date. This is working as per expectation now.

Thanks for sharing the details, which triggered me to think about this workaround solution.

1 Like