Daily sum of hours with measurement greater x

InfluxDB 1 InfluxQL
,
1

Hi,

I am working some days from home and want to automate my time tracking. The idea is to count the hours / minutes where my work laptop is powered up. The laptop is connected to a smart socket which records power consumption. These measurements are stored in InfluxDB. Metering is not 100% accurate, so I would like to have a daily sum of the time where consumption was i.e. >5 (Watts).

I manage to query basic consumption like this:
SELECT last(“value”) FROM “Power” WHERE (“location” = ‘laptop’) AND $timeFilter GROUP BY time($__interval) fill(null)

I would like to get results in form of
1st Feb - 6.5hrs
2nd Feb - 8hrs
3rd Feb - 7.8hrs
etc.

(How) Is this possible in Influxql / Grafana (output as a graph or table)?

2

Hi @mimo and welcome to the InfluxData forum.

Here is an example in Grafana using InfluxQL that I created which appears to be similar. If we modify the query to be only Texas with a value > 5000 like this:

SELECT sum("value") FROM "ElectricPowerOperations" WHERE ("type"::tag = 'Demand' AND "region"::tag ='Texas') AND "value" > 5000 AND $timeFilter GROUP BY region, time(1d)

then the daily bar graph looks like this:

image
image1084×300 13.1 KB

and table like this:
image

3

Hi @grant1 thank you for your reply.
From your Grafana chart the query gives the electric consumption per day?

I am looking for the number of minutes (time in hh:mm) during which consumption took place.

I.e. in the top blue chart I have the power draw of my laptop, similar to your graph.
That blue chart shows the query

SELECT last(“value”) FROM “Power” WHERE (“location” = ‘laptop’) AND $timeFilter GROUP BY time($__interval) fill(null)

The unit is Watts.

In the bottom green chart I try to have the sum of hours during which the power draw was >5Watts.
There I use the query

SELECT integral(“filled_power”,1d) from (SELECT last(“value”) as “filled_power” FROM “Power” WHERE (“location” = ‘laptop’) AND (“value” >= 5) AND $timeFilter GROUP BY time(1s) fill(previous)) group by time(1d)

This is on of my attempts but I think it is not rigth. The integral calculates the area under the graph, so the unit would be an area, i.e. Watts x seconds which would be Joules (energy).
I am looking for “hours” as the resulting unit.

I.e. my laptop consumed >5W between 8:00am and 8:00pm, then the result I am looking for is “10hrs” as the time the device was running (my working time, sans breaks).

image
image1343×645 78.1 KB

I am trying to format my queries as code but fail for not finding the right keyword to format as code sorry.

4

Hi @mimo

So I am not sure how to do that (or if it’s even possible) using InfluxQL. I did something similar using Flux here using the elapsed function. Instead of:

|> filter(fn: (r) => r["_value"] == 0)

You would have a filter where the value is > 5000 (or whatever you wish).

Maybe @Anaisdg or @Zoe_Steinkamp or @scott can tell us how (if?) this can be done using InfluxQL.

5

This query certainly would be easier in Flux, but I’m not sure the result you’re looking for is possible with InfluxQL. It depends on what version of InfluxDB you’re using (InfluxQL support is slightly different in InfluxDB OSS v1/v2 and InfluxDB Cloud Serverless. @mimo What version of InfluxDB are you using?