Django中日期时间型字段进行年月日时分秒分组统计
燃灯工作室 人气:61.数据结构说明:
数据结构如下:modification字段为修改数据时间字段,格式为 年,月,日,时,分,秒。
案例场景为,根据modification字段,统计每个统计粒子,产生数据的条数。如需要统计2020年10月29日 每个小时段产生数据的条数。
要进行这种统计需要用到 Django的connection库。
统计年月日粒子用 date_trunc_sql,统计时分秒用 datetime_extract_sql
2.进行年月日粒子的统计
2.1 官方 date_trunc_sql 原型
def datetime_trunc_sql(self, lookup_type, field_name, tzname): """ Given a lookup_type of 'year', 'month', 'day', 'hour', 'minute', or 'second', return the SQL that truncates the given datetime field field_name to a datetime object with only the given specificity. """ raise NotImplementedError('subclasses of BaseDatabaseOperations may require a datetime_trunc_sql() method')
解释说明下:
函数需要传入三个参数:
- lookup_type:统计粒子(year->年,月->month,day->日,hour->时,minute->分,second->秒)
- field_name:统计字段的名次
- tzname:时区,在中国一般用的是东8区,传入8即可
2.2 年
from django.db import connection from django.db.models import Count # 'year', 'month', 'day', 'hour', 'minute', 'second' select = {'year': connection.ops.datetime_trunc_sql('year', 'establish', 8)} result = models.FocusOnRecord.objects.extra(select=select).values('year').annotate(number=Count('id')).order_by("year")[0:9] for key in result: print(key) >>{'year': datetime.datetime(2019, 1, 1, 0, 0), 'number': 2168} >>{'year': datetime.datetime(2020, 1, 1, 0, 0), 'number': 9369}
2.3 月
from django.db import connection from django.db.models import Count # 'year', 'month', 'day', 'hour', 'minute', 'second' select = {'month': connection.ops.datetime_trunc_sql('month', 'establish', 8)} result = models.FocusOnRecord.objects.extra(select=select).values('month').annotate(number=Count('id')).order_by("month")[0:9] for key in result: print(key) >>{'month': datetime.datetime(2019, 6, 1, 0, 0), 'number': 8} >>{'month': datetime.datetime(2019, 7, 1, 0, 0), 'number': 51} >>{'month': datetime.datetime(2019, 8, 1, 0, 0), 'number': 118} >>{'month': datetime.datetime(2019, 9, 1, 0, 0), 'number': 7} >>{'month': datetime.datetime(2019, 10, 1, 0, 0), 'number': 731} >>{'month': datetime.datetime(2019, 11, 1, 0, 0), 'number': 514} >>{'month': datetime.datetime(2019, 12, 1, 0, 0), 'number': 739} >>{'month': datetime.datetime(2020, 1, 1, 0, 0), 'number': 483} >>{'month': datetime.datetime(2020, 2, 1, 0, 0), 'number': 921}
2.4 日
from django.db import connection from django.db.models import Count # 'year', 'month', 'day', 'hour', 'minute', 'second' select = {'day': connection.ops.datetime_trunc_sql('day', 'establish', 8)} result = models.FocusOnRecord.objects.extra(select=select).values('day').annotate(number=Count('id')).order_by("day")[0:9] for key in result: print(key) >>{'day': datetime.datetime(2019, 6, 28, 0, 0), 'number': 1} >>{'day': datetime.datetime(2019, 6, 29, 0, 0), 'number': 7} >>{'day': datetime.datetime(2019, 7, 2, 0, 0), 'number': 1} >>{'day': datetime.datetime(2019, 7, 11, 0, 0), 'number': 3} >>{'day': datetime.datetime(2019, 7, 20, 0, 0), 'number': 32} >>{'day': datetime.datetime(2019, 7, 21, 0, 0), 'number': 2} >>{'day': datetime.datetime(2019, 7, 29, 0, 0), 'number': 3} >>{'day': datetime.datetime(2019, 7, 31, 0, 0), 'number': 10} >>{'day': datetime.datetime(2019, 8, 1, 0, 0), 'number': 20}
2.5 时
from django.db import connection from django.db.models import Count # 'year', 'month', 'day', 'hour', 'minute', 'second' select = {'hour': connection.ops.datetime_trunc_sql('hour', 'establish', 8)} result = models.FocusOnRecord.objects.extra(select=select).values('hour').annotate(number=Count('id')).order_by("hour")[0:9] for key in result: print(key) >>{'hour': datetime.datetime(2019, 6, 28, 17, 0), 'number': 1} >>{'hour': datetime.datetime(2019, 6, 29, 9, 0), 'number': 6} >>{'hour': datetime.datetime(2019, 6, 29, 10, 0), 'number': 1} >>{'hour': datetime.datetime(2019, 7, 2, 14, 0), 'number': 1} >>{'hour': datetime.datetime(2019, 7, 11, 14, 0), 'number': 2} >>{'hour': datetime.datetime(2019, 7, 11, 15, 0), 'number': 1} >>{'hour': datetime.datetime(2019, 7, 20, 11, 0), 'number': 24} >>{'hour': datetime.datetime(2019, 7, 20, 12, 0), 'number': 3} >>{'hour': datetime.datetime(2019, 7, 20, 13, 0), 'number': 2}
2.6 分
from django.db import connection from django.db.models import Count # 'year', 'month', 'day', 'hour', 'minute', 'second' select = {'minute': connection.ops.datetime_trunc_sql('minute', 'establish', 8)} result = models.FocusOnRecord.objects.extra(select=select).values('minute').annotate(number=Count('id')).order_by("minute")[0:9] for key in result: print(key) >>{'minute': datetime.datetime(2019, 6, 28, 17, 22), 'number': 1} >>{'minute': datetime.datetime(2019, 6, 29, 9, 6), 'number': 4} >>{'minute': datetime.datetime(2019, 6, 29, 9, 39), 'number': 1} >>{'minute': datetime.datetime(2019, 6, 29, 9, 41), 'number': 1} >>{'minute': datetime.datetime(2019, 6, 29, 10, 4), 'number': 1} >>{'minute': datetime.datetime(2019, 7, 2, 14, 57), 'number': 1} >>{'minute': datetime.datetime(2019, 7, 11, 14, 48), 'number': 1} >>{'minute': datetime.datetime(2019, 7, 11, 14, 54), 'number': 1} >>{'minute': datetime.datetime(2019, 7, 11, 15, 40), 'number': 1}
2.7 秒
from django.db import connection from django.db.models import Count # 'year', 'month', 'day', 'hour', 'minute', 'second' select = {'second': connection.ops.datetime_trunc_sql('second', 'establish', 8)} result = models.FocusOnRecord.objects.extra(select=select).values('second').annotate(number=Count('id')).order_by("second")[0:9] for key in result: print(key) >>{'second': datetime.datetime(2019, 6, 28, 17, 22, 54), 'number': 1} >>{'second': datetime.datetime(2019, 6, 29, 9, 6, 1), 'number': 1} >>{'second': datetime.datetime(2019, 6, 29, 9, 6, 18), 'number': 1} >>{'second': datetime.datetime(2019, 6, 29, 9, 6, 35), 'number': 1} >>{'second': datetime.datetime(2019, 6, 29, 9, 6, 36), 'number': 1} >>{'second': datetime.datetime(2019, 6, 29, 9, 39, 30), 'number': 1} >>{'second': datetime.datetime(2019, 6, 29, 9, 41, 17), 'number': 1} >>{'second': datetime.datetime(2019, 6, 29, 10, 4, 15), 'number': 1} >>{'second': datetime.datetime(2019, 7, 2, 14, 57, 56), 'number': 1}
加载全部内容