Welcome to the Cumulus Support forum.
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 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
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 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
MySQL query assistance
Moderator: mcrossley
-
- Posts: 272
- Joined: Tue 28 May 2013 1:06 am
- Weather Station: Davis VP2 Plus
- Operating System: Raspian Buster (RPi 3b)
- Location: Tulsa, OK
MySQL query assistance
I'm pretty good at sussing out how I need to do things in MySQL query wise, but this one has me stumped.
I'm trying to find a way to query the Dayfile table to retrieve the following:
Number of consecutive records that match a specific criteria (in this instance, I'm trying to match temperatures equaling 100F or greater), and then group those records descending. Example, I have a run of 15 days of consecutive of greater than or equal to 100 degrees. There's another run of 13 under that, and so on.
I want to return three elements in this query: 1. Number of consecutive records 2. Start date 3. End date.
Can any of you get me pointed in the right direction? I tried searching for this in the forum and was not able to find a solution or anything to even get me started.
Thanks to all of you.
I'm trying to find a way to query the Dayfile table to retrieve the following:
Number of consecutive records that match a specific criteria (in this instance, I'm trying to match temperatures equaling 100F or greater), and then group those records descending. Example, I have a run of 15 days of consecutive of greater than or equal to 100 degrees. There's another run of 13 under that, and so on.
I want to return three elements in this query: 1. Number of consecutive records 2. Start date 3. End date.
Can any of you get me pointed in the right direction? I tried searching for this in the forum and was not able to find a solution or anything to even get me started.
Thanks to all of you.
-
- Posts: 2434
- Joined: Wed 08 Jun 2011 11:19 am
- Weather Station: Davis Vantage Pro 2 + Ecowitt
- Operating System: GNU/Linux Ubuntu 22.04 LXC
- Location: Alcaston, Shropshire, UK
- Contact:
Re: MySQL query assistance
Sounds like a good candidate for a stored procedure. You could do it in a query but it would be complex and tricky to get right, and even trickier to read
- HansR
- Posts: 5870
- Joined: Sat 20 Oct 2012 6:53 am
- Weather Station: GW1100 (WS80/WH40)
- Operating System: Raspberry OS/Bullseye
- Location: Wagenborgen (NL)
- Contact:
Re: MySQL query assistance
Doesn't this belong in another thread?
Hans
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
-
- Posts: 272
- Joined: Tue 28 May 2013 1:06 am
- Weather Station: Davis VP2 Plus
- Operating System: Raspian Buster (RPi 3b)
- Location: Tulsa, OK
Re: MySQL query assistance
I found a pretty complex query to modify to my needs that does what I need it to do. Doesn't provide me both Start and end date but this will suffice for now. All it does is count the number of consecutive days my station has recorded over 100 degree days, sort the number DESC and display them.
Just in case someone was needing something like this.
Code: Select all
WITH partitioned
AS (SELECT *,
Datediff(logdate, '1970-01-01') - ROW_NUMBER()
OVER (
ORDER BY LogDate ASC) AS
PartID
FROM Dayfile
WHERE MaxTemp >= 100)
SELECT Min(LogDate) AS StartDate,
Count(*) AS DayCount
FROM partitioned
GROUP BY PartID
HAVING Count(*) > 1
ORDER BY DayCount DESC
- HansR
- Posts: 5870
- Joined: Sat 20 Oct 2012 6:53 am
- Weather Station: GW1100 (WS80/WH40)
- Operating System: Raspberry OS/Bullseye
- Location: Wagenborgen (NL)
- Contact:
Re: MySQL query assistance
I should have said another forum iso thread.
And the because is that MySql programming is not a CMX issue.
I did suggest a programming around CMX forum once but as that does no exist, General would do I suppose.
Hans
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
- 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: MySQL query assistance
ROW_NUMBER requires MySQL V8. The attached - from Mark Crossley's site - is more cumbersome but works for earlier versions.
You do not have the required permissions to view the files attached to this post.
-
- Posts: 272
- Joined: Tue 28 May 2013 1:06 am
- Weather Station: Davis VP2 Plus
- Operating System: Raspian Buster (RPi 3b)
- Location: Tulsa, OK
Re: MySQL query assistance
Interesting. I'm running MariaDB 10.3.29, which, according to the authors of that version, is equivalent to MySQL 5.7. They even state any of their versions are not compatible with MySQL v8.
- dazza1223
- Posts: 860
- Joined: Sun 25 Jan 2015 8:41 pm
- Weather Station: Davis Vantage Pro 2 plus
- Operating System: Raspberry pi 4 (4gb)
- Location: Worthing
- Contact:
Re: MySQL query assistance
hey is there query for lighting data as i using the Custom upload where im uploading lighting data but as the days go bye without any lighting it all 0 0 0
so it there away to pick out when the lighting strike it but it in a different table for the chart
so it there away to pick out when the lighting strike it but it in a different table for the chart
-
- Posts: 272
- Joined: Tue 28 May 2013 1:06 am
- Weather Station: Davis VP2 Plus
- Operating System: Raspian Buster (RPi 3b)
- Location: Tulsa, OK
Re: MySQL query assistance
I don't have any kind of lightning detector on my system hence no from me. I don't even see any lightning columns in either the Dayfile or Monthly table. That doesn't mean that they aren't created if your setup uses lightning but I don't know.
- philpugh
- Posts: 381
- Joined: Tue 24 May 2011 8:34 am
- Weather Station: See Signature
- Operating System: Debian 11 (RPi4)
- Location: Antrobus, Cheshire, UK
- Contact:
Re: MySQL query assistance
I have an Ecowitt WH57 lightning detector 'attached' to one of my systems. I also have other extra sensors (CO2 and other internal temp sensors.) I created a table to log these separately.
I use the Custom Upload - Minutes in the MySQL settings to log these sensors every 5 mins.
I also log the daily high/low values for the internal temps (2 sensors), CO2 and the daily lightning strikes. You could do this with the MySQL Custom rollover function but I do additional processing to produce .json files of the lightning and temps for my own use via Python.
I use the Custom Upload - Minutes in the MySQL settings to log these sensors every 5 mins.
I also log the daily high/low values for the internal temps (2 sensors), CO2 and the daily lightning strikes. You could do this with the MySQL Custom rollover function but I do additional processing to produce .json files of the lightning and temps for my own use via Python.
Phil Pugh
GW1000 + WH65/WH57/WH31; HP2551 + WS68/WH40A; GW2001 WittBoy
cumulusutils generating website
http://goosegate.uk/
GW1000 + WH65/WH57/WH31; HP2551 + WS68/WH40A; GW2001 WittBoy
cumulusutils generating website
http://goosegate.uk/
- dazza1223
- Posts: 860
- Joined: Sun 25 Jan 2015 8:41 pm
- Weather Station: Davis Vantage Pro 2 plus
- Operating System: Raspberry pi 4 (4gb)
- Location: Worthing
- Contact:
Re: MySQL query assistance
I'm looking post trigger statement where it will pick out the lightning data and dump into another table so I'll get lightning charts from the sql?
- philpugh
- Posts: 381
- Joined: Tue 24 May 2011 8:34 am
- Weather Station: See Signature
- Operating System: Debian 11 (RPi4)
- Location: Antrobus, Cheshire, UK
- Contact:
Re: MySQL query assistance
CMX doesn't log any lightning information AFAIK which is why I use the Custom SQL to log all my extra sensors into an SQL table.
From there you can do anything you like with the data.
The command I use is :
INSERT INTO database.table (`ET1`, `EH1`, `ET2`, `LightStrikes`, `A25`, `A25A`, `A10`, `A10A`, `AT`, `AH`, `CO2`, `CO2A`) VALUES ('<#ExtraTemp1>', '<#ExtraHum1>', '<#ExtraTemp2>', '<#LightningStrikesToday>', '<#CO2-pm2p5>', '<#CO2-pm2p5-24h>', '<#CO2-pm10>', '<#CO2-pm10-24h>', '<#CO2-temp>', '<#CO2-hum>', '<#CO2>', '<#CO2-24h>');
Where database.table is a 'new' table purely for extra sensors.
I then use a bit of Python (run after midnight) to add entries into a daily extrasensors table along with Max/Min values and also produce a lightning.json file with the last 31 days values - this I use to graph the lightning data.
From there you can do anything you like with the data.
The command I use is :
INSERT INTO database.table (`ET1`, `EH1`, `ET2`, `LightStrikes`, `A25`, `A25A`, `A10`, `A10A`, `AT`, `AH`, `CO2`, `CO2A`) VALUES ('<#ExtraTemp1>', '<#ExtraHum1>', '<#ExtraTemp2>', '<#LightningStrikesToday>', '<#CO2-pm2p5>', '<#CO2-pm2p5-24h>', '<#CO2-pm10>', '<#CO2-pm10-24h>', '<#CO2-temp>', '<#CO2-hum>', '<#CO2>', '<#CO2-24h>');
Where database.table is a 'new' table purely for extra sensors.
I then use a bit of Python (run after midnight) to add entries into a daily extrasensors table along with Max/Min values and also produce a lightning.json file with the last 31 days values - this I use to graph the lightning data.
Phil Pugh
GW1000 + WH65/WH57/WH31; HP2551 + WS68/WH40A; GW2001 WittBoy
cumulusutils generating website
http://goosegate.uk/
GW1000 + WH65/WH57/WH31; HP2551 + WS68/WH40A; GW2001 WittBoy
cumulusutils generating website
http://goosegate.uk/
-
- Posts: 1774
- Joined: Sat 17 Dec 2011 11:55 am
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 11 x64
- Location: Dorset - UK
- Contact:
Re: MySQL query assistance
I just log the daily lightning strike total by using the custom rollover SQL function.