cloudy cloudy

Author Topic: SQL fix for 00:00 daily rain bug in 2003 log files  (Read 1567 times)

0 Members and 1 Guest are viewing this topic.

Offline ian

  • Posts: 210
  • Cary, NC
SQL fix for 00:00 daily rain bug in 2003 log files
« on: October 30, 2003, 01:39:52 PM »
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.
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
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
UPDATE `table1`  
`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

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!

WMR-968, WinXP SP2 RC2, Intel Celeron 800, 512MB Ram, MySQL
MDK 9.1, Intel Celeron 466, 256MB Ram, MySQL