4

I am storing an object in my DB with a timefield like so:

class MyClass(models.Model):

    start_time = models.TimeField(null=True, blank=True)
    stop_time = models.TimeField(null=True, blank=True)

The idea here is that when querying an endpoint, the server will return only objects where the current time is between the start_time and stop_time.

NB: start_time and stop_time are arbitrary times of the day, and can span across midnight, but will never be more than 24hr apart.

I have tried

currentTime = datetime.now().time()
MyClass.objects.filter(stop_time__gte=currentTime, start_time__lte=currentTime)

but this does not account for when the times span midnight.

I'm sure there must be a simple solution to this, but web search has left me fruitless. Does anyone know a good way to do this?

avgrammer
  • 409
  • 5
  • 13

2 Answers2

6

After some more digging, I found that this requires two queries: One for when the start time is less than the stop time (the common case), and one for when it is greater than the stop time (the uncommon, after-midnight case).

Here is the code:

currentTime = datetime.now().time()

#Returns a list of menus that have start times less than their stop times
list_1 = MyClass.objects.filter(Q(start_time__lte=F('stop_time')), Q(start_time__lte=currentTime), stop_time__gte=currentTime)

#Returns the menus that have start times greater than their stop times (span midnight)
list_2 = MyClass.objects.filter(Q(start_time__gt=F('stop_time')), Q(start_time__lte=currentTime) | Q(stop_time__gte=currentTime))

concat_list = list_1 | list_2
concat_list = concat_list.order_by('-priority')

Since we are using "|" to concatenate the lists, we can keep the same features as the original list, such as "order_by()". This is only the case if the data being concatenated is from the same model set.

References:

Django After Midnight Business Hours TimeField Comparison Error

How to combine 2 or more querysets in a Django view?

Community
  • 1
  • 1
avgrammer
  • 409
  • 5
  • 13
  • 2
    Just a clarification `MyClass.objects.filter(...)` does not return a list, it returns a queryset and your are performing **OR** operation on those querysets, which returns a new queryset. [order_by()](https://docs.djangoproject.com/en/1.10/ref/models/querysets/#order-by) is a method of Querysets. – Tiny Instance Oct 03 '16 at 05:35
  • These are two pretty hard queries really. If you look at it you have multiple OR conditions and unions all of which are pretty hard to optimize on low end databases like Mysql or sqlite. But without knowing why you can't set a date for the values, I cannot change my answer either – e4c5 Oct 03 '16 at 06:28
  • 1
    @e4c5 - I may be understanding this wrong, but my understanding is that by using specific date values, I am limiting myself to the days attached to those times, correct? The reason I am using time values alone is that they persist day-after-day, and I don't want to have to update the date values in order to keep the system functioning. Is that clearer? – avgrammer Oct 04 '16 at 07:55
  • not really but since you have stuck on something that seems to work for you, i think you can go with that :-) – e4c5 Oct 04 '16 at 08:29
0

Just for reference, I post this here as well (I answered this in the other linked question as well previously). You need to use something like this in your filter() clause, to handle the different comparisons needed for both the midnight crossing and non-midnight crossing cases. Replace daily_starts/ends_at with your start/stop time and now_time with your currentTime.

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

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

BjornW
  • 188
  • 1
  • 1
  • 8