Dear All,
i have this sensor that measure the illumination of the external environment. The value is from 0 to 1000.
I would to extract a time of light in one day. So basicaly I want count a time when the value is >100.
How can I get this query??
I’m not sure I understand the question exactly. If you want to change your query to include only values greater than 100, you could do something like this
SELECT 'my_field' FROM 'mydb' WHERE 'myfield' > 100
If you want to group by day, you need to make sure you have a tag you can group by.
Can you just amend @katy’s query to include a sum?
select sum('my_field') from 'mydb' where 'my_field' > 100
In this way I have a sum of all value > 100… I want the time in minutes.
The value is >100 from 6:00 to 20:00 this mean 14 hours or 840 minutes.
So it looks then that you want something as follows (though to be honest I’ve never used this before):
select sum(above) / 1000000000 from (select elapsed('my_field') as above from 'mydb' where time > now() - 1h and 'my_field' > 100)
I divided the value here by 1,000,000,000 since all elapsed times are displayed in nanoseconds. This should yield the total number of seconds you’ve spent in your window above your value of 100. You may need to divide again by 60 to get mins, but I think this should get you where you need.
