Django queryset return DurationField value in seconds

user_12373322 0
Viewed 1165

I have two models: Post, Comment (Comment has FK relation to Post).

Now I want to return all posts with theirs "response time". I get this response time in timedelta format. Can I receive it in seconds instead? I tried ExtractSecond but it is not what I'm looking for:

base_posts_queryset.annotate(
    min_commnet_date=Min("comment_set__date_created"),
    response_time=ExpressionWrapper(F("min_commnet_date") - F("date_created"), output_field=DurationField()),
    response_time_in_sec=ExtractSecond(F("response_time"))
).filter(response_time__isnull=False).values("response_time", "response_time_in_sec")

This code returns following objects:

{'response_time': datetime.timedelta(days=11, seconds=74024, microseconds=920107), 'response_time_in_sec': 44}

What I want to achieve is basically call .seconds for each item in result queryset. I could do this in python, but mb it could be done on db level?

postgresql django-orm django python
2 Answers

Sure can, but the exact mechanism may depend upon your database.

In postgres, you can use EXTRACT(epoch FROM <interval>) to get the total number of seconds.

To use this in Django, you can create a Func subclass:

class Epoch(django.db.models.expressions.Func):
    template = 'EXTRACT(epoch FROM %(expressions)s)::INTEGER'
    output_field = models.IntegerField()

Then you can use it directly:

base_posts.annotate(
    response_time_sec=Epoch(F('min_comment_date') - F('date_created'))
)
user_1880

Nice solution!

One wrinkle is that I think there is a missing 's' needed to get this to work in Django 3

class Epoch(django.db.models.expressions.Func):
    template = 'EXTRACT(epoch FROM %(expressions)s)::INTEGER'
    output_field = models.IntegerField()
user_138212590
Related