How do I write a Django query and include a PostGres function (current_time) in my WHERE clause?

Question

I'm using Django, Python 3.7 and PostGres 9.5. I want to write the following WHERE clause in Django ...

WHERE date_part('hour', current_time) = s.hour ...

so in reading some other documentation, I'm led to believe I need to write a "Func" create an annotation before running my query ...

qset = ArticleStat.objects.annotate(
    hour_of_day=Func(
        'current_time',
        Value('hour'),
        function='date_part',
    )
).filter(hour_of_day=F("article__website__stats_per_hour__hour"))

However, this results in a

Cannot resolve keyword 'current_time' into field. Choices are: article, article_id, elapsed_time_in_seconds, id, score

error. It seems like Django is tryhing to treat "current_time" as a column from my table but I really want it to be treated as a PostGres function. How do I do that?

Answer 1

Update 2: Reading the filter clause you use the annotated hour_of_day for, simply turning the clause around would make it all a lot easier unless I'm overlooking something:

hour = datetime.datetime.now().hour
qset = ArticleStat.objects.filter(article__website__stats_per_hour__hour=hour)

---

Update: Even easier than the double annotation hack below is to get the current time in Python (once per query instead of once per row) and pass it to the function. You may need to make sure that the time zones match.

import datetime
from django.db.models import DateTimeField
from django.db.models.expressions import Func, Value

current_time = datetime.datetime.now()
qset = Session.objects.annotate(
    hour_of_day=Func(
        Value('hour'),
        Value(current_time, output_field=DateTimeField()),
        function='date_part',
    )
)

---

A simple hack would be to use two annotations to avoid nesting a database function in another (which you can probably do with a custom function subclassed from Func if you're serious enough about it):

from django.db.models import DateTimeField
from django.db.models.expressions import Func, Value

qset = MyModel.objects.annotate(
    current_time=Func(
        Value(0),
        function='current_time',
        output_field=DateTimeField()
    )).annotate(
    hour_of_day=Func(
        Value('hour'),
        F('current_time'),
        function='date_part',
    )
)