Some Old Data Is Incorrect

[Grimers]

Hello,

I was just looking at my data and found that some of the hourly rainfall data from 2011, 2012 and 2013 is wrong. I don't have a backup of this data from that time period and would I have to use Weather Underground to correct this?

Kind regards,

William

[Grimers]

Ok, so I've found some information out.

I've found that before 18/12/2013 there was no high hourly rain figure recorded by Cumulus 1 (when I had it installed), I see that build 1086 was released on 16/12/2013 and I would have shortly after installed it myself, is this when the high hourly rainfall figure was added to the dayfile for Cumulus 1 even though the post doesn't say so? The only high hourly rainfall I've managed to retrieve is through the alltimelog.txt which had the June 2012 figure as that was the highest all time hourly rain before a severe thunderstorm in September 2014.

Is there any way to retrieve the hourly rainfall data from before the 18/12/2013?

Thanks!

[rogerthn]

...
Is there any way to retrieve the hourly rainfall data from before the 18/12/2013?

Thanks!

Assuming that you have all your monthly files imported into MySQL

Code: Select all

select LogDateTime,TodayRainSoFar from Monthly where LogDateTime < '2013-12-18';

I would then get this into e.g. excel and do some calculations

[PaulMy]

is this when the high hourly rainfall figure was added to the dayfile for Cumulus 1

Cumulus records the day's Maximum rainfall rate and Time of max rainfall rate but does not record that in hourly in dayfile.txt. The maximums and date/time are recorded in the All time records.

The All time records editor shows what data is available with this utility.

The monthly log file has the high rain rate logged so the information is stored at the time of the log entry. By some query of your monthly log files you might be able to get that information.

Paul

[mcrossley]

This MySQL query should give the highest hourly rainfall for each day (I think!), but it doesn't give the correct time, I think you will need to JOIN the Monthly table in again to get the time (help Kerry!)...

Code: Select all

SELECT x.LogDateTime, Max(x.hrlyRain)
FROM (
	SELECT a.LogDateTime, a.Raincounter - b.RainCounter as hrlyRain
	FROM Monthly a, Monthly b
	WHERE a.LogDateTime < '2013-12-18'
	AND b.LogDateTime = Date_Sub(a.LogDateTime, Interval 1 HOUR)
) AS x
GROUP BY Date_Format(x.LogDateTime, '%Y%m%d')

[tobyspond]

Mark,

change the group by to group by hour(x.logdatetime)


SELECT x.LogDateTime, Max(x.hrlyRain)
FROM (
SELECT a.LogDateTime, a.Raincounter - b.RainCounter as hrlyRain
FROM Monthly a, Monthly b
WHERE b.LogDateTime = Date_Sub(a.LogDateTime, Interval 1 HOUR)
) AS x
GROUP BY hour(x.logdatetime )

Kerry

[mcrossley]

Hi Kerry, that doesn't quite work, it gives 24 rows, one for each hour rather than one for each day.

[tobyspond]

Hi Mark,

Try this:

SELECT year(x.LogDateTime) as year, month(x.LogDateTime) as month, day(x.LogDateTime) as day, q.hour, x.hrlyRain
FROM (
SELECT a.LogDateTime, max(a.Raincounter - b.RainCounter) as hrlyRain
FROM Monthly a, Monthly b
WHERE a.raincounter-b.raincounter>0
AND b.LogDateTime = Date_Sub(a.LogDateTime, Interval 1 HOUR) GROUP BY Date_Format(a.LogDateTime, '%Y%m%d')
) AS x


join (
SELECT hour(t.LogDateTime) as hour, t.hrlyRain
FROM (
SELECT c.LogDateTime, max(c.Raincounter - d.RainCounter) as hrlyRain
FROM Monthly c, Monthly d
WHERE c.raincounter-d.raincounter>0 and
d.LogDateTime = Date_Sub(c.LogDateTime, Interval 1 HOUR)group by day(c.logdatetime), hour(c.logdatetime)
) AS t)q
on x.hrlyrain=q.hrlyrain group by day(x.logdatetime)


It may need some tweaking

Kerry

[mcrossley]

That works great, I added an "order by year, month, day".

And the usual problem with these type of queries: no row is returned if there isn't rain on a particular day. But fixing that would involve another join I think!

Anyway, it works. Thanks very much - now filed away in my list of useful queries