Welcome to the Cumulus Support forum.

Latest Cumulus MX V3 release 3.28.5 (build 3282) - 23 February 2024

Cumulus MX V4 beta test release 4.0.0 (build 4017) - 17 March 2024

Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

Highcharts Graphs

Discussion and support for 3rd-party (non-Sandaysoft) tools for Cumulus
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Highcharts Graphs

Post by The it guy »

Hi all I know this is a cop Out but I'm trying to set up the Highcharts Graphs databases I was reading about something in where you need triggers and table structures for columns is there any nice people out there will share there database layout and columns and triggers

As I need the structure for

Sunbyhour

Sun by month hour

Daily insolation
stewartwlewis
Posts: 38
Joined: Sat 29 Jan 2011 7:08 pm
Weather Station: Davis VP2 Plus
Operating System: Rasbian on RPI
Location: Durham, Co Durham
Contact:

Re: Highcharts Graphs

Post by stewartwlewis »

These are the structures and triggers I use for SunbyHour, SunbyMonth Hour and TempbyMonth Hour. I don't have the Daily Insolation ones and have been looking for them plus those for the Temperature Comfort Chart for a while so if you have those it would be great if you could share.
The table files are sql table creation scripts that you can run from within your database. The triggers are specific to my database name so you will need to amend those to your specific name. Also I added solar and uv sensors after the main data collection started so you will need to amend or adjust those where statements.
You do not have the required permissions to view the files attached to this post.
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Highcharts Graphs

Post by The it guy »

ok thank u so much for them and ive had a look about and ive havent got them but ive hade a look and it seem i can olny see mark as got them on his site so if he is very nice maybe he can share all the one are missing incuding the air quality :D :o
User avatar
billy
Posts: 255
Joined: Mon 30 Nov 2015 10:54 am
Weather Station: WLL / Davis VP2+
Operating System: RPi bullseye
Location: Gooseberry Hill, Western Australia

Re: Highcharts Graphs

Post by billy »

For what it is worth, I keep a MySQL table of rain, sunshine and insolation for every hour of every day. It gets updated every time the cmx log file (monthly) gets updated (every 5 minutes). This table is used for the historic drill-down graphs (and a few others). See https://tillyspaws.com/vp2waw/graphs_historic.php

The zip has a file that creates the table and another for updating it (a trigger that runs immediately after the monthly update).
You do not have the required permissions to view the files attached to this post.
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Highcharts Graphs

Post by The it guy »

Ok Billy thank you very much for that code it's a great help I have checked against what I actually need I was trying to strip down the bit I don't need to go in the database but my knowledge is way out of lead with this!!!
and I look at your site and I can see u have the UV ones can you mop the code as all the rest are working because I am absolutely useless with SQL and just send me the UV

i did try and take out the bits i dont need but as i said it all went Wrong im sorry for the exra work for you

I only need the UV ones

The daily one

And by Month
User avatar
billy
Posts: 255
Joined: Mon 30 Nov 2015 10:54 am
Weather Station: WLL / Davis VP2+
Operating System: RPi bullseye
Location: Gooseberry Hill, Western Australia

Re: Highcharts Graphs

Post by billy »

Well the data for the historic UV graphs just come from the dayfile table - see the zip
You do not have the required permissions to view the files attached to this post.
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Highcharts Graphs

Post by The it guy »

as billy been a great help for geting this working but as he role over is at 9am but mine is at midnight i cant see where to ajust the time

and when i in put this in my database it saying

Warning: #1292 Incorrect datetime value: '0'

Warning: #1048 Column 'insolation' cannot be null

and i see i had off rain i cheack the logs and there nothing in the saying -6.4?


2021-01-17 00:59:00
-6.4
0.0
0.00

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;

Mapantz
Posts: 1772
Joined: Sat 17 Dec 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 11 x64
Location: Dorset - UK
Contact:

Re: Highcharts Graphs

Post by Mapantz »

I get the same warnings in testing..

The nagative rain value always appears at 23:59 for me. It doesn't cause any issues as far as I can see?!
Image
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Highcharts Graphs

Post by The it guy »

well your having more luck then me then

as it plotting to the chart like this

[[0,-28.6],[1,0],[2,0],[3,0],[4,0],[5,0],[6,0],[7,2.9],[8,45.7],[9,21.4],[10,15.7],[11,21.4],[12,25.7],[13,21.4],[14,0],[15,30],[16,27.1],[17,0],[18,0],[19,0],[20,0],[21,0],[22,0],[23,0]]
Capture22222222.PNG
You do not have the required permissions to view the files attached to this post.
Mapantz
Posts: 1772
Joined: Sat 17 Dec 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 11 x64
Location: Dorset - UK
Contact:

Re: Highcharts Graphs

Post by Mapantz »

What chart is that supposed to be showing?
Image
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Highcharts Graphs

Post by The it guy »

sorry the top got cut off it the sunshine by hr
Capture21.PNG
You do not have the required permissions to view the files attached to this post.
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Highcharts Graphs

Post by The it guy »

Or if you kindly share your insert statement and your triggers? Then maybe it will work more better 🤔?
User avatar
billy
Posts: 255
Joined: Mon 30 Nov 2015 10:54 am
Weather Station: WLL / Davis VP2+
Operating System: RPi bullseye
Location: Gooseberry Hill, Western Australia

Re: Highcharts Graphs

Post by billy »

The it guy wrote: Sun 09 May 2021 10:53 am Warning: #1048 Column 'insolation' cannot be null
This may be because there are complete days missing from the monthly file, which really messes things up. I have one of these and solve it by putting in a single value for that day:
INSERT IGNORE INTO rainsuninsolByMonthHour(LogDateTime)
VALUES
("2016-07-14 12:59:00");

The it guy wrote: Sun 09 May 2021 10:53 am 2021-01-17 00:59:00
-6.4
0.0
0.00
I presume the "-6.4" is for the hourly rainfall from 00 to 01 hours. The problem we have is this. Cumulus accumulates the rainfall for the day but at the end of the last log interval for the day it resets the accumulation to zero. So, my rollover is at 0900 and I log at 5 minute intervals. On a day where it has rained, the 0900 monthly log update will always record TodayRainSoFar as zero and if I use the difference between that and the previous value, I will get a negative number. I solved this problem by using RainSinceMidnight whenever TodayRainSoFar is less than RainSinceMidnight (ie at the rollover).

The problem you have is your rollover time is midnight and you can't therefore use this workaround. Maybe Raincounter Or perhaps calculate from the rain total from the dayfile?
User avatar
billy
Posts: 255
Joined: Mon 30 Nov 2015 10:54 am
Weather Station: WLL / Davis VP2+
Operating System: RPi bullseye
Location: Gooseberry Hill, Western Australia

Re: Highcharts Graphs

Post by billy »

The it guy wrote: Sun 09 May 2021 3:49 pm well your having more luck then me then

as it plotting to the chart like this

[[0,-28.6],[1,0],[2,0],[3,0],[4,0],[5,0],[6,0],[7,2.9],[8,45.7],[9,21.4],[10,15.7],[11,21.4],[12,25.7],[13,21.4],[14,0],[15,30],[16,27.1],[17,0],[18,0],[19,0],[20,0],[21,0],[22,0],[23,0]]

Capture22222222.PNG
HrsSunShine also has the reset at rollover problem but is easier to solve than rainfall (unless you are inside the Arctic/Antarctic circles) with
IF(TIME(LogDateTime) = "00:00:00", 0, diffHrsSunShine) AS diffsun,
The it guy
Posts: 29
Joined: Fri 22 Feb 2019 12:46 am
Weather Station: Davis
Operating System: Windows 10

Re: Highcharts Graphs

Post by The it guy »

Thank u Billy for explaining the problems but like I say before I have a very lack of knowledge of SQL and database and queries so I think yeah I think if the rollover is 9 anf my rollovers is midnight so that means obviously I cant use this script because obviously I've got a lack of knowledge and I don't expect other people to do the work for me to just this script to work with my setup to thank you
Post Reply