Tim's blah blah blah

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 setup board, note the 10cm wire on the dallas sensor

esp_mobile

esp_kidsroom setup board.

esp_kidsroom

esp_bathroom setup board, no screen

esp_bathroom

esp_living setup board, no screen

esp_living

esp_bedroom setup board, no screen, slightly spaced sensors

esp_bedroom

Calibration methodology

I check my sensor measurements against a reference sensor (Aqara / LUMI T/RH/P sensor - archived). 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
bme280 ±0.5°C
dallas1 +0/-0.4°C
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.

There are gaps in some sensor data
# 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'

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)
Sampling frequency is not constant

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.

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),*
Difference between sensor readings as function of increasing averaging window duration (1 to \~100 hours)

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:

  1. Upload new firmware
  2. Correct last two days of data first before board reboots (~1min)
  3. Correct all old data (board might have been rebooted now)
  4. 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)
Data after calibration showing near perfect correlation
Difference between sensor readings as function of increasing averaging window duration (1 to \~45 hours)

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:

Data after calibration showing near perfect correlation
Difference between sensor readings as function of increasing averaging window duration (1 to \~100 hours)

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:

Data after calibration showing near perfect correlation
Difference between sensor readings as function of increasing averaging window duration (1 to \~100 hours)

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)
Data after calibration showing near perfect correlation
Difference between sensor readings as function of increasing averaging window duration (1 to \~170 hours)

#smarthome #esp8266