Trends are always shown in UTC

[danielterhorst]

Trend data is shown in UTC while it would be nice to show it in the locale timezone or make it a setting.

Right now I'm seeing data from the low tariff starting from 22:00 (10 pm) in the interface while in practice, the low tariff starts at 23:00 (11 pm).

[dennissiemensma]

Thanks, I did not notice it yet myself. Most views use localtime() to display data, but the trends page uses a small custom annotation, extended by: date_part('hour', hour_start). And since there are no dates, but only hours, it lacks any localtime capabilities (which would be a quick fix).

I think I'll have to specify timezone manually in the query, as this will hit multiple DST transitions in a year (since it does a group by over all readings, both in summer and winter, at some point).

[dennissiemensma]

It seems I can only fix this in PostgreSQL, as it's the only database backend to support timezone conversion in sessions.
Django calls connection.ops.set_time_zone_sql(), which is empty for all other backends.

I see no other solutions, as I would have to iterate over every hour and localize it according to the DST of that very moment. Over three months I already have 2000+ hours logged, so I think that is not an option.
Aligning the hours in bulk won't work either, as they will be mixed up at some point due to UTC + 1 and UTC + 2.

[dennissiemensma]

Fixed & pushed to master. I will update the README as well, to advise postgresql over mysq, despite that mysql is more common.

[dennissiemensma]

My fix caused another bug, which only happens on Gunicorn due to code cache, as the database connection get reused.

I didn't revert the timezone back to UTC after changing it and executing the query I need. Django then fails:
AssertionError:database connection isn't set to UTC

Only the webinterface process is affected when using postgres and opening the trends page.

I will fix it by resetting timezone in db session to UTC.