Time difference between two unix timestamp

Store
1

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,

image
image1694×64 7.42 KB

OUTPUT is: 10361504

Please help me to rectify the query.
Thanks in advance

2

Hello @Neelanjan,
I believe this will be useful to you:

I also suggest upgrading to 1.8 and enabling Flux.
With Flux you. can:

Thanks! :slight_smile:

3

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

4

@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)
5

Hi,

It is showing error,

image
image974×181 7.73 KB

image

6

Are you running this in InfluxDB 2.0?

7

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

8

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)
9

Hi,
Still giving some error,

image
image971×407 16.3 KB

10

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

11

Hi,
With InfluxQL, its OK.

image
image1488×392 33 KB

But with Flux,
Still showing no results

image
image1441×486 34.2 KB

12

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"
  )
13

Still showing “No Results”

Is there any problem with Influx1.8 flux?

14

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

15

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

16

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