def average_consumption_by_hour(max_weeks_ago):
""" Calculates the average consumption by hour. Measured over all consumption data of the past X months. """
sql_extra = {
# Ugly engine check, but still beter than iterating over a hundred thousand items in code.
'postgresql': "date_part('hour', hour_start)",
'sqlite': "strftime('%H', hour_start)",
'mysql': "extract(hour from hour_start)",
}[connection.vendor]
# Only PostgreSQL supports this builtin.
set_time_zone_sql = connection.ops.set_time_zone_sql()
if set_time_zone_sql:
connection.connection.cursor().execute(set_time_zone_sql, [settings.TIME_ZONE]) # pragma: no cover
hour_statistics = HourStatistics.objects.filter(
# This greatly helps reducing the queryset size, but also makes it more relevant.
hour_start__gt=timezone.now() - timezone.timedelta(weeks=max_weeks_ago)
).extra({
'hour_start': sql_extra
}).values('hour_start').order_by('hour_start').annotate(
avg_electricity1=Avg('electricity1'),
avg_electricity2=Avg('electricity2'),
avg_electricity1_returned=Avg('electricity1_returned'),
avg_electricity2_returned=Avg('electricity2_returned'),
avg_electricity_merged=Avg(models.F('electricity1') + models.F('electricity2')),
avg_electricity_returned_merged=Avg(models.F('electricity1_returned') + models.F('electricity2_returned')),
avg_gas=Avg('gas'),
)
# Force evaluation, as we want to reset timezone in cursor below.
hour_statistics = list(hour_statistics)
if set_time_zone_sql:
# Prevents "database connection isn't set to UTC" error.
connection.connection.cursor().execute(set_time_zone_sql, ['UTC']) # pragma: no cover
return hour_statistics
评论列表
文章目录