Django After Midnight Business Hours TimeField Comparison Error

Question

I have a Business Hours that I need to compare and I'm getting incomplete results if the business hours are past midnight

My Model

class Hours(models.Model):
  dayofweek = models.ForeignKey('Dayofweek')
  opentime = models.TimeField(blank=True, null=True)
  closetime = models.TimeField(blank=True, null=True)
  ...

If I just need to display hours everything works OK such: Saturday 5pm - 2am

Now when I'm trying to query the hours to check if the business is even open, those with past midnight hours will return False for exists():

my query

if Hours.objects.filter(
            business__id=id,
            dayofweek__pyday=dt, 
            opentime__lte=mytime, 
            closetime__gte=mytime).exists():
    #do something

Any suggestions how to tell Django that the 2am is after 5pm?

Why not do 2 queries... (5pm -11:59 pm) || (12am - 2am)? It sounds like the api isn't set up to do what you want? My guess is you can daisy chain 2 filters... Hours.objects.filter().filter().exists() — Dr.Knowitall, Jun 05 '15 at 22:29
Well, how would that work if the business hours were just 5pm-10pm? For example the app is checking if the 5pm-10pm business is open at 9pm? — WayBehind, Jun 05 '15 at 22:40
just a note: you have the same problem if the current time is 2am: the closing time would match, but not the opening time. — Pynchia, Jun 05 '15 at 23:02
Somewhat off topic, why did you decide to set dayofweek to a ForeignKey. Instead of e.g. IntegerField with choices = ((1, 'Monday'), (2, 'Tuesday'),.. — Maxim, Apr 09 '16 at 03:58
Maxim, the main reason for that is that when I have it in DB, I can have additional fields for each day. For example, in addition to regular name `Monday`, I also have abbreviated version of `Mon` and I do also store Python days where Sunday is `0` so I dont have to run any conversions. — WayBehind, Apr 09 '16 at 13:12

score 2 · Answer 1 · answered Jun 05 '15 at 22:51

A day can have multiple periods, but no matter what, the day ends at 11:59 PM. If that extends into the next day, you have to break up your time intervals. So the logic of your filters will be like so...

# Spans over 2 days
if opentime > closetime:
  Hours.objects.filter(
    business_id=id,
    dayofweek_pyday=dt,
    opentime_tye=myOpenTime,
    closetime_gte=11:59:99
  ).exists() ||
  Hours.objects.filter(
    business_id=id,
    # Next date
    dayofweek_pyday=dt + 1,
    opentime_tye=00:00:00,
    closetime_gte=myCloseTime
  ).exists()
# Spans over 1 day
else:
   Hours.objects.filter(
   business__id=id,
   dayofweek__pyday=dt, 
   opentime__lte=myOpentime, 
   closetime__gte=myClosetime).exists()

I don't know django so this is just some pseudo code and an alternative approach I would use.

Thanks for the feedback. This approach would certainly work. I guess the only downside is that I have to first run another query just to get the opening and closing hours so I can compare those. — WayBehind, Jun 05 '15 at 22:58
You have to just look at the api docs to see if it does what you want, but this is my hack around to get through that issue. If anything don't use Hours.objects ... if you can do absolute UTC time, that would make more sense, and you can query the way you want. Check out the api docs. I think you're actually using the wrong query service. — Dr.Knowitall, Jun 05 '15 at 23:02

Pynchia · Answer 2 · 2015-06-06T00:09:01.930

2

What about using F and Q expressions through two queries:

one for the simple case (opening time <= closing time)

Hours.objects.filter(
         opentime__lte=F('closetime'),
         business__id=id,
         dayofweek__pyday=dt, 
         opentime__lte=mytime, 
         closetime__gte=mytime).exists():

and one for the odd case (closing time < opening time)

Hours.objects.filter(
         opentime__gt=F('closetime'),
         business__id=id,
         dayofweek__pyday=dt, 
         Q(opentime__lte=mytime) | Q(closetime__gte=mytime)).exists():

edited Jun 06 '15 at 00:09

answered Jun 05 '15 at 23:51

Pynchia

10,996
5
34
43

Thanks for the alternative, I will definitely try this solution as well! Your help is appreciated! – WayBehind Jun 06 '15 at 00:03
1

I am having a similar problem, and this is exactly how I was thinking of solving it. – Maxim Apr 09 '16 at 03:56
This code doesn't work as the keyword opentime__lte is repeated twice in the filter clause. – BjornW Feb 04 '20 at 18:13

score 0 · Answer 3 · answered Feb 04 '20 at 21:14

(Q(daily_starts_at__lte=F('daily_ends_at')) &
 Q(daily_starts_at__lte=now_time, daily_ends_at__gte=now_time)) |   
(Q(daily_starts_at__gt=F('daily_ends_at')) &
 (Q(daily_starts_at__lte=now_time) | Q(daily_ends_at__gte=now_time))

This is how I solved the same case, use this in your filter() clause (and replace my daily_starts/ends_at with your names and now_time with now().time() or something). The logic is like in the other answers here, the comparison has to be different depending on if the range crosses midnight.

You also need to & with a Q() clause containing your other filter parameters.

Django After Midnight Business Hours TimeField Comparison Error

3 Answers3

Linked