http://weather.wilmslowastro.com/highch ... raphs2.htm
The UV Index is normally quoted as a rolling 10 or 15 minute average value to even out the 'spikes' and lows caused by clouds.
Rather than calculate a rolling average for every record each time it is required which is quite expensive, I decided add the average to the real time table.
Anyway, to calculate the average I used a pre-insert trigger on the real time table, so the value gets calculated every time a new 1 minute entry is added.
First I added a new table column "UV_AVG" as decimal(3,1).
Then created a new trigger:
Code: Select all
DELIMITER $$
DROP TRIGGER RT_INSERT;
CREATE TRIGGER RT_INSERT BEFORE INSERT ON `realtime`
FOR EACH ROW
BEGIN
SET NEW.UV_AVG = (SELECT ROUND(AVG(t1.UV),1)
FROM (SELECT UV
FROM realtime
ORDER BY LogDateTime DESC LIMIT 10) t1);
END;
Code: Select all
...
FROM realtime
WHERE LogDateTime >= DATE_SUB(NEW.LogDateTime, INTERVAL 10 MINUTE) ) t1
If any of that is incomprehensible, then it probably isn't for you, wait for Steve to plough through 100's of feature requests to get to the UV average one, MySQL support isn't something I am qualified or up for