Django: How to calculate the uptime and downtime of a shop in the last hour, day, and week based on its business hours and status updates?

Question

I have a Shop model that represents a ecommerce store. Each shop has ShopHour objects that define its business hours for each day of the week. The ShopHour model has the following fields:

class ShopHour(models.Model):
    DAY_CHOICES = [
        (0, 'Monday'),
        (1, 'Tuesday'),
        (2, 'Wednesday'),
        (3, 'Thursday'),
        (4, 'Friday'),
        (5, 'Saturday'),
        (6, 'Sunday'),
    ]

    store = models.ForeignKey(
        Shop,
        on_delete=models.CASCADE,
        related_name='business_hours'
    )
    day_of_week = models.IntegerField(choices=DAY_CHOICES)
    start_time_local = models.TimeField()
    end_time_local = models.TimeField()

I also have a Status model that records the status of each shop at a given time. The Status model has the following fields:

class Status(models.Model):
    STATUS_CHOICES = [
        ('active', 'Active'),
        ('inactive', 'Inactive'),
    ]

    shop = models.ForeignKey(
        Shop,
        on_delete=models.CASCADE,
        related_name='updates'
    )
    timestamp_utc = models.DateTimeField()
    status = models.CharField(max_length=10, choices=STATUS_CHOICES)

I would like to create a hourly report Report for each Shop that shows its uptime and downtime in the last hour, day, and week, based on its business hours and status updates. Additionally if a shop has missing business hours, it's assumed to be open for 24*7. Specifically, I would like to know:

• How much time the shop was active and inactive in the last hour, day, and week
• How can I calculate this information efficiently and accurately? I would appreciate any suggestions or guidance on how to approach this problem. Thank you in advance!

Since reports are supposely sent to shop owners, that means I need to convert those timestamps back to their timezones so on and so forth. Here's a service that I came up with after spending 2 days browsing docs and stuffs, This creates report for shop based on it's shop_id. However I am unsure of this service because uptime and downtime are new terms for me. Please help!

def create_report(shop_id):
    shop = Shop.objects.get(id=shop_id)
    business_hours = shop.business_hours.all()

    if not business_hours:
        business_hours = {
            "start_time_local": datetime.min.time(),
            "end_time_local": datetime.max.time()
        }

    store_tz = timezone(shop.timezone_str)
    now = datetime.now(tz=store_tz)
    last_hour = now - timedelta(hours=1)
    last_day = now - timedelta(days=1)
    last_week = now - timedelta(days=7)

    # calculate uptime and downtime in minutes for last hour
    last_hour_statuses = Status.objects.filter(shop=shop, timestamp_utc__gte=last_hour)
    uptime_last_hour = downtime_last_hour = 0
    for status in last_hour_statuses:
        if status.status == 'active':
            uptime_last_hour += (now - max(status.timestamp_utc, last_hour)).seconds // 60
        else:
            downtime_last_hour += (now - max(status.timestamp_utc, last_hour)).seconds // 60

    # calculate uptime and downtime in hours for last day
    last_day_statuses = Status.objects.filter(shop=shop, timestamp_utc__gte=last_day)
    uptime_last_day = downtime_last_day = 0
    for status in last_day_statuses:
        if status.status == 'active':
            uptime_last_day += (now - max(status.timestamp_utc, last_day)).seconds // 3600
        else:
            downtime_last_day += (now - max(status.timestamp_utc, last_day)).seconds // 3600

    # calculate uptime and downtime in hours for last week
    last_week_statuses = Status.objects.filter(shop=shop, timestamp_utc__gte=last_week)
    uptime_last_week = downtime_last_week = 0
    for status in last_week_statuses:
        if status.status == 'active':
            uptime_last_week += (now - max(status.timestamp_utc, last_week)).seconds // 3600
        else:
            downtime_last_week += (now - max(status.timestamp_utc, last_week)).seconds // 3600

    # convert open and close time to store's timezone
    start_time_local = datetime.combine(datetime.today(), business_hours['start_time_local'])
    end_time_local = datetime.combine(datetime.today(), business_hours['end_time_local'])
    start_time_local = store_tz.localize(start_time_local)
    end_time_local = store_tz.localize(end_time_local)

    # calculate business hours for last hour
    business_hours_last_hour = (end_time_local - max(start_time_local, last_hour)).seconds // 60

    # create a report object and return
    report = Report.objects.create(
        shop=shop,
        uptime_last_day=uptime_last_day,
        uptime_last_hour=uptime_last_hour,
        uptime_last_week=uptime_last_week,
        downtime_last_day=downtime_last_day,
        downtime_last_hour=downtime_last_hour,
        downtime_last_week=downtime_last_week
    )
    return report