Select temp over 25 but daily only one

InfluxDB 1 InfluxQL
1

Hello
wiht this code i got a result
select from temperatur WHERE temp >= 25

but, sometimes i have two temperatur entry in one day.
i would like to sort/group them to just check on which day the temperature was over 25 Degree.
At the end doing a into a new measurment TX25GE

have a nice day
vinc

2

Hello @vinc,
What version of influxdb are you using?
What do you mean by sort/group them to check on which day? The data has a timestamp, no? So don’t you know which day? Or do you mean day of the week? Can you include some more detail around what you’re trying t achieve please? Thank you

3

Hello @Anaisdg
Influx 1.8 i use
i temp over 25 is a summerday, i would like to count at the end how many day over 25 i had. When did the fist summerday came when the last.
the same will be with a hotday over 30 degree.

if i measure in one day a couple of time over 25 degree it still should be at the end - one day and not multiple days!

This site did help so far

have a nice day
vinc

1 Like
4 
SELECT COUNT("temp") FROM "temperatur" WHERE "temp" >=25 GROUP BY time(24h)

name: temperatur
time count

1620518400000000000 4
1620604800000000000 0
1620691200000000000 0
1620777600000000000 0
1620864000000000000 0

how the query should be to really show only the count over 1?

how the query should be to count every day this happen grouped by year?
i would expect something like:
2020 | 23 Days over >25 degree
2021 | 15 Days over >25 degree

how the query should be to show the first hit by year?
i would expect something like:
2020 | 22.06.2020
2021 | 15.05.2021

or mixing both (but could be done with Node Red too)
2020 | first 22.06.2020 | 23 Days over >25 degree
2021 | first 15.05.2021 | 15 Days over >25 degree

i will use this query in a Node Red Node to querry against the database and send telegram.

have a nice day
vinc

5

any idea or help to solve it?

6

“how the query should be to really show only the count over 1?”

Try a nested query:

select hightemp from (SELECT COUNT(“temp”) as hightemp FROM “temperatur” WHERE
“temp” >=25 GROUP BY time(24h)) where hightemp>1

I’ve not tested that exact query, but I’ve used the general principle in my
own queries, so give it a go and if you get an error message that you can’t
resolve, come back with the detaisl and we can try to help further.

Antony.

7

The Output of your Example. i had to change the name because it is a new database too.
But it should show 1 day, it is correct to have 10 Datas this mean 10 temperatur Measurement came over 25 degree

 >select TX25GE from (SELECT COUNT(average) as TX25GE FROM spez_days WHERE average >=25 GROUP BY time(24h)) where TX25GE>1
name: spez_days
time                TX25GE
----                ------
1627516800000000000 10

> select average from spez_days where average>=25
name: spez_days
time                average
----                -------
1627568934045212890 25.1
1627570134050625577 25
1627570734118811384 25.33
1627571334070399719 25.33
1627571934049091787 25.3
1627572534067067094 25.37
1627573134068110643 25.43
1627573734067848798 25.53
1627574334067293927 25.13
1627574934070196590 25.43
8

@Pooh just found out i could work, the time just shows the Date so this would be OK, so i don’t have to be fixed to much to the TX25GE but just to the time output.
i think i will have to put it into " INTO clause" and save it as measurment
and in a second query take the sum of this or a fist and last input