influxdb time precision prevents me to join wind and temp/hum

[Thomas V]

Hi all,

I have an issue not directly link to rtl_433 it-self but maybe you could help me.

I am trying to implement a Wind Chill calculator in my project.

The math formula requires to use the wind speed like that:

wc = 13.12 + 0.6215*t - 11.37*math.pow(v, 0.16) + 0.3965*t*math.pow(v, 0.16)

t = temp, v=wind speed.

My issue is that, rtl_433 stores in InfluxDB the wind in a second measurement because it is not in same radio packet. Therefore, I am not able to do a proper select because timestamp are not the same. I guess because of a few sec/nano-sec of diff.

> select temperature_C, humidity, wind_avg_km_h from "LaCrosse-TX141W" WHERE time > now() - 12h ORDER by time limit 10;
name: LaCrosse-TX141W
time                temperature_C humidity wind_avg_km_h
----                ------------- -------- -------------
1611463758370533787 -14.4         67       
1611463759943055158                        1.7
1611463791401505375 -14.4         67       2.3
1611463821286742431 -14.4         67       
1611463822859352270                        1.7
1611463852750571458 -14.4         67       
1611463854317619490                        1.7
1611463884202928828 -14.4         67       
1611463885775548917                        1.3
1611463915660964576 -14.4         67       
> 

Any idea please how I could link all measurements together from the same series? The weather station sends 6 packets (3x temp/hum, 3x wind avg) each minutes ircc.

Thanks !

Thomas

[Philip Knowles]

It’s a while since I did any MySQL programming but you could create a query based on the last readings of both

Select .... ORDER BY wind_avg_km_h DESC LIMIT 1
and JOIN the tables

 

Regards

 

Phil K

 

Sent from Mail for Windows 10

--
You received this message because you are subscribed to the Google Groups "rtl_433" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rtl_433+u...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/rtl_433/f94e7433-1a6d-4cc7-a853-27b6dd0d67bbn%40googlegroups.com.

 

[Thomas V]

I can't join the result as it is not the same time.

[Christian Z.]

Maybe use the syslog output and https://github.com/merbanan/rtl_433/blob/master/examples/rtl_433_influxdb_relay.py

Collect both messages, then send a single aggregated point to influx, possibly with the wind chill already calculated.

[Philip Knowles]

The time isn’t important – you can use CONCAT to join the2 rows.

To view this discussion on the web, visit https://groups.google.com/d/msgid/rtl_433/4779b547-e733-41b4-9486-0a74917c174dn%40googlegroups.com.

 

[Thomas V]

I don't know how to CONCAT (or join) 2 lines without a common field.

I wonder if I could "round"the nano second timestamp to approximative ~seconds/minutes then group common series together.

[Greg Troxel]

Thomas V <thomas.v...@gmail.com> writes:

> I don't know how to CONCAT (or join) 2 lines without a common field.
>
> I wonder if I could "round"the nano second timestamp to approximative
> ~seconds/minutes then group common series together.

I have never heard of SQL supporting this. But I could imagine a join
where instead of equal it was "closest match, required to be closer than
X".

What I'd do is iterate over the less frequent reading, and for each, do
another query to find the nearby ones, find nearest, and use it. Or
iterate over both in parallel with two cursors if you care about
efficiency.

[eha...@gmail.com]

If you don't mind your data being a bit less frequent, you can let Influxdb do some aggregation and selection for you.
Something like (and I haven't tested this so you may need to mess with it a bit)
> SELECT median( temperature_C,) median(humidity), median(wind_avg_km_h) FROM  "LaCrosse-TX141W" WHERE time > now() - 12h GROUP BY time(10s) fill(previous) ORDER BY time LIMIT 10

Influx will look at a 10s window, take the median value for each of your measurements within that window (or fill with the previous value if there's nothing) and return them with the time for that row being the start time of the window. 

https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#advanced-group-by-time-syntax
for reference

[Thomas Vassilian]

Thanks a lot for the query. This works perfectly.

--

You received this message because you are subscribed to the Google Groups "rtl_433" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rtl_433+u...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/rtl_433/b381aa07-20b5-4601-af40-5867a93d9bf9n%40googlegroups.com.