Speed ​​up django nested in loop time loop

Question

I am working on a django-based open source project called OpenREM ( http://demo.openrem.org/openrem/ , http://openrem.org ).

To calculate the data for one of the graphs used, I perform a series of queries to get the number of items that fall into each of 24 hours for each day of the week. These data are used to build a pie chart of studies on a weekday on the CT page of a demo site, followed by an hourly study for that day:

studiesPerHourInWeekdays = [[0 for x in range(24)] for x in range(7)] for day in range(7): studyTimesOnThisWeekday = f.qs.filter(study_date__week_day=day+1).values('study_time') if studyTimesOnThisWeekday: for hour in range(24): try: studiesPerHourInWeekdays[day][hour] = studyTimesOnThisWeekday.filter(study_time__gte = str(hour)+':00').filter(study_time__lte = str(hour)+':59').values('study_time').count() except: studiesPerHourInWeekdays[day][hour] = 0 

It takes a little time to work in the production system. I think the second FOR loop can be removed using qsstats-magic time_series aggregated over several hours. Unfortunately, there is no suitable datetime object in the database that I can use for this.

Does anyone know how I can combine the datetime.date object "study_date" and the datetime.time object "study_time" into one datetime.datetime object so that I can run qsstats-magic time_series clockwise?

Thanks,

David

Answer 1

If you can at all (although you do not seem to agree with your circumstances), it is best to modify the database schema to reflect the types of queries you make. The datetime field in which this information was indicated, some type of foreign key, etc.

You probably already know that, nevertheless, the practical answer to your question is that you want to use the basic database tools to your advantage through an additional () call . Maybe something like this * if you are using postgres:

 date_hour_set = f.qs.extra( select={ 'date_hour': "study_date + interval '1h' * date_part('hour', study_time)", 'date_hour_count': "count(study_date + interval '1h' * date_part('hour', study_time))" }).values('date_hour', 'date_hour_count').distinct() 

which will provide you with a request for the number of dates (hours only) with their associated number of occurrences. Handwritten SQL will give you the easiest option at the moment because Django is behind TimeField support and is likely to be the most productive.

* Note. I don't write SQL regularly and am getting lazy, so there are cleaner ways to do this.

If you really need a portable database and you can't edit the schema , you can combine the Django aggregation functions, which can be a bit confusing:

 from django.db.models import Value, Count, ExpressionWrapper, CharField from django.db.models.functions import Substr, Concat hour_counts = f.qs.annotate(hour=Concat(Substr('study_time', 1, 2), Value(':00:00'))) date_hour_pairs = hour_counts.annotate( date_hour=ExpressionWrapper(Concat('study_date', 'hour'), output_field=CharField())).values('study_date', 'hour', 'date_hour') date_hour_counts = date_hour_pairs.annotate(count=Count('date_hour')).distinct() 

which should provide you with a set of dicts with a datetime.time object for 'hour', datetime.date, from which you started to use for 'study_date', a date and time string string according to 'date_hour', and then all the important ones (date, hour ) are counted under "count".