django查询今天，昨天，一周，分组统计月，年

短命女 2023-03-01 11:59 8阅读 0赞

# 一、概述 #

有一个用户表，models.py内容如下：

from django.db import models
    
    # Create your models here.
    class User(models.Model):   #用户名表
        username = models.CharField(max_length=16,verbose_name="用户名")
        password = models.CharField(max_length=32,verbose_name="密码")
        create_time = models.DateTimeField(auto_now_add=True,verbose_name="创建时间")
        last_time = models.DateTimeField(verbose_name="登录时间")
    
        class Meta:
            # 多列唯一索引
            unique_together = ('username','create_time')

本文用的是sqlite3数据库，插入初始数据，insert语句如下：

INSERT INTO "main"."application_user" ("id", "username", "password", "create_time", "last_time", "ROWID") VALUES (1, 'xiao1', 1234, '2020-01-01 13:14:52', '2020-01-01 13:14:52', 1);
    INSERT INTO "main"."application_user" ("id", "username", "password", "create_time", "last_time", "ROWID") VALUES (2, 'xiao2', 1234, '2020-02-02 13:14:52', '2020-02-02 13:14:52', 2);
    INSERT INTO "main"."application_user" ("id", "username", "password", "create_time", "last_time", "ROWID") VALUES (3, 'xiao3', 1234, '2020-03-03 13:14:52', '2020-03-03 13:14:52', 3);
    INSERT INTO "main"."application_user" ("id", "username", "password", "create_time", "last_time", "ROWID") VALUES (4, 'xiao4', 1234, '2020-04-04 13:14:52', '2020-04-04 13:14:52', 4);
    INSERT INTO "main"."application_user" ("id", "username", "password", "create_time", "last_time", "ROWID") VALUES (5, 'xiao5', 1234, '2020-05-05 13:14:52', '2020-05-05 13:14:52', 5);
    INSERT INTO "main"."application_user" ("id", "username", "password", "create_time", "last_time", "ROWID") VALUES (6, 'xiao6', 1234, '2020-06-06 13:14:52', '2020-06-06 13:14:52', 6);
    INSERT INTO "main"."application_user" ("id", "username", "password", "create_time", "last_time", "ROWID") VALUES (7, 'xiao7', 1234, '2020-07-07 13:14:52', '2020-07-07 13:14:52', 7);
    INSERT INTO "main"."application_user" ("id", "username", "password", "create_time", "last_time", "ROWID") VALUES (8, 'xiao8', 1234, '2020-07-23 13:14:52', '2020-07-22 13:14:52', 8);
    INSERT INTO "main"."application_user" ("id", "username", "password", "create_time", "last_time", "ROWID") VALUES (9, 'xiao9', 1234, '2020-07-24 13:14:52', '2020-07-22 13:14:52', 9);

需求如下：

1. 查询今天，昨天，一周的用户数。

2. 最近一个月，分组统计每一天的数量

3. 最近1年，分组统计每一个月的数量

# 二、项目演示 #

新建一个项目，名字为：test\_rom，应用名称为：application

django版本为：3.0.8

## settings.py ##

修改时区，内容如下：

TIME_ZONE = 'Asia/Shanghai'
    
    USE_I18N = True
    
    USE_L10N = True
    
    USE_TZ = False

## urls.py ##

from django.contrib import admin
    from django.urls import path
    from application import views
    
    urlpatterns = [
        path('admin/', admin.site.urls),
        path('index/', views.index),
        path('month/', views.month),
        path('year/', views.year),
    ]

views.py

![aHR0cHM6Ly9pbWFnZXMuY25ibG9ncy5jb20vT3V0bGluaW5nSW5kaWNhdG9ycy9Db250cmFjdGVkQmxvY2suZ2lm][] ![aHR0cHM6Ly9pbWFnZXMuY25ibG9ncy5jb20vT3V0bGluaW5nSW5kaWNhdG9ycy9FeHBhbmRlZEJsb2NrU3RhcnQuZ2lm][]

from django.shortcuts import render
    import datetime
    from application import models
    from django.http import JsonResponse
    import time
    from dateutil.relativedelta import relativedelta
    from django.db.models.functions import TruncMonth,TruncYear,ExtractYear,ExtractMonth
    from django.db.models import Count
    from django.db import connection
    
    # Create your views here.
    def index(request):
        # 今日###############
        today = datetime.datetime.now().date()
        ret = models.User.objects.filter(create_time__gt=today)
        today_len = len(ret)
    
        # 昨天###############
        yesterday = (datetime.datetime.now() + datetime.timedelta(days=-1)).date()
        ret = models.User.objects.filter(create_time__lt=today,create_time__gt=yesterday)
        yesterday_len = len(ret)
    
        # 一周前
        today = datetime.datetime.now().date()
        weekdelta = datetime.datetime.now().date() - datetime.timedelta(weeks=1)
        ret = models.User.objects.filter(create_time__gte=weekdelta, create_time__lte=today)
        week_len = len(ret)
    
        # 总数量
        ret = models.User.objects.all()
        total_len = len(ret)
    
        data = {
            "today": today_len,
            "yesterday": yesterday_len,
            "week": week_len,
            "total": total_len
        }
    
        return JsonResponse(data)
    
    
    def month(request):
        # 当前年，月
        this_year = time.strftime("%Y", time.localtime(time.time()))
        this_month = time.strftime("%m", time.localtime(time.time()))
    
        # 按天分组
        select = {
         'day': connection.ops.date_trunc_sql('day', 'create_time')}
        count_data = models.User.objects.filter(create_time__year=this_year, create_time__month=this_month).extra(
            select=select).values('day').annotate(number=Count('id'))
    
        x_list = []
        y_list = []
        for i in count_data:
            x_list.append(i['day'])
            y_list.append(i['number'])
    
        data = {
         "x": x_list, "y": y_list}
        return JsonResponse(data)
    
    
    def year(request):
        # 计算时间
        time_ago = datetime.datetime.now() - relativedelta(years=1)
        # print("time_ago",time_ago)
        # 获取近一年数据
        one_year_data = models.User.objects.filter(create_time__gte=time_ago)
        # 分组统计每个月的数据
        count_res = one_year_data \
            .annotate(year=ExtractYear('create_time'), month=ExtractMonth('create_time')) \
            .values('year', 'month').order_by('year', 'month').annotate(count=Count('id'))
    
        # 封装数据格式
        month_list = []
        count_list = []
        for i in count_res:
            month_list.append("%s-%s" % (i['year'], i['month']))
            count_list.append(i["count"])
    
        data = {
         "month": month_list, "count": count_list}
        return JsonResponse(data)

## 启动并访问 ##

使用Pycharm直接启动即可，访问首页：

http://127.0.0.1:8001/index/

效果如下：

![format_png][]

访问最近一个月分组数据

http://127.0.0.1:8001/month/

效果如下：

![format_png 1][]

访问最近一年的分组数据

http://127.0.0.1:8001/year/

效果如下：

![format_png 2][]

[aHR0cHM6Ly9pbWFnZXMuY25ibG9ncy5jb20vT3V0bGluaW5nSW5kaWNhdG9ycy9Db250cmFjdGVkQmxvY2suZ2lm]: http://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMuY25ibG9ncy5jb20vT3V0bGluaW5nSW5kaWNhdG9ycy9Db250cmFjdGVkQmxvY2suZ2lm
[aHR0cHM6Ly9pbWFnZXMuY25ibG9ncy5jb20vT3V0bGluaW5nSW5kaWNhdG9ycy9FeHBhbmRlZEJsb2NrU3RhcnQuZ2lm]: /images/20230208/055c2f5408ac4955974f0004bc977e9d.png
[format_png]: /images/20230208/4d98d77be24046ec88fb6761a4b25db9.png
[format_png 1]: /images/20230208/2da51d6336834fa3ac80004ed0ffcbd8.png
[format_png 2]: /images/20230208/d3bf5c95b9c942b3bd43f826f490f3fc.png