Luc, Gary good info, great insight. Thanks for that... It all makes sense
and wasn't something I had considered previously.
I'm on holiday today, but Luc if you want to submit a pull request that
would be great. Otherwise I can merge this myself after some testing
tomorrow.
On Mon, Feb 18, 2019, 11:37 AM Luc Heijst <ljm.hei...@gmail.com wrote:
> On Sunday, 17 February 2019 15:48:47 UTC-3, Pat wrote:
>>
>> I think I'll leave it alone for now unless you have insight on that?
>>
>
> Hi Pat,
>
> I have a working solution.
>
> In Belchertown.py
> Change:
> # Find what kind of database we're working with and specify the
> correctly tailored SQL Query for each type of database
> dbtype =
> self.generator.config_dict['DataBindings']['wx_binding']['database']
> if dbtype == "archive_sqlite":
> year_rainiest_month_sql = 'SELECT strftime("%%m",
> datetime(dateTime, "unixepoch")) as month, ROUND( SUM( sum ), 2 ) as total
> FROM archive_day_rain WHERE strftime("%%Y", datetime(dateTime,
> "unixepoch")) = "%s" GROUP BY month ORDER BY total DESC LIMIT 1;' %
> time.strftime( "%Y", time.localtime( time.time() ) )
> at_rainiest_month_sql = 'SELECT strftime("%m",
> datetime(dateTime, "unixepoch")) as month, strftime("%Y",
> datetime(dateTime, "unixepoch")) as year, ROUND( SUM( sum ), 2 ) as total
> FROM archive_day_rain GROUP BY month, year ORDER BY total DESC LIMIT 1;'
> year_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM
> archive_day_rain WHERE strftime("%%Y", datetime(dateTime, "unixepoch")) =
> "%s";' % time.strftime( "%Y", time.localtime( time.time() ) )
> at_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM
> archive_day_rain;'
> # The all stats from http://www.weewx.com/docs/customizing.htm
> doesn't seem to calculate "Total Rainfall for" all time stat correctly.
> at_rain_highest_year_sql = 'SELECT strftime("%Y",
> datetime(dateTime, "unixepoch")) as year, ROUND( SUM( sum ), 2 ) as total
> FROM archive_day_rain GROUP BY year ORDER BY total DESC LIMIT 1;'
> elif dbtype == "archive_mysql":
> year_rainiest_month_sql = 'SELECT FROM_UNIXTIME( dateTime,
> "%%m" ) AS month, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain
> WHERE year( FROM_UNIXTIME( dateTime ) ) = "{0}" GROUP BY month ORDER BY
> total DESC LIMIT 1;'.format( time.strftime( "%Y", time.localtime(
> time.time() ) ) ) # Why does this one require .format() but the other's
> don't?
> at_rainiest_month_sql = 'SELECT FROM_UNIXTIME( dateTime, "%%m"
> ) AS month, FROM_UNIXTIME( dateTime, "%%Y" ) AS year, ROUND( SUM( sum ), 2
> ) AS total FROM archive_day_rain GROUP BY month, year ORDER BY total DESC
> LIMIT 1;'
> year_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM
> archive_day_rain WHERE year( FROM_UNIXTIME( dateTime ) ) = "%s";' %
> time.strftime( "%Y", time.localtime( time.time() ) )
> at_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM
> archive_day_rain;'
> # The all stats from http://www.weewx.com/docs/customizing.htm
> doesn't seem to calculate "Total Rainfall for" all time stat correctly.
> at_rain_highest_year_sql = 'SELECT FROM_UNIXTIME( dateTime,
> "%%Y" ) AS year, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain
> GROUP BY year ORDER BY total DESC LIMIT 1;'
>
> into:
> # Find what kind of database we're working with and specify the
> correctly tailored SQL Query for each type of database
> dataBinding =
> self.generator.config_dict['StdArchive']['data_binding']
> database =
> self.generator.config_dict['DataBindings'][dataBinding]['database']
> databaseType =
> self.generator.config_dict['Databases'][database]['database_type']
> driver =
> self.generator.config_dict['DatabaseTypes'][databaseType]['driver']
> if driver == "weedb.sqlite":
> year_rainiest_month_sql = 'SELECT strftime("%%m",
> datetime(dateTime, "unixepoch")) as month, ROUND( SUM( sum ), 2 ) as total
> FROM archive_day_rain WHERE strftime("%%Y", datetime(dateTime,
> "unixepoch")) = "%s" GROUP BY month ORDER BY total DESC LIMIT 1;' %
> time.strftime( "%Y", time.localtime( time.time() ) )
> at_rainiest_month_sql = 'SELECT strftime("%m",
> datetime(dateTime, "unixepoch")) as month, strftime("%Y",
> datetime(dateTime, "unixepoch")) as year, ROUND( SUM( sum ), 2 ) as total
> FROM archive_day_rain GROUP BY month, year ORDER BY total DESC LIMIT 1;'
> year_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM
> archive_day_rain WHERE strftime("%%Y", datetime(dateTime, "unixepoch")) =
> "%s";' % time.strftime( "%Y", time.localtime( time.time() ) )
> at_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM
> archive_day_rain;'
> # The all stats from http://www.weewx.com/docs/customizing.htm
> doesn't seem to calculate "Total Rainfall for" all time stat correctly.
> at_rain_highest_year_sql = 'SELECT strftime("%Y",
> datetime(dateTime, "unixepoch")) as year, ROUND( SUM( sum ), 2 ) as total
> FROM archive_day_rain GROUP BY year ORDER BY total DESC LIMIT 1;'
> elif driver == "weedb.mysql":
> year_rainiest_month_sql = 'SELECT FROM_UNIXTIME( dateTime,
> "%%m" ) AS month, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain
> WHERE year( FROM_UNIXTIME( dateTime ) ) = "{0}" GROUP BY month ORDER BY
> total DESC LIMIT 1;'.format( time.strftime( "%Y", time.localtime(
> time.time() ) ) ) # Why does this one require .format() but the other's
> don't?
> at_rainiest_month_sql = 'SELECT FROM_UNIXTIME( dateTime, "%%m"
> ) AS month, FROM_UNIXTIME( dateTime, "%%Y" ) AS year, ROUND( SUM( sum ), 2
> ) AS total FROM archive_day_rain GROUP BY month, year ORDER BY total DESC
> LIMIT 1;'
> year_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM
> archive_day_rain WHERE year( FROM_UNIXTIME( dateTime ) ) = "%s";' %
> time.strftime( "%Y", time.localtime( time.time() ) )
> at_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM
> archive_day_rain;'
> # The all stats from http://www.weewx.com/docs/customizing.htm
> doesn't seem to calculate "Total Rainfall for" all time stat correctly.
> at_rain_highest_year_sql = 'SELECT FROM_UNIXTIME( dateTime,
> "%%Y" ) AS year, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain
> GROUP BY year ORDER BY total DESC LIMIT 1;'
>
> For my situation the log info was:
> Feb 18 11:18:49 pi21 wee_reports[20273]: Belchertown Extension:
> dataBinding vpro_binding
> Feb 18 11:18:49 pi21 wee_reports[20273]: Belchertown Extension: database
> vpro_mysql
> Feb 18 11:18:49 pi21 wee_reports[20273]: Belchertown Extension:
> databaseType MySQL
> Feb 18 11:18:49 pi21 wee_reports[20273]: Belchertown Extension: driver
> weedb.mysql
>
> With this modification the Belchertown reports finished without errors.
>
> Cheers, Luc
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "weewx-user" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/weewx-user/NNvvqe5ni2E/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> weewx-user+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
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.
