Date filter binned data

I have a model in django that contains a start and end date for a financial year and a value for demand volume.

    start_date = models.DateField(null=True)
    end_date = models.DateField(null=True)
    new_demand = models.PositiveIntegerField(default=0)

I would like to filter this data on a given date range that may not coincide with the date ranges of the data, taking a proportion of the demand from each data point that may fall somewhere in the filter date range. I am assuming I need to work out if the data point falls over the edge of the filter and divide the demand by the number of days inside the filter? But I am having a hard time trying to write this. any help would be appreciated! :)

Answer

If you don’t need to do the calculation inside the database you can filter for overlapping date ranges (here is a nice picture of when ranges overlap) and perform the calculations in python when iterating over the queryset:

for obj in MyModel.objects.filter(start_date__lte=end, end_date__gte=start):
    overlap = min(end, obj.end_date) - max(start, start_date)
    proportion = (obj.end_date - obj.start_date) / overlap
    print(obj, "demand in overlapping range:", obj.new_demand * proportion)