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 [email protected].
For more options, visit https://groups.google.com/d/optout.
# Author: Rich Altmaier
# Feb 24, 2018
# You may modify or use this functionality for personal use.
# Please give credit to me in your usage.
#
import time
import sqlite3
# first fine end of most recent storm.
# may be still ongoing (e.g. rain in most recent record).
# parameterized with:
storm_dry_period = 8 # number of hours of no rain to delimit
# storm end.
storm_in_past = 4 # 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
