Help with influxdb query like history_stats sensor

Configuration
1

Hi guys,

I have been playing around with history_stats in the config yaml.
Got some nice cards set up but I know that history_stats is pulling from the local sql db so only had 6/7 days of data.

I have my data now feeding into influxdb also with retention policy much longer.
How can I write a query equivalent to the one below but to pull the same data from influxdb? See blue arrows from my screen shot.

Thanks a lot

historystats
historystats1587×899 89.6 KB

  - platform: history_stats
    name: Downstairs Heating ON 30 days
    entity_id: switch.sonoff_1000907bc4
    state: 'on'
    type: time
    end: '{{ now().replace(hour=0, minute=0, second=0) }}'
    duration:
      days: 30
2

Maybe the way to go is to use an inlfuxdb script and make it continuous.

Getting familiar with the syntax

from(bucket: "home_assistant/autogen")
  |> range(start: -30d)
  |> filter(fn: (r) => r._measurement == "state" and r._value == "on" and (r._field == "state"))
  |> stateDuration(fn: (r) => r._value == "on", column: "ON", unit: 1s)
3

ok this is the data I want to show but I created this in flux and created a dashboard showing the single figure.

How do I get this into grafana or show directly on my lovelace card?

from(bucket: "home_assistant/autogen")
  |> range(start: -30d)
  |> filter(fn: (r) => r._measurement == "state"  and r._value == "on")
  |> filter(fn: (r) => r.entity_id == "sonoff_1000729132")
  |> stateDuration(
   fn: (r) => r._value == "on" ,
   column: "PoolPump_OnTime",
   unit: 1m
 )
|> top(n:1, columns: ["PoolPump_OnTime"])
|> keep(columns: ["PoolPump_OnTime","_time"])