How to get how much time elapsed since data point is stored in influxdb

Hi,

I’m trying to find the time difference between current timestamp and the time a point is entered in influxdb
For example, below query gives me an entry from activealerts measurement.

select env, level, alert_name from activealerts order by time desc limit 1
name: activealerts
time env level alert_name


1596542152514023232 uat CRITICAL alerts_cron_jobs

Here I would like to know, how much time have elapsed since this entry was made.

I tried as below

select now() - time, env, level, alert_name from activealerts order by time desc limit 1
ERR: undefined function now()

This seems very simple thing to do, but I dont know how to do it.

Regards,
Robert

Hello @Robert_George,
I don’t think this is something you can do in InfluxQL, however you can do it with Flux in InfluxDB 1.8+.
You can use the the now() function
https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/built-in/misc/now/
as well as the first() function to return the most recent point.
https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/built-in/transformations/selectors/first/
as well as the map() function
https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/built-in/transformations/map/
to write something like this:

from(bucket: "animals")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "cats")
  |> filter(fn: (r) => r["_field"] == "adult")
  |> filter(fn: (r) => r["shelter"] == "A")
  |> filter(fn: (r) => r["type"] == "calico")
  |> group()
  |> first()
  |> map(fn: (r) => ({ time: uint(v: now()) - uint(v: r._time)}))

Please let me know how you get along!

You can also use the findRecord() https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/built-in/transformations/stream-table/findrecord/ function in conjunction with duration() function https://v2.docs.influxdata.com/v2.0/query-data/flux/manipulate-timestamps/#calculate-the-duration-between-two-timestamps

To do someting like:

time1 = uint(v: 2019-09-17T21:12:05Z)
time2 = uint(v: 2019-09-18T22:16:35Z)

duration(v: time2 - time1)