Annotate Custom SQL Function (similar To Date_trunc) To Django ORM Queryset

I am using timescaledb which is basically just an extension for postgres. It comes with a SQL function called time_bucket. I want to use this function in combination with the ORM t

Solution 1:

The un-truncated column time can be used only in filter before the first annotate(...) and never more.

The truncated time annotation must be used in .values() (e.g. .values('tb')) before any aggregation function.

qs = (
    Measurement.objects
    .filter(...)
    .annotate(tb=TimeBucket('time'))
    .values('tb')
    .annotate(s_desc=Avg('s0'))
    .order_by('tb')
)

About alias in GROUP BY: The alias can be generally used only in ORDER BY in all databases, but only specific database allow it in GROUP BY or WHERE (only MySQL and Postgresql). This is probably because the GROUP BY and WHERE clauses are evaluated before the SELECT clause. This can be bypassed by a subquery, but not useful at all. I'm sure that the standard query plan optimizer of every modern database driver reuses the auxiliary expressions from GROUP BY and it never evaluates the function repeatedly. The alias is useful if the SQL is written manually and read by humans, but not useful to implement it in Django ORM compiler by alias only for some backends.

---

EDIT: This solution works in Django >= 1.11 and tested also in Django 3.0.

(There was an incorrect information about Django versions. I don't remember the solution in Django <=1.10, but I'm sure that it was more complicated than in Django 1.11. Maybe there had to be written an additional final .values('tb', 's_desc'))