Cumulus Support

Code: Select all

############################################################################################
# 1. Create the table                                                                      #
############################################################################################
DROP TABLE IF EXISTS rainsuninsolByMonthHour;
CREATE TABLE rainsuninsolByMonthHour (
	LogDateTime datetime NOT NULL,
	rain decimal(4,1) NOT NULL,
	sun decimal(4,1) NOT NULL,
	insolation decimal(4,2) NOT NULL,
	PRIMARY KEY (LogDateTime)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='rain and sun by Year, Month, Day, Hour';

############################################################################################
# 2. Insert the data                                                                       #
############################################################################################
INSERT IGNORE INTO rainsuninsolByMonthHour
SELECT
	MAX(LogDateTime_1) AS LogDateTime,
	ROUND(SUM(diffrain), 1) AS rain,
	ROUND(SUM(diffsun), 1) AS sun,
	ROUND(SUM(insolation), 2) AS insolation
FROM
	(SELECT
		LogDateTime,
		DATE_SUB(LogDateTime, INTERVAL 1 MINUTE) AS LogDateTime_1,
		IF((diffTodayRainSoFar + 0.1) >= diffRainSinceMidnight, diffTodayRainSoFar, diffRainSinceMidnight) diffrain,
		IF(TIME(LogDateTime) = "00:00:00", 0, diffHrsSunShine) AS diffsun,
		(((TIMESTAMPDIFF(SECOND,pLogDateTime,LogDateTime) / 3600) * ((SolarRad + pSolarRad)/2)) / 1000 * 3.6) insolation
	FROM
		(SELECT 
			Monthly.LogDateTime AS LogDateTime, 
			@plogdatetime:= @prev_LogDateTime AS pLogDateTime,
			@prev_LogDateTime:= Monthly.LogDateTime AS dummyLDT,

			(Monthly.TodayRainSoFar - @prev_TodayRainSoFar) AS diffTodayRainSoFar,
			@ptodayrainsofar:= @prev_TodayRainSoFar AS pTodayRainSoFar,
			@prev_TodayRainSoFar:= Monthly.TodayRainSoFar AS dummyRF,

			(Monthly.RainSinceMidnight - @prev_RainSinceMidnight) AS diffRainSinceMidnight,
			@prainsincemidnight:= @prev_RainSinceMidnight AS pRainSinceMidnight,
			@prev_RainSinceMidnight:= Monthly.RainSinceMidnight AS dummyRSM,

			(Monthly.HrsSunShine - @prev_HrsSunShine) AS diffHrsSunShine,
			@phrssunshine:= @prev_HrsSunShine AS pHrsSunShine,
			@prev_HrsSunShine:= Monthly.HrsSunShine AS dummySS,

			Monthly.SolarRad AS SolarRad,
			@psolarrad:= @prev_SolarRad AS pSolarRad,
			@prev_SolarRad:= Monthly.SolarRad AS dummySR
		FROM 
			Monthly, 
			(SELECT @prev_LogDateTime:= 0 AS t1) AS z1,
			(SELECT @prev_TodayRainSoFar:= 0 AS t2) AS z2,
			(SELECT @prev_RainSinceMidnight:= 0 AS t3) AS z3,
			(SELECT @prev_HrsSunShine:= 0 AS t4) AS z4,
			(SELECT @prev_SolarRad:= 0 AS t5) AS z5
		WHERE LogDateTime >= "2021-01-17"
		LIMIT 1000
		) AS a
	) AS b
GROUP BY DATE(LogDateTime_1), HOUR(LogDateTime_1)
ORDER BY LogDateTime_1 ASC;