UPDATE:
For those that may be interested. I was able to get this working! It was
not easy though due to limitation on how weewx stores current data and
problems with averaging across years when you do group_by. So to handle
that I did two things:
1. Created a custom service that adds weekly, monthly and yearly rain
totals to the weewx archive as separate tables. This makes averaging much
easier. The service will parse all archived data and then keep track of
future data on database changes.
2. Modified belchertown.pay to add the ability to run custom sql queries
in-line in graphs.conf. I added two more parameters
(use_custom_sql=boolean) and (custom_sql_query="string"). This allowed me,
in the instance of creating monthly rain averages across years to write a
custom sql query of
custom_sql_query = "SELECT month, AVG(avg_rain) AS avg_monthly_climo_rain
FROM rain_monthly_averages GROUP BY month ORDER BY month ASC"
that is placed in my graphs.conf table.
The graphs.conf would look like:
[averageclimate]
title = "Average Climate by Month"
show_button = true
button_text = "Average Climate"
type = spline
time_length = all
tooltip_date_format = "dddd LL"
aggregate_interval = 86400 # 1 day
gapsize = 86400 # 1 day in seconds
start_at_beginning_of_month = true
[[avgclimatetotal]]
title = "Average Climatological Values by Month"
type = spline
aggregate_interval = 86400 # 1 day
xAxis_groupby = month
xAxis_categories = 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul',
'Aug', 'Sep', 'Oct', 'Nov', 'Dec'
[[[outTemp]]]
aggregate_type = avg
average_type = max
zIndex = 2
name = Max Average Temperature
color = red
[[[[marker]]]]
enabled = true
radius = 4
[[[outTemp_min]]]
name = Min Average Temperature
observation_type = outTemp
aggregate_type = avg
average_type = min
zIndex = 2
[[[[marker]]]]
enabled = true
radius = 4
[[[dewpoint]]]
name = Average Dewpoint
aggregate_type = avg
zIndex = 2
color = purple
[[[[marker]]]]
enabled = true
radius = 4
[[[custom_average_rains]]]
name = Average Monthly Rain Total
use_custom_sql = true # This tells Belchertown to use a custom
SQL query
custom_sql_query = "SELECT month, AVG(avg_rain) AS
avg_monthly_climo_rain FROM rain_monthly_averages GROUP BY month ORDER BY
month ASC"
x_column = month # The column from your SQL query for the
X-axis (1-12)
y_column = avg_monthly_climo_rain # The column from your SQL
query for the Y-axis (the average total rain)
type = column # Display as a bar chart
yAxis = 1 # Use the secondary Y-axis, typically for rain
zIndex = 0
color = "#268bd2" # Use your preferred color for rain bars
And the output renders:
[image: Untitled-1.png]
Now it has the correct averages for each month instead of the SUM, which it
would do no matter how you asked it to create averages.
If anyone is interested in the code for the service or modifications to
belchertown.py, I am doing some cleanup, but can put those up on github. I
have not had the time to do a lot of testing, so I am sure there are edge
cases that might break things. I am not a Python coder, I muddle more in
javascript, and knew turbo pascal well back in high school (yeah I am
getting old). But I at least got the code to do what I wanted, while trying
to make this applicable for future custom queries. Oh the queries that are
placed in weewx.conf should also be able to be called this way as well. I
have not tried it yet, but it should work. I will try to test that as well
when I clean up some of the code.
Shane
On Monday, May 19, 2025 at 5:22:12 PM UTC-7 Shane Burkhardt wrote:
> I am trying to create a chart in the Belchertown skin that would display
> the average monthly climatological data such as a average high, average
> low, average dewpoint and average rainfall per month. This would average
> across my entire database so it would not be year specific.
>
> I have the averages for everything working correctly except the rainfall.
> No matter what I do, I am either only getting a per day average, or a sum
> of all rainfall (i.e. monthly rainfall in February for each year TOTAL
> instead of average. So in the graph below it is showing 13in when in fact
> that is the sum of rain in February over a 3 year period and not the
> average of the total monthly rainfall in each February).
>
> I have consulted the Belchertowwn skin wiki, but the definitions of
> aggregate_type, aggregate_interval, and average_type, are a bit unclear and
> use some circular definitions. I looked at some chart examples, but they
> also seem to sum the totals and not average them. I do not mean this as
> criticism as the skin really rocks!!!!! I know this skin uses Highcharts,
> but the Highcharts forums seem a foreign language to me.
>
> I have tried different combinations of interval, and aggregate/average
> tyIIpes but that does not seem to work.
>
> Here is the code from my graphs.conf:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *[averageclimate] title = "Average Climate by Month" show_button =
> true button_text = "Average Climate" type = spline time_length =
> all tooltip_date_format = "dddd LL" aggregate_interval = 86400 # 1
> day gapsize = 86400 # 1 day in seconds start_at_beginning_of_month =
> true [[avgclimatetotal]] title = "Average Climatological Values
> by Month" type = spline aggregate_interval = 86400 # 1 day
> xAxis_groupby = month xAxis_categories = 'Jan', 'Feb', 'Mar',
> 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'
> [[[outTemp]]] aggregate_type = avg average_type =
> max zIndex = 2 name = Max Average Temperature
> color = red [[[[marker]]]] enabled = true
> radius = 4 [[[outTemp_min]]] name = Min
> Average Temperature observation_type = outTemp
> aggregate_type = avg average_type = min zIndex = 2
> [[[[marker]]]] enabled = true radius
> = 4 [[[dewpoint]]] name = Average Dewpoint
> aggregate_type = avg zIndex = 2 color = purple
> [[[[marker]]]] enabled = true radius = 4
> [[[rain]]] name = Rain Total aggregate_interval
> = day aggregate_type = sum average_type = none
> type = column yAxis = 1 zIndex = 0
> color = "#268bd2"*
>
> Here is an example of the output I am getting:
> [image: climate_graph.png]
> I am screwing something up here, I just do not know what it is. Any help
> is appreciated!!!
>
>
>
--
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.
To view this discussion visit
https://groups.google.com/d/msgid/weewx-user/93dd080b-eeb1-40f1-bcee-67cb7378de57n%40googlegroups.com.
