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
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
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!
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.
“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.
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
@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