How do I write a Django query that does date math when executed as PostGres SQL?

Question

I'm using Django and Python 3.7. I'm writing a Django query to be run on a PostGres 9.4 db, but having trouble figuring out how to form my expression wrapper so that I add a number of seconds (an integer) to an existing date column. I tried the below

hour_filter = ExtractHour(ExpressionWrapper(
            F("article__created_on") + timedelta(0,
                                                 F("article__websitet__avg_time_in_seconds_to_reach_ep")),
            output_field=models.DateTimeField)
        ),
)

but I'm getting the error

unsupported type for timedelta seconds component: F

Any ideas how I can rewrite my ExpressionWrapper to do the date math inside the PostGres query?

Edit; Here are the models and relevant fields ...

class Website(models.Model):
    ...
    avg_time_in_seconds_to_reach_ep = models.FloatField(default=0, null=True)


class Article(models.Model):
    objects = ArticleManager()
    website = models.ForeignKey(Website, on_delete=models.CASCADE, related_name='articlesite')

Have you tried converting the value returned by the F(...) expression to int or float? Ex: ... + timedelta(0, int(F("article_websitet__avg_time_in_seconds_to_reach_ep")), ... — Sparrow1029
– Sparrow1029, Commented Aug 30, 2019 at 23:40
@Caleb, I edited my question to include the models and their relevant fields. — Dave
– Dave, Commented Aug 31, 2019 at 1:46

Iain Shelvington · Accepted Answer · 2019-08-31 02:07:57Z

5

You can add database functions to Django, for this you can add a function for the INTERVAL statement in postgres

class IntervalSeconds(Func):

    function = 'INTERVAL'
    template = "(%(expressions)s * %(function)s '1 seconds')"

You can then use this function in your queries to add seconds to a datetime

YourModel.objects.annotate(
    attr=ExpressionWrapper(
        F("article__created_on") + IntervalSeconds(F("article__websitet__avg_time_in_seconds_to_reach_ep")),
        output_field=models.DateTimeField()
    ),
)

The output of the IntervalSeconds function is a 1 second Postgres interval multiplied by the field passed to it. This can be added and subtracted from a timestamp. You could make a generic Interval function that doesn't just take seconds, this is a little more complex

The ExpressionWrapper is required to convert the result to a datetime object

edited Aug 31, 2019 at 2:07

answered Aug 31, 2019 at 1:21

Iain Shelvington

32.5k3 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dave Over a year ago

Thx, gave this a whirl. In my ExpressionWrapper I put 'F("article__created_on") + IntervalSeconds(F("article__website__avg_time_in_seconds_to_reach_ep")' but got the error, "invalid input syntax for type interval: ""articlesum_website"."avg_time_in_seconds_to_reach_ep" seconds" LINE 1: ...FROM ("articlesum_article"."created_on" + INTERVAL '"articlesu...'

Iain Shelvington Over a year ago

Updated the answer with a slightly better function

Collectives™ on Stack Overflow

How do I write a Django query that does date math when executed as PostGres SQL?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related