Querying Multiple Business hour ranges in Django that overlap midnight

Question

I'm trying to deal with businesses such as bars that have fairly unusual hours such as;

Saturday: 9am - 3pm then reopen 10pm - 4am #multiple open/closures

Sunday: 11am - 8pm

Monday: closed

Tuesday-Friday 9am - 9pm #would be each be a row in the DB for each weekday

I am also storing special events at the bar which also have availability time ranges like above where they can be available to customers.

Mobile apps will be requesting an array of the bar objects in JSON with the opening hours nested and specials nested inside the bar object. Each special subsequently nests the hours the special is available. I'm using Django-Rest.

I'm kind of concerned about the space complexity and time complexity of the query process when generating the output for large sets. Also putting 23:59:59 everywhere seems wrong when there might be a way to simplify it down to DateTime Ranges.

import datetime
from django.db import models
WEEKDAYS = [
    (1, "Monday"),
    (2, "Tuesday"),
    (3, "Wednesday"),
    (4, "Thursday"),
    (5, "Friday"),
    (6, "Saturday"),
    (7, "Sunday"),
]

class Bar(models.Model):
    name = models.CharField(max_length=200)
    location = models.CharField(max_length=200, blank=True)
            

class Special(models.Model):
    bar = models.ForeignKey(Bar, related_name="availablespecials")
    name = models.CharField(max_length=500)
    price = models.FloatField()

"""The bar's businesses hours"""    
class OpeningHour(models.Model):
    bar = models.ForeignKey(Bar, related_name="baropeninghours")
    weekday = models.IntegerField(choices=WEEKDAYS)
    from_hour = models.TimeField()
    to_hour = models.TimeField()

"""The ranges for when a special is available"""
class SpecialHour(models.Model):
    special = models.ForeignKey(Special, related_name="specialopeninghours")
    weekday = models.IntegerField(choices=WEEKDAYS)
    from_hour = models.TimeField()
    to_hour = models.TimeField()

Output of getting bars

"results": [
        {
            "name": "Santa's Brew House", 
            "Address": "123 Ho Ho Ho Street", 
            "gps_latitude":  90.00000, 
            "gps_longitude": 0.00000, 
            "baropeninghours": [
                #This would mean santas place is open from 10pm to 4am
                "Saturday 22:00:00 to 23:59:59"
                "Sunday 00:00:00 to 04:00:00", 
            ], 
            "availablespecials": [
                {
                    "name": "Captain's Mast", 
                    "specialopeninghours": [
                        "Monday 00:00:00 to 23:59:59", 
                        "Tuesday 00:00:00 to 23:59:59", 
                        "Wednesday 00:00:00 to 23:59:59", 
                        "Thursday 00:00:00 to 23:59:59", 
                        "Friday 00:00:00 to 23:59:59", 
                        "Saturday 00:00:00 to 23:59:59", 
                        "Sunday 00:00:00 to 23:59:59"
                    ]
                }, 
                {
                    "name": "Aggie Punch", 
                    "specialopeninghours": [
                        "Saturday 00:00:00 to 23:59:59", 
                        "Sunday 00:00:00 to 23:59:59"
                    ]
                }
            ]
        }]... continued for each bar

Answer 1

If a bar is open on Monday morning till 3 a.m. I wouldn't consider it "Open" on that day, but rather that is the closing time on Sunday. So the time is too little info here.

    from_hour = models.TimeField()
    to_day = models.IntegerField(choices=WEEKDAYS)
    to_hour = models.TimeField()

Your client code can figure out how to render this best (maybe ignore it if to_day and weekday are the same).

Your JSON:

...
        "baropeninghours": [
            "Saturday 22:00:00 to Sunday 3:00:00"
...

or

...
        "baropeninghours": [
            "Tuesday 10:00:00 to Tuesday 22:00:00"
...