Days it hasn't rained - for all years

[AndyH]

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.

[mcrossley]

This sort of thing?

Code: Select all

SELECT LogDate, TotRainFall
FROM `daydata`
WHERE Month(LogDate) = 9
  AND DAY(LogDate) = 1
  AND TotRainFall = 0

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) = Month(Now())
  AND DAY(LogDate) = DAY(Now())
  AND TotRainFall = 0

[AndyH]

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

[mcrossley]

That would seem to a much easier task?

Code: Select all

SELECT YEAR(LogDate) AS 'year', LogDate
FROM dayfile
WHERE TotRainFall = 0

[AndyH]

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?

[tobyspond]

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

[rogerthn]

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;

[mcrossley]

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

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.

[AndyH]

SELECT month(logdate) as month, day(logdate) as day, year(logdate) as year, totrainfall
FROM dayfile
WHERE TotRainFall = 0 order by month, day, year

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.

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?

[tobyspond]

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

[AndyH]

This returned no rows so I amended the code slightly to the following:

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

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.

[beteljuice]

... and 4 times if after today's date ...

[AndyH]

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

[PaulMy]

Looks very nice, well done

however there appears to be an issue with the 'gauges'

Paul

[tobyspond]

looks good. I'm glad it worked out for you.