Time difference between two unix timestamp

Hi, I am using Influx 1.7 and grafana
I need to get difference between two unix timestamp in seconds, as per below query,

SELECT $__to - “timestamp” as “DIFF” FROM
(SELECT “ProjectRoom_time_unix” AS
“timestamp” FROM “EMS-Smart-IOT-Device-test”
WHERE “ProjectRoom_St” = 1 AND $timeFilter ORDER BY time DESC LIMIT 1) WHERE $timeFilter ORDER BY time DESC LIMIT 1

Query output is,

OUTPUT is: 10361504

Please help me to rectify the query.
Thanks in advance

Hello @Neelanjan,
I believe this will be useful to you:
https://docs.influxdata.com/influxdb/v1.7/query_language/functions#elapsed

I also suggest upgrading to 1.8 and enabling Flux.
With Flux you. can:
https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/built-in/transformations/elapsed/

Thanks! :slight_smile:

Hi,
I have upgraded to Influx 2.0(required for my system) but with the query getting confused with select statement and where clause,
Can you please help in putting the correct Flux Syntax for the Influx QL query as below:

SELECT “ProjectRoom_time_unix” AS “timestamp” FROM “MYDB”.“autogen”.“EMS-Smart-IOT-Device-test1” WHERE “ProjectRoom_St” = 1 AND time > :dashboardTime: AND time < :upperDashboardTime: AND “Equipment”=‘Wi’

This query is working with Influx 1.8 - InfluxQL

@Neelanjan InfluxDB 2.0 doesn’t support InfluxQL (yet). You need to use Flux. Try this:

from(bucket: "MYDB")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStart)
  |> filter(fn: (r) =>
    r._measuremet == "EMS-Smart-IOT-Device-test1" and
    r.ProjectRoom_St == 1 and
    r.Equipment == "Wi"
  )
  |> elapsed(unit: 1s)

Hi,

It is showing error,

image

Are you running this in InfluxDB 2.0?

No just testing it with influx 1.8 flux
Flux language should be same…I think

This made me think you were running on 2.0. The Flux language is the same, but the names of the prebuilt variables and how you reference them are different between InfluxDB 1.x and 2.0.

from(bucket: "MYDB")
  |> range(start: dashboardTime, stop: upperDashboardTime)
  |> filter(fn: (r) =>
    r._measuremet == "EMS-Smart-IOT-Device-test1" and
    r.ProjectRoom_St == 1 and
    r.Equipment == "Wi"
  )
  |> elapsed(unit: 1s)

Hi,
Still giving some error,

Change r.ProjectRoom_St == 1 to r.ProjectRoom_St == "1"

Hi,
With InfluxQL, its OK.

But with Flux,
Still showing no results

Sorry, there was some changes in there that still assumed 2.0. Also, I included elapsed because I thought that’s what you were looking for, but if you want the queries to be the same, remove it.

from(bucket: "MYDB/autogen")
  |> range(start: dashboardTime, stop: upperDashboardTime)
  |> filter(fn: (r) =>
    r._measurement == "EMS-Smart-IOT-Device-test1" and
    r._field == "ProjectRoom_time_unix" and
    r.ProjectRoom_St == "1" and
    r.Equipment == "Wi"
  )

Still showing “No Results”

Is there any problem with Influx1.8 flux?

No, Flux works with InfluxDB 1.8. Are ProjectRoom_St and Equipment both tags?

No, ProjectRoom_St is the Field Key and Field value is 1, Equipment is the tag

Hi,
Is there any problem with the where clause key(ProjectRoom_St) being a field key?