python - django filter to calculate hours within range -

i have set of data defines datetime stamp "in" , datetime stamp "out" when working. each person have multiple combinations of in-s , out-s on many months.

class inout(models.model):     user = models.foreignkey(user)     in_dt = models.datetime     out_dt = models.datetime     hours = models.floatfield 

(then have signal calculates hours worked between out_dt , in_dt.)

i want write code/filter/query calculates total hours worked between start_date , end_date (e.g., on month). kicker if started work on last day of month , ended on first of next month hours should include hours midnight of last day of month.

now, can create queryset filters entries contained between start_date , end_date (e.g., month).

worked_in_month = inout.objects.filter( in_dt__lte=end_date, out_dt__gte=start_date) 

and can annotation or values , annotation , sum(hours) doesn't account hours outside of start_date/end_date. or can try , in_dt , out_dt , ignore precalculated hours.

i can calculations in python (which might answer) wondering if might missing in djano filtering etc.

i can think of @ least 2 approaches problem.

a (rather convoluted) query:

month_start = datetime(year, month, 1, 0, 0, 0, 0, tz); next_month = (month % 12) + 1 next_month_start = datetime(year, next_month, 1, 0, 0, 0, 0, tz)  models.inout.objects.filter(     (         q(in_dt__gte=month_start) , q(in_dt__lt=next_month_start))         | (q(out_dt__gte=month_start) , q(out_dt__lt=next_month_start)     )  ).annotate(      start_in_month=func(f('in_dt'), month_start, function='max'),      end_in_month=func(f('out_dt'), month_end, function='min')  ).aggregate(worked=sum(f('end_in_month') - f('start_in_month')) 

if using postgresql need use

 .annotate(      start_in_month=func(f('in_dt'), month_start, function='greatest'),      end_in_month=func(f('out_dt'), month_end, function='least')  ) 

since in postgresql max() , min() not defined date types.

note aggregation not work on sqlite because not have appropriate data types (dates stored text).

preprocessing entries

in database, inout entries span month border logically (not physically) 2 entries:

1. one starts @ designated time , ends @ month end
2. one starts @ end of month , ends @ designated time

filtering out affected inout objects takes little thinking, since f() objects cannot (currently) resolve parts of datetimes (e.g. in_dt__month).

something 1 lines of

# xxx - magic number of months month in range(1, 13):     wraparound in models.inout.objects.filter(         q(in_dt__month=month) , ~q(out_dt__month=month)     )         year = wraparound.in_dt.year         next_month = (month % 12) + 1         month_end = datetime(year, next_month, calendar.monthrange(year, month)[1], 23, 59, 59, 999999, tz)         next_month_start = datetime(year, next_month, 1, 0, 0, 0, 0, tz)          models.inout.objects.bulk_create([             models.inout(user=wraparound.user, in_dt=wraparound.in_dt, out_dt=month_end),             models.inout(user=wraparound.user, in_dt=next_month_start, out_dt=wraparound.out_dt)         ])         wraparound.delete() 

could trick, however.

ideally, don't afterwards when saving time entry in view. might confound users because 2 entries instead of 1 when entering wraparound work span.

caveat emptor: might need dicker around next_month, next_month_start , __lt __gte bit, because implementation looses microsecond @ end of each wraparound after expansion.

and yes, nice exercise ;-)