Annotate one part of a range to a new field

Question

So we've been using a DateTimeRangeField in a booking model to denote start and end. The rationale for this might not have been great —separate start and end fields might have been better in hindsight— but we're over a year into this now and there's no going back.

It's generally been fine except I need to annotate just the end datetime onto a related model's query. And I can't work out the syntax.

Here's a little toy example where I want a list of Employees with end of their last booking annotated on.

class Booking(models.Model):
    timeframe = DateTimeRangeField()
    employee = models.ForeignKey('Employee')

sq = Booking.objects.filter(employee=OuterRef('pk')).values('timeframe')
Employee.objects.annotate(last_on_site=Subquery(sq, output_field=DateTimeField()))

That doesn't work because the annotated value is the range, not the single value. I've tried a heap of modifiers (egs __1 .1 but nothing works).

Is there a way to get just the one value? I guess you could simulate this without the complication of the subquery just doing a simple values lookup. Booking.objects.values('timeframe__start') (or whatever). That's essentially what I'm trying to do here.

Not sure if it would be useful to you but you can access the lower/upper value of a `DateTimeTZRange` object through `Booking.objects.values_list('timeframe', flat=True)[1].upper` — bdoubleu, Jun 26 '18 at 15:55
Unfortunately you can't call max on a tstzrange otherwise something like this would work. If you can find another way to find the most recent booking then the timeframe can still be annotated and you could call upper after the fact. `booking = Booking.objects.filter(employee=OuterRef('pk')).order_by().values('employee')` `max_booking = booking.annotate(max_booking=Max('timeframe')).values('max_booking')` `employees = Employee.objects.annotate(max_booking=Subquery(max_booking))` — bdoubleu, Jun 26 '18 at 15:56

score 1 · Answer 1 · answered Jun 27 '18 at 12:38

Thanks to some help in IRC, it turns out you can use the RangeStartsWith and RangeEndsWith model transform classes directly. These are the things that are normally just registered to provide you with a __startswith filter access to range values, but directly they can pull back the value.

In my example, that means just modifying the annotation slightly:

from django.contrib.postgres.fields.ranges import RangeEndsWith
sq = Booking.objects.filter(employee=OuterRef('pk')).values('timeframe')
Employee.objects.annotate(last_on_site=RangeEndsWith(Subquery(sq[:1])))

Annotate one part of a range to a new field

1 Answers1