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

MySQL query assistance

From build 3044 the development baton passed to Mark Crossley. Mark has been responsible for all the Builds since. He has made the code available on GitHub. It is Mark's hope that others will join in this development, but at the very least he welcomes your ideas for future developments (see Cumulus MX Development suggestions).

Moderator: mcrossley

Post Reply
BigOkie
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

Post by BigOkie »

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.
freddie
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

Post by freddie »

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 :lol:
Freddie
Image
User avatar
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

Post by HansR »

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
BigOkie
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

Post by BigOkie »

HansR wrote: Wed 27 Jul 2022 8:31 pm Doesn't this belong in another thread?
And why?
BigOkie
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

Post by BigOkie »

HansR wrote: Wed 27 Jul 2022 8:31 pm Doesn't this belong in another thread?
And why? I've seen other requests for MySQL assistance in this forum...
BigOkie
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

Post by BigOkie »

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.

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
Just in case someone was needing something like this.
User avatar
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

Post by HansR »

BigOkie wrote: Wed 27 Jul 2022 10:13 pm
HansR wrote: Wed 27 Jul 2022 8:31 pm Doesn't this belong in another thread?
And why? I've seen other requests for MySQL assistance in this forum...
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
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: MySQL query assistance

Post by billy »

BigOkie wrote: Wed 27 Jul 2022 10:40 pm ROW_NUMBER()
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.
BigOkie
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

Post by BigOkie »

billy wrote: Thu 28 Jul 2022 5:29 am
BigOkie wrote: Wed 27 Jul 2022 10:40 pm ROW_NUMBER()
ROW_NUMBER requires MySQL V8. The attached - from Mark Crossley's site - is more cumbersome but works for earlier versions.
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.
User avatar
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

Post by dazza1223 »

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
Have fun and keep learning

dazza :D

https://www.davisworthing.co.uk

Image
BigOkie
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

Post by BigOkie »

dazza1223 wrote: Thu 28 Jul 2022 2:59 pm 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
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.
User avatar
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

Post by philpugh »

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.
Phil Pugh
GW1000 + WH65/WH57/WH31; HP2551 + WS68/WH40A; GW2001 WittBoy
cumulusutils generating website
http://goosegate.uk/
User avatar
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

Post by dazza1223 »

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?
Have fun and keep learning

dazza :D

https://www.davisworthing.co.uk

Image
User avatar
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

Post by philpugh »

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.
Phil Pugh
GW1000 + WH65/WH57/WH31; HP2551 + WS68/WH40A; GW2001 WittBoy
cumulusutils generating website
http://goosegate.uk/
Mapantz
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

Post by Mapantz »

I just log the daily lightning strike total by using the custom rollover SQL function.
Image
Post Reply