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 4019) - 03 April 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 V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 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
Days it hasn't rained - for all years
Moderator: daj
-
- Posts: 52
- Joined: Thu 16 Sep 2010 8:34 am
- Weather Station: La Crosse 2350
- Operating System: Windows 8
- Location: Reigate, Surrey, UK
- Contact:
Days it hasn't rained - for all years
Hi all,
Back again for some help and guidance (hopefully)
I am trying to work out how to return dates from a MySQL database which holds the dayfile on which (for example) it hasn't rained.
i.e I have records going back to 2009 and I know that I have never recorded rainfall on 1st September for all years (2009, 2010, 2011, 2012 and 2013) but how do I get this from MySQL and all the other dates that no rainfall has been recorded?
I have tried GROUP BY on the date after removing the year but this just returns the first date in each year that it hasn't rained.
Hope this makes sense.
Andy.
Back again for some help and guidance (hopefully)
I am trying to work out how to return dates from a MySQL database which holds the dayfile on which (for example) it hasn't rained.
i.e I have records going back to 2009 and I know that I have never recorded rainfall on 1st September for all years (2009, 2010, 2011, 2012 and 2013) but how do I get this from MySQL and all the other dates that no rainfall has been recorded?
I have tried GROUP BY on the date after removing the year but this just returns the first date in each year that it hasn't rained.
Hope this makes sense.
Andy.
- mcrossley
- Posts: 12767
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Days it hasn't rained - for all years
This sort of thing?
Though of course you don't need to return the TotRainFall
Or more generally, for 'today in history'...
Code: Select all
SELECT LogDate, TotRainFall
FROM `daydata`
WHERE Month(LogDate) = 9
AND DAY(LogDate) = 1
AND TotRainFall = 0
Or more generally, for 'today in history'...
Code: Select all
SELECT LogDate, TotRainFall
FROM `daydata`
WHERE Month(LogDate) = Month(Now())
AND DAY(LogDate) = DAY(Now())
AND TotRainFall = 0
-
- Posts: 52
- Joined: Thu 16 Sep 2010 8:34 am
- Weather Station: La Crosse 2350
- Operating System: Windows 8
- Location: Reigate, Surrey, UK
- Contact:
Re: Days it hasn't rained - for all years
Thanks Mark - that looks to be on the right line. However, I am trying to create a page on my site that would list all the days throughout the year that have had no rain fall (perhaps trying to run before I could walk) and was trying to go through the whole dayfile and pick out every day that this event had occurred.
This would certainly appear to work for a specific date (I am away from my computer at the moment).
This would certainly appear to work for a specific date (I am away from my computer at the moment).
- mcrossley
- Posts: 12767
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Days it hasn't rained - for all years
That would seem to a much easier task?
Code: Select all
SELECT YEAR(LogDate) AS 'year', LogDate
FROM dayfile
WHERE TotRainFall = 0
-
- Posts: 52
- Joined: Thu 16 Sep 2010 8:34 am
- Weather Station: La Crosse 2350
- Operating System: Windows 8
- Location: Reigate, Surrey, UK
- Contact:
Re: Days it hasn't rained - for all years
Sorry, I think I may have confused the issue with my last post.
What I am trying to do list those days where I have not recorded any rainfall since my records began (2009).
So, for example, if it did not rain on 1st September 2009, 2010, 2011, 2012 and 2013 then show that date.
However, if it had rained on 1st September 2012 (for example) then it would not show that date.
I need to do this for every date from 1st January to 31st December and check that day for all the years to see whether there was any rainfall, if there was then don't display, if there wasn't then display the date.
Hopefully that's clearer.
I could write your code for every day of the year but that seems a lot of code. Do I need to loop round somehow to check all the days?
What I am trying to do list those days where I have not recorded any rainfall since my records began (2009).
So, for example, if it did not rain on 1st September 2009, 2010, 2011, 2012 and 2013 then show that date.
However, if it had rained on 1st September 2012 (for example) then it would not show that date.
I need to do this for every date from 1st January to 31st December and check that day for all the years to see whether there was any rainfall, if there was then don't display, if there wasn't then display the date.
Hopefully that's clearer.
I could write your code for every day of the year but that seems a lot of code. Do I need to loop round somehow to check all the days?
-
- Posts: 252
- Joined: Fri 24 Jun 2011 5:57 pm
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 10
- Location: Lamoine, Maine, USA
Re: Days it hasn't rained - for all years
If I understand you correctly then this may do it for you:
SELECT month(logdate) as month, day(logdate) as day, year(logdate) as year, totrainfall
FROM dayfile
WHERE TotRainFall = 0 order by month, day, year
Kerry
SELECT month(logdate) as month, day(logdate) as day, year(logdate) as year, totrainfall
FROM dayfile
WHERE TotRainFall = 0 order by month, day, year
Kerry
- rogerthn
- Posts: 518
- Joined: Thu 11 Apr 2013 6:31 pm
- Weather Station: Ecowitt GW1000/GW1003
- Operating System: Raspberry Pi OS bullseye aarch64
- Location: Trollhättan Sweden
- Contact:
Re: Days it hasn't rained - for all years
Guessing without knowing exactly what you have in your database, if this doesn't do the trick some sample data will help.
SELECT DISTINCT logdate
FROM dayfile
WHERE TotRainFall = 0
ORDER BY logdate;
SELECT DISTINCT logdate
FROM dayfile
WHERE TotRainFall = 0
ORDER BY logdate;
- mcrossley
- Posts: 12767
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: Days it hasn't rained - for all years
I think that is as good as it will get. The question I didn't ask was how the days was going to be decoded to create a page.tobyspond wrote:If I understand you correctly then this may do it for you:
SELECT month(logdate) as month, day(logdate) as day, year(logdate) as year, totrainfall
FROM dayfile
WHERE TotRainFall = 0 order by month, day, year
Kerry
-
- Posts: 52
- Joined: Thu 16 Sep 2010 8:34 am
- Weather Station: La Crosse 2350
- Operating System: Windows 8
- Location: Reigate, Surrey, UK
- Contact:
Re: Days it hasn't rained - for all years
Thank you Kerry, this is the closest to the final result I am looking for. This gives me all the days, since my records began, that it has not rained and orders them by month, day, year.tobyspond wrote: SELECT month(logdate) as month, day(logdate) as day, year(logdate) as year, totrainfall
FROM dayfile
WHERE TotRainFall = 0 order by month, day, year
However, what I am looking for is to only show the month, day where it has never rained on that day since my records began.
I'm still not sure I have explained properly what I am trying to achieve so will give it another go.
I have a records page on my website and want to add a facts one that displays items such as:
"Did you know it has never rained on the 1st September"
"Did you know it has always got above 20ºC on 4th August"
"Did you know it has always fallen below 0ºC on 5th February"
In order to do this I need to get the rows from the dayfile mySQL database that relate and then use PHP to display this on my website.
So I need to return the day, month IF on this day, month it has not rained in 2009, 2010, 2011, 2012 and 2013. And then do this for every day of the year that this has occurred.
Is that any clearer?
-
- Posts: 252
- Joined: Fri 24 Jun 2011 5:57 pm
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 10
- Location: Lamoine, Maine, USA
Re: Days it hasn't rained - for all years
Try this
SELECT month(logdate) as month, day(logdate) as day, totrainfall, count(totrainfall) as ct
FROM dayfile
WHERE TotRainFall = 0 group by month, day
having ct=(select count(year) from (select year(logdate) as year from dayfile
group by year) as t)
order by month, day
SELECT month(logdate) as month, day(logdate) as day, totrainfall, count(totrainfall) as ct
FROM dayfile
WHERE TotRainFall = 0 group by month, day
having ct=(select count(year) from (select year(logdate) as year from dayfile
group by year) as t)
order by month, day
-
- Posts: 52
- Joined: Thu 16 Sep 2010 8:34 am
- Weather Station: La Crosse 2350
- Operating System: Windows 8
- Location: Reigate, Surrey, UK
- Contact:
Re: Days it hasn't rained - for all years
This returned no rows so I amended the code slightly to the following:
and this gave me those dates on which no rainfall had been recorded 5 times (if before today's date) and 4 times if after today's date which matches what I need (as my records go back to 2009). Therefore I can now play around with this data in PHP to display the correct dates - thank you very much.
Code: Select all
SELECT month(logdate) as month, day(logdate) as day, totrainfall, count(totrainfall) as ct
FROM dayfile
WHERE TotRainFall = 0 group by month, day
order by ct DESC, month, day
- beteljuice
- Posts: 3292
- Joined: Tue 09 Dec 2008 1:37 pm
- Weather Station: None !
- Operating System: W10 - Threadripper 16core, etc
- Location: Dudley, West Midlands, UK
Re: Days it hasn't rained - for all years
... and 4 times if after today's date ...
......................Imagine, what you will KNOW tomorrow !
-
- Posts: 52
- Joined: Thu 16 Sep 2010 8:34 am
- Weather Station: La Crosse 2350
- Operating System: Windows 8
- Location: Reigate, Surrey, UK
- Contact:
Re: Days it hasn't rained - for all years
Just for your information - I got a bit of time today so started on the page I was trying to create. More data to be added by in the mean time this is the data I was trying to display. Thanks all for your help.
http://www.sompt.com/weather/history/daysithas.php
http://www.sompt.com/weather/history/daysithas.php
- PaulMy
- Posts: 3849
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Re: Days it hasn't rained - for all years
Looks very nice, well done
however there appears to be an issue with the 'gauges'
Paul
however there appears to be an issue with the 'gauges'
Paul
VP2+
C1 www.komokaweather.com/komokaweather-ca
MX https://komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX https://komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX https:// komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX https://komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX https://komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX https:// komokaweather.com/cumulusmx4/index.htm
-
- Posts: 252
- Joined: Fri 24 Jun 2011 5:57 pm
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 10
- Location: Lamoine, Maine, USA
Re: Days it hasn't rained - for all years
looks good. I'm glad it worked out for you.