Adding modfiers for non-calibratable sensors-CodePudding

I have some 433MHz temperature sensors that I log. One of these is used for the furnace hot water circuit which goes to my radiators and water heater. Since I cannot recalibrate the sensor, and I am measuing the temperature with a probe mashed in between the insulation and the pipe, I get a measurement that is 10°C off, compared to the analog, properly calibrated sensor which come with the insulation.

I store the values in a MySQL database, and for future use, I prefer to store the raw data. I have a separate table for the sensor metadata.

I want to do something like

select
  sd.sensorid, 
  sd.unittype,
  sd.value * sm.modifier as realvalue
from
  telldus.sensorData sd 
    left join sensormeta sm
    on sensorData.id = sensormeta.id
group by 
  sm.id,
  unittype

(I want to be able to do this, because i think that it might be a multiplier not just an addition)

But this would require me havinb an int value - I'd like to store the operator in the field as well. (*1.1/ 10, etc). I don't see this as possible...

Sensor metadata

id	type	modifier	name
135	temperature/humidity	0	Soveværelse 1
136	temperature/humidity	0	Udendørs østvendt
147	temperature/humidity	10	varmtvandskreds
166	temperature/humidity	0	Stue 1
167	temperature/humidity	0	Soveværelse 2

Sensordata

id	unittype	time	value
135	temperature	01:00	21.2
135	humidity	01:00	51
136	temperature	01:00	-0.3
147	temperature	01:02	22.2
147	humidity	01:02	41
166	temperature	01:00	20.7
166	humidity	01:00	31
167	temperature	01:00	11.7
167	humidity	01:00	64
199	temperature	00:50	51.2
215	temperature	01:01	4.1

CodePudding user response：

Use another column to hold the operation.

id	type	modifier	operation	name
135	temperature/humidity	0	none	Soveværelse 1
136	temperature/humidity	0	none	Udendørs østvendt
147	temperature/humidity	10	add	varmtvandskreds
166	temperature/humidity	1.1	multiply	Stue 1
167	temperature/humidity	0	none	Soveværelse 2

Then the query will begin:

select
  sd.sensorid, 
  sd.unittype,
  CASE sm.operation
    WHEN 'multiply' THEN sd.value * sm.modifier
    WHEN 'add' THEN sd.value   sm.modifier
    ELSE sd.value
  END as realvalue