Annotate Custom SQL Function (similar to date_trun

Question

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 to generate a query as follows:

SELECT
  time_bucket('1 minute', time) AS tb,
  AVG(s0) 
FROM measurements
WHERE
  time >= to_timestamp(1) AND
  time <= to_timestamp(2)
GROUP BY tb
ORDER BY tb ASC;

models.py:

class Measurement(models.Model):

    device_id = models.IntegerField(primary_key=True)
    time = models.DateTimeField()
    s0 = models.FloatField(blank=True, null=True)
    s1 = models.FloatField(blank=True, null=True)

---

My try so far:

class TimeBucket(Func):

    function = 'time_bucket'
    template = '%(function)s(\'{bucket_width}\', %(expressions)s)'.format(bucket_width='1 minute')


(Measurement.objects
    .values('time')
    .annotate(tb=TimeBucket('time'))
    .annotate(s_desc=Avg('s0'))
    .filter(
        time__gte=datetime.fromtimestamp(start),
        time__lte=datetime.fromtimestamp(end))
    .order_by('tb')
)

Results in:

SELECT
  "measurements"."time",
  time_bucket('1 minute', "measurements"."time") AS "tb",
  (AVG("measurements"."s0")) AS "s_desc"
FROM "measurements"
WHERE (
  "measurements"."time" <= 2447-10-02 14:17:01+00:00 AND 
  "measurements"."time" >= 1970-01-01 00:00:01+00:00
)
GROUP BY "measurements"."time", time_bucket('1 minute', "measurements"."time")
ORDER BY "tb" ASC

As you see there are two ugly points left:

• How could I use the alias tb in the GROUP BY instead of repeating it?
• I only need to query time_bucket and s0. How to get rid of time without breaking the query?

Answer 1

The truncated value 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')
)

---

In Django 1.11, it is possible to use a more DRY solution and combine the lines

.annotate(tb=TimeBucket('time'))
.values('tb')

to

.values(tb=TimeBucket('time'))