SQL fix for 00:00 daily rain bug in 2003 log files

Between December 2002 and August 2003 there was a bug in WD whereby the first entry in the logfiles following a rainy day would show the daily rain for the previous day e.g. if the was 20 mm of rain on 03/01/2003 and you open the logfile for March2003 and look at the first line for 03/02/2003 00:00, it would show a daily rain of 20mm too (but the monthly and yearly rain are correct).

I was using the imported data in mySQL, and when trying to do rain reports, I noticed a lot more rain than I expected. I made 2 SQL queries, the first shows all database entries containing this error, the second fixes them automatically.

Note I have a slightly different database table structure than Brian’s default, but you should be able to get the idea of what I am doing. I suggest running the SELECT query first, and verify that the returned records are faulty by comparing to your daily report files e.g. October2003.htm.

Find all bad records i.e. records where rain is reported at midnight.

[code:1]
SELECT Time/Date , Today rain , Month rain , Year rain , Today rain inch , Month rain inch , Year rain inch
FROM table1
WHERE Time/Date LIKE ‘200_-- 00:00:00’
AND Today rain > 0
AND Today rain inch > 0
LIMIT 0 , 1000
[/code:1]
Once you are satisfied that there is incorrect data in the database for the first record of each day following a rain day, you can fix them all at once with

[code:1]
UPDATE table1
SET
Today rain = 0,
Today rain inch = 0.0
WHERE 1 AND Time/Date LIKE ‘200_-- 00:00:00’
AND Today rain > 0
AND Today rain inch > 0
[/code:1]

Also note that WD log files will still have the bad data, this only fixes the SQL database. It does show the power of SQL!

Cheers,
Ian