Welcome to the Cumulus Support forum.
Latest Cumulus MX V4 release 4.0.0 (build 4022) - 11 May 2024
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Legacy Cumulus 1 release 1.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
Latest Cumulus MX V4 release 4.0.0 (build 4022) - 11 May 2024
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Legacy Cumulus 1 release 1.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
-
- Posts: 29
- Joined: Fri 22 Feb 2019 12:46 am
- Weather Station: Davis
- Operating System: Windows 10
Re: Highcharts Graphs
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
As I need the structure for
Sunbyhour
Sun by month hour
Daily insolation
-
- 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
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.
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.
Stewart
www.stewartwlewis.com
www.stewartwlewis.com
-
- Posts: 29
- Joined: Fri 22 Feb 2019 12:46 am
- Weather Station: Davis
- Operating System: Windows 10
Re: Highcharts Graphs
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
- 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
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).
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.
-
- Posts: 29
- Joined: Fri 22 Feb 2019 12:46 am
- Weather Station: Davis
- Operating System: Windows 10
Re: Highcharts Graphs
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
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
- 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
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.
-
- Posts: 29
- Joined: Fri 22 Feb 2019 12:46 am
- Weather Station: Davis
- Operating System: Windows 10
Re: Highcharts Graphs
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
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;
-
- Posts: 1830
- 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
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?!
The nagative rain value always appears at 23:59 for me. It doesn't cause any issues as far as I can see?!
-
- Posts: 29
- Joined: Fri 22 Feb 2019 12:46 am
- Weather Station: Davis
- Operating System: Windows 10
Re: Highcharts Graphs
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]]
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]]
You do not have the required permissions to view the files attached to this post.
-
- Posts: 29
- Joined: Fri 22 Feb 2019 12:46 am
- Weather Station: Davis
- Operating System: Windows 10
Re: Highcharts Graphs
sorry the top got cut off it the sunshine by hr
You do not have the required permissions to view the files attached to this post.
-
- Posts: 29
- Joined: Fri 22 Feb 2019 12:46 am
- Weather Station: Davis
- Operating System: Windows 10
Re: Highcharts Graphs
Or if you kindly share your insert statement and your triggers? Then maybe it will work more better ?
- 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
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");
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?
- 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
HrsSunShine also has the reset at rollover problem but is easier to solve than rainfall (unless you are inside the Arctic/Antarctic circles) withThe 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
IF(TIME(LogDateTime) = "00:00:00", 0, diffHrsSunShine) AS diffsun,
-
- Posts: 29
- Joined: Fri 22 Feb 2019 12:46 am
- Weather Station: Davis
- Operating System: Windows 10
Re: Highcharts Graphs
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