[weewx-user] Re: rainstorm total rainfall SQL and demonstration python script

['Rich Altmaier' via weewx-user]

I fixed the licensing statement to be GPL3, added the copyright notice, and 
I changed the storm dry period to 24 hours.
Thanks, Rich



On Saturday, February 24, 2018 at 4:09:26 PM UTC-8, Rich Altmaier wrote:
>
> I studied the weewx archive database and created this SQL, embedded into a 
> python script, to find the most recent rain storm and show the running 
> total rainfall of the storm.  
> You can see the script is parameterized to define a storm as delimited by 
> some number of hours of no rain, I selected 8.  
>
> My objective is to hope someone will extract useful portions for the 
> calculation of a tag to add to a report, enabling showing storm total 
> rainfall!
> If you do this, please do add to this post!!
> The script *does not modify* your database, creating only a temporary 
> table to make the query reasonably quick. 
>
> Thanks, Rich
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to weewx-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

#
# Copyright 2018 Rich Altmaier  richa...@yahoo.com
#
#
#   This program is free software: you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation, either version 3 of the License, or
#   (at your option) any later version.
#
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.
#
#   You should have received a copy of the GNU General Public License
#   along with this program.  If not, see <http://www.gnu.org/licenses/>.
#

import time
import sqlite3


# first find end of most recent storm.
#   may be still ongoing (e.g. rain in most recent record).
#   parameterized with:  
storm_dry_period = 24   # number of hours of no rain to delimit
                       # storm end.
storm_in_past = 7      # number of days to look back for a storm
storm_now = time.time()   # right now this second
                       # can choose other epoch value for testing!



# set now to a testing time, to look at various past rain records
#StructTime  = time.strptime('2017-02-05 16:00:00-PST', '%Y-%m-%d %H:%M:%S-%Z')
#storm_now = int(time.mktime(StructTime))

print "now is ", storm_now
print "  ", time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(storm_now))
 
# PRESUMABLY this DB connectivity follows normal weewx methods. 
conn = sqlite3.connect('weewx.sdb')
c = conn.cursor()

c.execute("drop table IF EXISTS RainWeek")
c.execute("""
    create TEMPORARY table RainWeek as 
    select dateTime, rain, 
           datetime(dateTime, 'unixepoch', 'localtime') as dateString
       from archive where dateTime >= :now - (24 * 3600 * :past )
       AND dateTime <=  :now
       ORDER BY dateTime
   """, {"now": storm_now, "past": storm_in_past})
conn.commit()

#c.execute("select count(*) from temp.RainWeek")
#print "number of rows in storm viewing table is ", int(c.fetchone()[0])

#dump RainWeek table
#c.execute("select rowid, rain, datetime(dateTime, 'unixepoch', 'localtime') as LocalTZ, dateTime from temp.RainWeek")
#StormTable = c.fetchall()
#print StormTable



c.execute("""
   -- find end of first wet period ending at or before storm_now
   -- or storm_now if has been wet within storm_dry_period hours of now. 
   select  CASE 
    WHEN 
     ((select MAX(dateTime) as RainEndT  from temp.RainWeek where rain <> 0 ) 
      + (:dry * 3600))
     >  
      :now  
    THEN
      :now  -- storm is still on-going

    ELSE -- find most recent last wet time
    (select MAX(dateTime) from temp.RainWeek where rain <> 0 OR rowid = 
      (select rowid from 
      (select MAX(dateTime) as LastWetT, rowid from temp.RainWeek where rain <> 0 )
      ) + 1  )
       --above is record after latest wet interval, or last wet interval
       --note: DB dateTime stamp is of beginning of interval

    END  as RainEndTime

""", {"now": storm_now, "past": storm_in_past, "dry": storm_dry_period})
 
EndT = c.fetchone()
if EndT is None:
    print "No storm in past", storm_in_past, " days"
    storm_end_epoch = storm_now + storm_in_past*24*3600  # next query will also get null
else:
    storm_end_epoch = int(EndT[0])
    print "End time epoch ", storm_end_epoch
    print "  ", time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(storm_end_epoch))



c.execute("""
   -- find dry period preceeding storm_end_epoch.
   -- look at storm_dry_period hours of records preceeding each record to find 
   --  a preceeding sequence of 0 rain.

   select MAX(dateTime) as RainStartT from  
     (select f1.rowid AS ROWID, sum(f2.rain) as RunningRain, f1.dateTime
       FROM temp.RainWeek f1  INNER JOIN temp.RainWeek f2
       ON f1.dateTime > f2.dateTime AND f2.dateTime >= (f1.dateTime - ( :dry *3600))
       GROUP BY ROWID
       HAVING  RunningRain = 0 and f1.dateTime < :ending   -- Rain end time
     )
""", {"ending": storm_end_epoch, "dry": storm_dry_period})

row = c.fetchone()
if row is not None:
    StartTepoch = int(row[0])
    print "Storm start time epoch ", StartTepoch
    print "  ", time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(StartTepoch))



c.execute("""
    select sum(rain) from temp.RainWeek
    WHERE dateTime >= :firstrain AND dateTime < :afterrain 
""", {"firstrain": StartTepoch, "afterrain": storm_end_epoch})
row2 = c.fetchone()
if row2 is None:
   stormTotalrain = 0.0
else:
   stormTotalrain = float(row2[0])

print "Storm total rain ", stormTotalrain