Support Resolving F Expression in Custom DB Function

[Charlie McBride]

I am trying to write a custom postgresql function that will coerce datetimes to a specified timezone inside of a queryset. In failing to do so, I noticed that it is not currently possible to resolve kwargs that are F expressions in a custom Func. My first pass at the db function looks like this:

from django.db.models.expressions import Func

class DateTimeInTimezone(Func):
    template="%(expressions)s AT TIME ZONE %(tz_info)s"

This function works in the simple case where I pass a timezone string into the function directly like so:

MyModel.objects.filter(timefield__gte=DateTimeInTimezone(Now(), tz_info='EST'))

However it doesn't work in the more complex case, where the timezone is defined on some field on the model. Consider the following contrived example:

class User(models.Model):
    time_zone = models.CharField()

class Meeting(models.Model):
    users = models.ManyToManyField(User)
    start_time = models.DateTimeField() # in UTC
    end_time = models.DateTimeField() # in UTC

To answer the question "What users will be in a meeting at 12pm local time today?", I'd need some variation of this queryset:

noon_utc = ...
User.objects.filter(
    meetings__start_time__lte=DateTimeInTimezone(noon_utc, tz_info=F('time_zone')),
    meetings__end_time__gt=DateTimeInTimezone(noon_utc, tz_info=F('time_zone'))
)

As currently written, however, DateTimeInTimezone will simply inject the string F('time_zone') into the sql rather than resolve the expression to the row value.

In the source for Func it's evident that the expressions are checked for resolve_expression attributes and resolved if possible, while any extra attributes (i.e. kwargs) are not. Is it possible to add support for evaluating extra in Func in the same manner as expressions to support dynamic values via F expressions? In Django 1.10 the code in question is in django/db/models/expressions.py:472 - 551.

[Josh Smeaton]

I'm closing this as invalid because there's no actual bug. You should post to the #django-users mailing list or the #django IRC channel for support/user questions like this.

That said - we don't always want to treat extra kwargs as expressions themselves. Extra is used for passing around extra context that you might want in a query, but arguments that are expressions *must* be processed as expressions. That means adding them to self.set_source_expressions(), and resolving them in resolve_expressions.

What I think you want to do is make your __init__ method wrap any string argument to tz_info in Value, and add it into your set_source_expressions call. That way, if someone passes an F object, it'll be resolved as an expression anyway. So don't pass tz_info around via extra - treat it as an expression from the very start. It's exactly the reason we have the Value expression in the first place. I hope that helps. If not, please continue this topic on the django-users mailing list.