Best approach to Logging data in a rdbms or change perspective and use a time series db

Sensors
1

Until discovering OMG i have been connecting to and querying my miflora and mija ble sensors individually, the one benefit of this is the retrieval of a complete data record which lends itself to the construction of a simple sql insert statement into a basic Miflora or Mija table containing all the relevant data fields for the respective sensor readings.

Now using OMG to retrieve the data from the publicly advertised data from the sensors a am getting payloads of a single sensor reading rather than a full data set.

I could just carry on with a minor node red function edit to use null as a default data element when constructing the sql or each insertion but that will result in sparsely populated tables which i do recall as being a big nono. I could go full boyce codd and end up with multiple sensor tables keyed by parent device id and a timestamp.
Or i could just start using a time series db??

Before i plunge into yet another new(to me) tech i thought it wise to ask here if there is a best practice trick for buffering data for sql inserts or should i just get on and RTFM… thanks…

2

I had the same issue and built a Node-Red flow to construct a complete message. I then tried the Tasmota ESP32 build which gives
21:40:14 MQT: tele/MiFlora/SENSOR = {“Time”:“2020-11-24T21:40:14”,“Flora-6b3be0”:{“Temperature”:11.2,“Illuminance”:151,“Moisture”:21,“Fertility”:166,“Firmware”:“3.2.2”,“Battery”:12,“RSSI”:-72},“TempUnit”:“C”}
I still use OMG for my BLE beacons

3

Indeed the sensor data are advertised one at a time for miflora, sometimes 2 at a time for mi jia. The gateway publication reflect the sensor data received. We are not using a connection to retrieve data. This could enable to retrieve all the data in a short time frame but will decrease the sensor battery life.

4

Thanks for the feedback, So no quick fix, its a paradigm shift or compromise, hmm… Indeed @1technophile I walked in eyes wide open to this feature of OMG I just completely overlooked the logging implications until i sat looking at the data arriving.

I lurve Tasmota Phil and appreciate your input here and over in tasmotausers… but with a growing number of sensors battery life is a Significant factor not just the cost but the hassle of changing them, that reminds me PIR batteries need a recharge…

Timeseries DB’s im coming to the party so its about to stop being Kewl :wink:
after all how hard can it be I mean “I know all the joins”…?

1 Like