Question 247041 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Compare created_at to an hours_old integer in JsonField in Django

I’m trying to create a django query that compares my models created_at date to see if they are at least x hours old where x is defined in a nested JsonField hours_old.

So for example:

# This creates a timedelta ("Duration" natively, I think)
hours_past = ExpressionWrapper(
    timezone.now() - F('created_at'),
    output_field=DateTimeField()
)

Person.objects.annotate(
    hours_old=F('settings__json__data__hours_old') # Let's say this is an int of 3
    hours_past=hours_past # Let's say this is a timedelta of 2 days
).filter(
    hours_old__lt=F('hours_past') # I want 3 < 48
)

But I can’t compare a time duration to a random int obviously. What’s the play here?

Using django==3.2.5

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Got it! Thanks to Zegarek and lxop for the inspiration!

        delta_since_created = ExpressionWrapper(
        timezone.now() - F('created_at'),
        output_field=DateTimeField()
    )
    
    hours_delta = ExpressionWrapper(
        Cast(F('settings__json__data__hours_old'), IntegerField()) * timedelta(hours=1),
        output_field=DateTimeField()
    )
    
    people = Person.objects.annotate(
        hours_delta=hours_delta
    ).filter(
        hours_delta__lt=delta_since_created
    )

    Seems casting timedelta to DateTimeField creates a DurationField. The json was being turned into an int after the query ran, but not during. Comparisons and math were failing until I cast it to an IntegerField manually.


  2. 0

    If created_at is a timestamp, hours_past results in a timedelta/interval and its output_field should be a DurationField() instead:

    hours_past = ExpressionWrapper(
    timestamp.now() - F('created_at'), #10am-9am=1hour, not 1am
    output_field=DurationField()       #timedelta/interval
)

    For your filter, you can convert one:

    Person.objects.annotate(
    hours_old=F('settings__json__data__hours_old') #3 hours
    hours_past=hours_past                          #2 days
).filter(
    hours_old__lt=hours_past/timedelta(hours=1)
)

    Or the other

    Person.objects.annotate(
    hours_old=F('settings__json__data__hours_old') #3 hours
    hours_past=hours_past                          #2 days
).filter(
    hours_past__gte=timedelta(hours=hours_old)
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search