Combine individual sensor values into an overall value with correction factors For a school project, we are trying to calculate (corrected) Ozone values based on a combination of 5 sensors (3x O3, 1x temp, 1x humidity).

We are using MySQL and PHP for the rest of the project.

The table Measurements has this structure:

id (int(11))
time (datetime)
value (float)
measured_value (float)
sensor (tinytext)
unit (tinytext)
measurement_short_type (tinytext)
stream_id int(11)


so an example row looks like this:

ID    Time                 value  measured_value  sensor  unit   measurement_short_type  stream_id
----  -------------------  -----  --------------  ------  -----  ----------------------  ---------
3324  2016-05-21 11:00:34  0      193             O3r     KOhms  O3                      6511


As you can see, we have 2 columns, value (float) and measured_value (float).

In order to calculate the correct, final value based on the individual sensor data (stored in measured_value), we need to apply a formula similar to this for every datapoint:

Corrected value[datetime] = ("6511".measured_value[datetime] * -0.106830613)
                          + ("6512".measured_value[datetime] * 0.065201457)
                          + ("6513".measured_value[datetime] * 0.335456161)
                          + ("6514".measured_value[datetime] * -0.248569888)
                          + ("6515".measured_value[datetime] * 1.423460009) + -2.113591555


where 6511, 6512, ..., 6515 are the IDs for the 5 sensors (3x O3, 1x temp, 1x humidity). The values for all sensors are stored in the same table.

The notation "6514". measured_value[datetime] means: use the measured_value (not value!) of the row that has approximately the same datetime stamp, and the stream ID 6514. 

All sensors report their value approximately at the same time, every 30 minutes (i.e. x:00 and x:30), but not exactly: one sensor could have 10:59:59 as timestamp, another 11:00:34.

As far as I know, there are 2 ingredients to solving this:


How can we apply the correction per the formula above in MySQL to get an exact value, based on the 5 sensor input values that are generated roughly every 30 minutes?
Since the timestamps for the 5 individual sensors are not always exactly the same, is there a way to have a time "fudge factor", since the corresponding sensor input values are never reported at exactly the same timestamp?


If this is not possible, can somebody advise on how to achieve this? We cannot influence the timing of the sensors, but we could change the DB/table structure if that facilitates the calculation.
You could do the following, provided that your sensor 6511 takes measurements reliably every 30 minutes (use instead the one that is most reliable). This will calculate measurements based on the recordings of that primary sensor used (in the main FROM, in this case 6511), show the exact date and time of such recording, also show an adjusted time in 30 min slots, and use any available measurements for all other sensors, whichever latest available up to 10 minutes after the primary measurement:

SELECT "date",
       DATE("date") + INTERVAL round((UNIX_TIMESTAMP("date") - UNIX_TIMESTAMP(DATE("date")))/1800) * 30 MINUTE AS adj_date,
       (measured_value * -0.106830613) +
       (SELECT measured_value * 0.065201457
        FROM Measurements
        WHERE stream_id = '6512' AND "date" &lt; (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) +
       (SELECT measured_value * 0.335456161
        FROM Measurements
        WHERE stream_id = '6513' AND "date" &lt; (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) +
       (SELECT measured_value * -0.248569888
        FROM Measurements
        WHERE stream_id = '6514' AND "date" &lt; (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) +
       (SELECT measured_value * 1.423460009
        FROM Measurements
        WHERE stream_id = '6515' AND "date" &lt; (s1."date" + INTERVAL 10 MINUTE)
        ORDER BY "date" DESC LIMIT 1) + -2.113591555 AS "Corrected value"
FROM "Measurements" AS s1
WHERE stream_id = '6511';


Ensure you have an index by stream_id, "date"!