select data in a time interval in minutes django orm

Question

How are you? I need some help please. I am working in Django and I need to make a query of all sales between 00:00 and 00:30 minutes, so every 30 minutes I need to see the sales. I was putting together something like this but it is not working.

sales = (
    SalesOrder
    .objects
    .annotate(Sum('subtotal'))
    .filter(created_at__time__range= ('00:00:00:00', ''))
    .filter(created_at__time = hour_now)
)

This is a SQL query that delivers the correct data.

SELECT sum(subtotal) as tot 
  FROM sales_salesorder ss 
 where extract(minute from created_at::time) between 31 and 59 and
       created_at::date = now()::date and 
       extract(hour from created_at) = extract(hour from CURRENT_TIME);

The problem is that I can't leave the sql query because I have to pass it through a for loop to return the data, that's why the most effective way is to use the ORM, that's the reason of my question.

Could you clarify what you mean by "leave the sql query"? Are you iterating (looping) the objects in the list, have found an object that you want, and no longer wish to iterate over the query? — Swift, Jan 17 '22 at 15:02
Hello do you try those previous solutions : https://stackoverflow.com/questions/35430237/specify-time-interval-in-django-timestampedmodel-while-querying https://stackoverflow.com/questions/4668619/how-do-i-filter-query-objects-by-date-range-in-django — LSeu, Jan 17 '22 at 15:03

dani herrera · Answer 1 · 2022-01-17T15:29:15.593

The direct translation from your sql to django query is:

from django.utils import timezone

sales = (
    SalesOrder
    .objects
    .filter(created_at__minute__range=(31, 59))
    .filter(created_at__hour=timezone.now().hour)
    .filter(created_at__date=timezone.now().today())
    .count()
)

But, perhaps, a more elegant and index friendly approach is:

from django.utils import timezone
from datetime import datetime, timedelta

today = datetime.now().today()

date_from = timezone.datetime(
    today.year, today.month, today.day,
    0, 0, 0,
    tzinfo=timezone.get_current_timezone())

date_to = date_from + timedelta(minutes=30)

sales = (
    SalesOrder
    .objects
    .filter(created_at__range=(date_from, date_to))
    .count()
)

If you are looking to group by each half hour here a good answer: https://stackoverflow.com/a/61747028/842935

select data in a time interval in minutes django orm

1 Answers1