CODEWITHSUNDEEP
djangopython-3.xpostgresqldatetime
Dave
Dave

Reputation: 19120

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

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')

Upvotes: 4

Views: 1116

Answers (1)

Iain Shelvington
Iain Shelvington

Reputation: 32244

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

Upvotes: 5

Related Questions