Question about construction of my first Continuous Queries

Store
1

Hi everyone,

I am trying to make my first CQ, and I am a little stuck, I have been able to get data from a measurement that has several tags in it but I need it to be specific to the queries listed below and and then ultimately minus each other.

SELECT mean(“current_power_w”) AS “mean_current_power_w” FROM “homeassistant”.“autogen”.“watts” WHERE time > :dashboardTime: AND “entity_id”=‘power_ct1_40_540’ GROUP BY time(:interval:) FILL(none)

and

SELECT mean(“current_power_w”) AS “mean_current_power_w” FROM “homeassistant”.“autogen”.“watts” WHERE time > :dashboardTime: AND “entity_id”=‘power_ct2_40_539’ GROUP BY time(:interval:) FILL(none)

Here is one of my attempts that failed, I have just tried to get the data before adding the complication of math.

CREATE CONTINUOUS QUERY “cq_total_energy” ON “homeassistant”
BEGIN
SELECT last(“current_power_w, entity_id=power_ct1_40_540”)
AS “current_power_w”
INTO “Power”
FROM “homeassistant”.“autogen”.“watts”
GROUP BY time(30s)
END

Like I said I have received data when I , entity_id=power_ct1_40_540" from the SELECT field but their are several measurement in it.

Big thanks in advanced

2

Hi @rweisback,

Continuous Queries don’t exist in the same fashion they did with our 1.x product.

In 2.x, we utilise “Tasks” and Flux.

option task = {
    name: "cq_total_energy",
    every: 30s,
    offset: 0m,
    concurrency: 1,
    retry: 2
}

// Fetch Data
data = from(bucket: "homeassistant/autogen")
  |> range(start: -task.every)
  |> filter(fn: (r) =>
    r._measurement == "watts" AND
    r.entity_id == "power_ct1_40_540"
  )
  |> keep(columns: ["current_power_w"])
  |> rename(columns: {current_power_w: "Power"})

// To Into New Measurement
data
  |> last()
  |> to(
    bucket: "homeassistant/autogen",
    tagColumns: ["Power"]
  )

You can save this to main.flux and then execute:

influx task create --org my-org @/tasks/main.flux

3

Hi @rawkode, mate really sorry I posted this in the wrong area, I am running 1.7.7.

Thank you for the details for version 2.

I have moved it to the Store section now.

4

Hi @rweisback,

Can you provide some sample data and I will run some tests?

5

Hi @rawkode, please find below,

SELECT “current_power_w” FROM “homeassistant”.“autogen”.“watts” WHERE time > :dashboardTime: AND “entity_id”=‘power_ct1_40_540’

time watts.current_power_w
2019-02-09T21:23:50.747Z 260.4
2019-02-09T21:24:20.974Z 271.3
2019-02-09T21:24:50.831Z 283.8
2019-02-09T21:25:21.123Z 258.2
2019-02-09T21:25:51.382Z 260.6
2019-02-09T21:26:21.640Z 262.7
2019-02-09T21:26:51.948Z 264.2
2019-02-09T21:27:23.049Z 262.1
2019-02-09T21:27:52.955Z 269.7
2019-02-09T21:28:23.223Z 259.1

If you need more info let me know.

Regards Rob