Calibrating esp8266 temperature sensors
(Updated: )
I built temperature sensors using the ESP8266 platform, however these suffer from self-heating, and produce reliable but offset temperatures. Here I explain how I calibrated this and how I modified historical Influxdb data.
Problem statement ¶
I built temperature sensors using the ESP8266 platform, however these suffer from self-heating, and produce reliable but offset temperatures.
Physically, this should give a constant temperature offset regardless of ambient temperature, as the sensor will find an equilibrium between inflow of heat through dissipation and (mostly) radiative cooling, and given that radiative cooling is linear to temperature difference. Note that if there is a strong and varying component of convective cooling (i.e. wind), this offset will vary and this approach will not work.
The end result for one of the sensors looks very satisfying:
Summary ¶
These are my calibration results
| Param/sensor | esp_mobile | esp_kidsroom | esp_bathroom | esp_living | esp_bedroom |
|---|---|---|---|---|---|
| Sleep | none | 150s/180s | none | none | none |
| Screen | yes | no | no | yes | no |
| Aqara | 17.16 (ref) | 18.82 (ref) | 18.09 (ref) | 18.32 (ref) | 18.16 (ref) |
| Dallas1 mount | 10cm wire | 1cm spaced | fixed | heatsink | 1cm spaced |
| Dallas1 | 17.13 (-0.027) | 20.19 (+1.37) | 22.50 (+4.41) | 20.70 (+2.39) | 20.29 (+2.04) |
| bme280 mount | fixed | fixed | fixed | fixed | 1cm spaced |
| bme280 | 19.38 (+2.221) | 19.41 (+0.59) | N/A | 20.53 (+2.22) | 19.87 (+1.72) |
| mhz19b | 20.63 (+3.474) | 19.98 (+1.16) | 23.08 (+4.99) | 21.56 (+3.24) | 20.60 (+2.44) |
Setup ¶
esp_mobile
esp_kidsroom
esp_bathroom
esp_living
esp_bedroom
Calibration methodology ¶
I check my sensor measurements against a reference sensor (Aqara / LUMI T/RH/P sensor (maero.dk) - archived (archive.org)). The other data comes from BME280, DS18B20 (Dallas sensor) and MH-Z19B. Futhermore, one of my sensorboards (esp_mobile) has a Dallas sensor connected to a 10cm wire, which should mitigate any self-heating, and in fact the Aqara and wired Dallas sensors differ by 0.027°C only (sounds too good to be true).
The sensor specs are as follows:
| Source | Spec @ 20°C |
|---|---|
| aqara | ±0.3°C (digikey.com) |
| bme280 | ±0.5°C (adafruit.com) |
| dallas1 | +0/-0.4°C (maximintegrated.com) |
| mhz19b | ±1°C, only 1°C accuracy |
Inspecting calibration data ¶
As there is a gap in some datasets, we need to exclude this time range from all sensors when averaging for calibration.
# Gappy sensor data:
SELECT round(mean(value)*1000)/1000 FROM "environv3" WHERE time > '2022-01-19T21:09:00Z' AND time < '2022-01-24T20:00:00Z' AND "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' GROUP BY time(30m),*
SELECT first(value), last FROM temperaturev3 WHERE source = 'aqara' AND room = 'studyguest'
SELECT last(value) FROM temperaturev3 WHERE source = 'aqara' AND room = 'studyguest'
- Range 1: 2022-01-19T21:09:00Z - 2022-01-21T19:30:00Z (46.4 hrs)
- Range 2: 2022-01-22T09:00:00Z - 2022-01-24T20:00:00Z (58 hrs)
- Range total: 104.4hrs
Which results in the following two InfluxDB time filters:
WHERE (time > '2022-01-19T21:09:00Z' AND time < '2022-01-21T19:30:00Z')
WHERE (time > '2022-01-22T09:00:00Z' AND time < '2022-01-24T20:00:00Z')
Regularly sampling calibration data ¶
The aqara sensor don’t sample regularly. This means we need to interpolate and/or bin the signal before averaging it, also taking into account the gaps in the data.
SELECT count(value) as Tcount FROM temperaturev3 WHERE (time > '2022-01-19T21:09:00Z' AND time < '2022-01-24T20:00:00Z') AND source = 'aqara' AND room = 'studyguest' GROUP BY time(3h)
We solve this by using GROUP BY time(x) fill(linear), the first bins the data, the second interpolates linearly for empty buckets. This works OK if the gaps are not too big and we expect the temperature to change gradually. Alternatively we could use fill(none) to ignore big gaps.
SELECT round(mean(value)*1000)/1000 as Tall FROM temperaturev3 WHERE (time > '2022-01-19T21:09:00Z' AND time < '2022-01-24T20:00:00Z') AND source = 'aqara' AND room = 'studyguest' GROUP BY *
SELECT round(mean(temp_int)*1000)/1000 FROM (SELECT mean(value) as temp_int FROM temperaturev3 WHERE (time > '2022-01-19T21:09:00Z' AND time < '2022-01-24T20:00:00Z') AND source = 'aqara' AND room = 'studyguest' GROUP BY time(10m) fill(linear))
Using different grouping periods gives the following results, up to 0.15 °C delta!
| Binning | Average T (°C) |
|---|---|
| full dataset | 17.255 |
| group by time(24h) | 17.213 |
| group by time(12h) | 17.189 |
| group by time(6h) | 17.122 |
| group by time(3h) | 17.107 |
| group by time(1h) | 17.100 |
| group by time(10m) | 17.103 (-0.152) |
Putting calibration together ¶
Taking gappy data and irregular sampling into account gives the following queries
SELECT round(mean(value)*1000)/1000 FROM "environv3" WHERE (time > '2022-01-19T21:09:00Z' AND time < '2022-01-24T20:00:00Z') AND "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' GROUP BY *
SELECT round(mean(Tbinned)*1000)/1000 as Trange1 FROM (SELECT mean(value) as Tbinned FROM temperaturev3 WHERE (time > '2022-01-19T21:09:00Z' AND time < '2022-01-21T19:30:00Z') AND source = 'aqara' AND room = 'studyguest' GROUP BY time(1h), * fill(linear))
SELECT round(mean(Tbinned)*1000)/1000 as Trange2 FROM (SELECT mean(value) as Tbinned FROM temperaturev3 WHERE (time > '2022-01-22T09:00:00Z' AND time < '2022-01-24T20:00:00Z') AND source = 'aqara' AND room = 'studyguest' GROUP BY time(1h), * fill(linear))
| Source | T_range1 (°C) | T_range2 (°C) | Full range - total (°C) | Full range - 2nd fix (°C) |
|---|---|---|---|---|
| aqara | 17.679 (ref) | 16.744 (ref) | 17.160 (ref) | 17.160 (ref) |
| bme280 | 19.381 (+2.221) | 17.241 (+0.081) | ||
| dallas1 | 17.133 (-0.027) | 17.243 (+0.083) | ||
| mhz19b | 20.634 (+3.474) | 17.234 (+0.074) |
Where T_range1 and T_range2 have weights of 46.4 and 58 respectively, the duration of each time range in hours.
How much calibration is needed ¶
Here I used 104 hours ~ 4 days of calibration data, how much is really needed? After calibration I checked how well the sensors agree by taking an averaging window increasing from 0 to 104 hours, and comparing the averaged sensor values. I hacked this in a spreadsheet because of xkcd (xkcd.com).
SELECT round(mean(value)*1000)/1000 FROM "environv3" WHERE time > '2022-01-19T20:07:29Z' AND time < '2022-01-24T20:21:46Z' AND "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' GROUP BY time(1h),*
SELECT round(mean(value)*1000)/1000 FROM "temperaturev3" WHERE time > '2022-01-19T20:07:29Z' AND time < '2022-01-24T20:21:46Z' AND "source" = 'aqara' and "room" = 'studyguest' GROUP BY time(1h),*
We get reasonably agreement already after ~48 hours or 2 days.
Correcting existing data ¶
After calibration, we can correct the data, both new (coming from esphome.io) and existing (stored in InfluxDB).
Inspect ¶
SELECT count(value) FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' AND "source" = 'bme280' GROUP BY *
SELECT count(value) FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' AND "source" = 'dallas1'GROUP BY *
Backup & test ¶
MAke backup
SELECT value as value INTO "calibratebackupv3" FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' GROUP BY *
SELECT value as value INTO "calibratebackupv3" FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' AND time>now()-3d GROUP BY *
Test restoring backup
SELECT * INTO "environv3" FROM "calibratebackupv3" GROUP BY *
Update sensor firmware ¶
Update firmware in esphome (don’t upload yet).
filters:
- offset: 0.027 # Calibrated 20220124, improved w/ binning and gap fix 20220201
Rewrite measurements ¶
Once all above steps are done, here we rewrite the measurements. First we take the last 2 days so we can quickly get the data while immediately updating the sensor firmware to start reporting new values. If the query would take too long, the sensor might already be writing new data that has not been copied/correct yet.
Once we have the first 2 days’ worth of data, we copy the rest of the measurement. After that’s done we inject it back in the original measurement.
So:
- Upload new firmware
- Correct last two days of data first before board reboots (~1min)
- Correct all old data (board might have been rebooted now)
- Inject data back into original measurement
DROP SERIES FROM calibratev3
SELECT value-0.081 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' AND "source" = 'bme280' AND time>now()-2d GROUP BY *
SELECT value-0.083 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' AND "source" = 'dallas1' AND time>now()-2d GROUP BY *
SELECT value-0.074 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' AND "source" = 'mhz19b' AND time>now()-2d GROUP BY *
SELECT value-0.081 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' AND "source" = 'bme280' AND time<now()-1d GROUP BY *
SELECT value-0.083 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' AND "source" = 'dallas1' AND time<now()-1d GROUP BY *
SELECT value-0.074 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_mobile' AND "quantity" = 'T' AND "room" = 'guestoffice' AND "source" = 'mhz19b' AND time<now()-1d GROUP BY *
Inject back in original measurement
SELECT * INTO "environv3" FROM "calibratev3" GROUP BY *
Delete old data ¶
Delete old esp_mobile temperature data from previous mount (where we didn’t have calibration):
SELECT first(*) FROM environv3 WHERE board = 'esp_mobile' AND quantity = 'T' AND time<now()-1d GROUP BY *
SELECT first(*) FROM environv3 WHERE board = 'esp_mobile' AND quantity = 'T' AND AND room='guestoffice' AND time < '2021-04-11T12:40:28Z' GROUP BY *
DELETE FROM environv3 WHERE board = 'esp_mobile' AND quantity='T' AND room='guestoffice' AND time < '2021-04-11T12:40:28Z'
Clean up ¶
DROP SERIES FROM calibratev3
DROP SERIES FROM calibratebackupv3
Condensed update of other sensors ¶
Bathroom ¶
TODO: fix & calibrate BME280 sensor (currently broken?) TODO: subtract 0.196°C from calibration because we didn’t bin the Aqara data (unbinned: 18.126°C avg, binned: 17.93°C, delta=0.196°C)
Inspect, observe that not all sensors have same runtime (e.g. DS18B22 have been added in new mount later)
SELECT first(value) FROM "environv3" WHERE "board" = 'esp_bathroom' AND "quantity" = 'T' AND time<now()-1d GROUP BY *
Get calibration data
SELECT round(mean(value)*1000)/1000 as T_avg FROM "environv3" WHERE time > '2022-01-24 22:00:00' AND time < '2022-01-26 20:00:00' AND "board" = 'esp_bathroom' AND "quantity" = 'T' GROUP BY *
SELECT round(mean(value)*1000)/1000 as T_avg FROM "temperaturev3" WHERE time > '2022-01-24 22:00:00' AND time < '2022-01-26 20:00:00' AND "source" = 'aqara' GROUP BY *
Results:
| Source | Full range (°C) | Full range unbinned (°C) | Full range binned correction (°C) |
|---|---|---|---|
| Aqara | 18.09 (ref) | 18.09 (ref) | 17.93 (ref) |
| Dallas1 | 22.496 (+4.406) | 22.675 (+4.585) | 18.109 (-0.179) |
| mhz19b | 23.08 (+4.990) | 23.264 (+5.174) | 18.114 (-0.184) |
Test run
SELECT value as value INTO "calibratebackupv3" FROM "environv3" WHERE "board" = 'esp_bathroom' AND "quantity" = 'T' AND time>now()-3d GROUP BY *
SELECT count(*) FROM calibratebackupv3
Test restoring backup
SELECT * INTO "environv3" FROM "calibratebackupv3" GROUP BY *
Update firmware (don’t upload yet)
filters:
- offset: -4.406 # Calibrated 20220126, binning fixed 20220209
filters:
- offset: -4.990 # Calibrated 20220126, binning fixed 20220209
Now upload, then quickly fix recent data, then relax and fix old data.
DROP SERIES FROM calibratev3
SELECT value-0.179 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bathroom' AND "quantity" = 'T' AND "source" = 'dallas1' AND time>now()-2d GROUP BY *
SELECT value-0.184 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bathroom' AND "quantity" = 'T' AND "source" = 'mhz19b' AND time>now()-2d GROUP BY *
SELECT value-0.179 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bathroom' AND "quantity" = 'T' AND "source" = 'dallas1' AND time<'2022-01-26 19:18:00' GROUP BY *
SELECT value-0.184 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bathroom' AND "quantity" = 'T' AND "source" = 'mhz19b' AND time<'2022-01-26 19:18:00' GROUP BY *
Inject back in original measurement
SELECT * INTO "environv3" FROM "calibratev3" GROUP BY *
Clean up
DROP SERIES FROM calibratev3
DROP SERIES FROM calibratebackupv3
Delete old esp_bathroom temperature data from previous mount (where we didn’t have calibration):
DELETE FROM environv3 WHERE board = 'esp_bathroom' AND quantity='T' AND time < '2021-02-23T10:34:06Z';
Verification & check of averagin window need
SELECT round(mean(value)*1000)/1000 FROM "environv3" WHERE (time > '2022-01-24 22:00:00' AND time < '2022-01-26 20:00:00') AND "board" = 'esp_bathroom' AND "quantity" = 'T' AND "room" = 'bathroom' GROUP BY time(1h),*
SELECT round(mean(Tbinned)*1000)/1000 as Tcalib FROM (SELECT mean(value) as Tbinned FROM temperaturev3 WHERE (time > '2022-01-24 22:00:00' AND time < '2022-01-26 20:00:00') AND source = 'aqara' AND room = 'bathroom' GROUP BY time(1h), * fill(linear)) GROUP BY time(1h)
Bedroom ¶
Inspect, observe that not all sensors have same runtime (e.g. DS18B22 have been added in new mount per 2020-12-28T09:51:59Z, and aqara started at 2022-01-26T20:39:07Z)
SELECT first(*) FROM "environv3" WHERE "board" = 'esp_bedroom' AND "quantity" = 'T' AND time<now()-1d GROUP BY *
SELECT first(*) FROM "temperaturev3" WHERE "source" = 'aqara' GROUP BY *
Dallas appears at 2020-12-28T09:51:59Z, remove temperature data before that (because we used a different mount with different self-heating)
DELETE FROM environv3 WHERE board = 'esp_bedroom' AND quantity='T' AND time < '2020-12-28T09:51:59Z'
Get calibration data
SELECT round(mean(value)*1000)/1000 as T_avg FROM "environv3" WHERE time > '2022-02-09T21:30:00Z' AND time < '2022-02-13T20:00:00Z' AND "board" = 'esp_bedroom' AND "quantity" = 'T' GROUP BY time(1h),*
SELECT round(mean(Tbinned)*1000)/1000 as Tcalib FROM (SELECT mean(value) as Tbinned FROM temperaturev3 WHERE (time > '2022-02-09T21:30:00Z' AND time < '2022-02-13T20:00:00Z') AND source = 'aqara' GROUP BY time(1h), * fill(linear))GROUP BY time(1h),*
# For Dallas only (which stopped working somehow)
SELECT round(mean(Tbinned)*1000)/1000 as Tcalib FROM (SELECT mean(value) as Tbinned FROM temperaturev3 WHERE (time > '2022-02-09T21:30:00Z' AND time < '2022-02-12T18:00:00Z') AND source = 'aqara' GROUP BY time(1h), * fill(linear))GROUP BY *
Results:
| Source | Full range (°C) | Dallas1 time range (°C) |
|---|---|---|
| Aqara | 18.155 (ref) | 18.250 (ref) |
| Dallas1 | 20.289 (+2.039) | |
| mhz19b | 20.597 (+2.442) | |
| bme280 | 19.872 (+1.717) |
N.B. Dallas1 sensor broke down before calibration time was over and was replaced afterwards.
DROP SERIES FROM calibratev3
SELECT value-2.134 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bedroom' AND "quantity" = 'T' AND "source" = 'dallas1' AND time>now()-2d GROUP BY *
SELECT value-2.442 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bedroom' AND "quantity" = 'T' AND "source" = 'mhz19b' AND time>now()-2d GROUP BY *
SELECT value-1.717 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bedroom' AND "quantity" = 'T' AND "source" = 'bme280' AND time>now()-2d GROUP BY *
SELECT value-2.134 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bedroom' AND "quantity" = 'T' AND "source" = 'dallas1' AND time<now()-1d GROUP BY *
SELECT value-2.442 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bedroom' AND "quantity" = 'T' AND "source" = 'mhz19b' AND time<now()-1d GROUP BY *
SELECT value-1.717 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_bedroom' AND "quantity" = 'T' AND "source" = 'bme280' AND time<now()-1d GROUP BY *
Inject back in original measurement
SELECT * INTO "environv3" FROM "calibratev3" GROUP BY *
Clean up
DROP SERIES FROM calibratev3
DROP SERIES FROM calibratebackupv3
Verification & check of averagin window need:
Nursery ¶
Inspect data
SELECT first(*), count(*) FROM "environv3" WHERE "board" = 'esp_kidsroom' AND "quantity" = 'T' AND time<now()-1d GROUP BY *
SELECT first(*) FROM "temperaturev3" WHERE "source" = 'aqara' GROUP BY *
Dallas appears at 2020-12-22T17:25:59Z, remove temperature data before that (because we used a different mount with different self-heating)
DELETE FROM environv3 WHERE board = 'esp_kidsroom' AND quantity='T' AND time < '2020-12-22T17:25:59Z'
Get calibration data, Aqara starts from 2022-01-29T10:00:26Z
SELECT round(mean(value)*1000)/1000 FROM "environv3" WHERE (time > '2022-01-29T10:00:26Z' AND time < '2022-02-02T20:00:00Z') AND "board" = 'esp_kidsroom' AND "quantity" = 'T' AND "room" = 'kidsroom' GROUP BY time(1h),*
SELECT round(mean(Tbinned)*1000)/1000 as Tcalib FROM (SELECT mean(value) as Tbinned FROM temperaturev3 WHERE (time > '2022-01-29T10:00:26Z' AND time < '2022-02-02T20:00:00Z') AND source = 'aqara' AND room = 'nursery' GROUP BY time(1h), * fill(linear)) GROUP BY time(1h)
Results:
| Source | Full range (°C) |
|---|---|
| Aqara | 18.821 (ref) |
| Dallas1 | 20.187 (+1.366) |
| mhz19b | 19.978 (+1.157) |
| bme280 | 19.414 (+0.593) |
DROP SERIES FROM calibratev3
SELECT value-1.366 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_kidsroom' AND "quantity" = 'T' AND "source" = 'dallas1' AND time>now()-2d GROUP BY *
SELECT value-1.157 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_kidsroom' AND "quantity" = 'T' AND "source" = 'mhz19b' AND time>now()-2d GROUP BY *
SELECT value-0.593 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_kidsroom' AND "quantity" = 'T' AND "source" = 'bme280' AND time>now()-2d GROUP BY *
SELECT value-1.366 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_kidsroom' AND "quantity" = 'T' AND "source" = 'dallas1' AND time<now()-1d GROUP BY *
SELECT value-1.157 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_kidsroom' AND "quantity" = 'T' AND "source" = 'mhz19b' AND time<now()-1d GROUP BY *
SELECT value-0.593 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_kidsroom' AND "quantity" = 'T' AND "source" = 'bme280' AND time<now()-1d GROUP BY *
Inject back in original measurement
SELECT * INTO "environv3" FROM "calibratev3" GROUP BY *
Clean up
DROP SERIES FROM calibratev3
DROP SERIES FROM calibratebackupv3
Verification & check of averagin window need:
Living room ¶
Inspect data
SELECT first(*) FROM "environv3" WHERE "board" = 'esp_living' AND "quantity" = 'T' AND time<now()-1d GROUP BY *
SELECT first(*) FROM "temperaturev3" WHERE "source" = 'aqara' GROUP BY *
Dallas appears at 2020-12-13T10:16:47Z, remove temperature data before that (because we used a different mount with different self-heating)
DELETE FROM environv3 WHERE board = 'esp_living' AND quantity='T' AND time < '2020-12-13T10:16:47Z'
Get calibration data, Aqara starts from 2022-02-02T20:20:59Z
SELECT round(mean(value)*1000)/1000 FROM "environv3" WHERE (time > '2022-02-02T20:20:59Z' AND time < '2022-02-09T20:00:00Z') AND "board" = 'esp_living' AND "quantity" = 'T' AND "room" = 'living' GROUP BY *
SELECT round(mean(Tbinned)*1000)/1000 as Tcalib FROM (SELECT mean(value) as Tbinned FROM temperaturev3 WHERE (time > '2022-02-02T20:20:59Z' AND time < '2022-02-09T20:00:00Z') AND source = 'aqara' AND room = 'livingroom' GROUP BY time(1h), * fill(linear))
Results:
| Source | Full range (°C) |
|---|---|
| Aqara | 18.317 (ref) |
| Dallas1 | 20.704 (+2.387) |
| mhz19b | 21.561 (+3.244) |
| bme280 | 20.532 (+2.215) |
DROP SERIES FROM calibratev3
SELECT value-2.387 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_living' AND "quantity" = 'T' AND "source" = 'dallas1' AND time>now()-2d GROUP BY *
SELECT value-3.244 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_living' AND "quantity" = 'T' AND "source" = 'mhz19b' AND time>now()-2d GROUP BY *
SELECT value-2.215 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_living' AND "quantity" = 'T' AND "source" = 'bme280' AND time>now()-2d GROUP BY *
SELECT value-2.387 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_living' AND "quantity" = 'T' AND "source" = 'dallas1' AND time<now()-1d GROUP BY *
SELECT value-3.244 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_living' AND "quantity" = 'T' AND "source" = 'mhz19b' AND time<now()-1d GROUP BY *
SELECT value-2.215 as value INTO "calibratev3" FROM "environv3" WHERE "board" = 'esp_living' AND "quantity" = 'T' AND "source" = 'bme280' AND time<now()-1d GROUP BY *
Inject back in original measurement
SELECT * INTO "environv3" FROM "calibratev3" GROUP BY *
Clean up
DROP SERIES FROM calibratev3
Verification & check of averagin window need
SELECT round(mean(value)*1000)/1000 FROM "environv3" WHERE (time > '2022-02-02T20:20:59Z' AND time < '2022-02-09T20:00:00Z') AND "board" = 'esp_living' AND "quantity" = 'T' AND "room" = 'living' GROUP BY time(1h),*
SELECT round(mean(Tbinned)*1000)/1000 as Tcalib FROM (SELECT mean(value) as Tbinned FROM temperaturev3 WHERE (time > '2022-02-02T20:20:59Z' AND time < '2022-02-09T20:00:00Z') AND source = 'aqara' AND room = 'livingroom' GROUP BY time(1h), * fill(linear)) GROUP BY time(1h)
