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

一、概述

有一个用户表，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

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__gte=str(today) + ' 00:00:00')
    today_len = len(ret)

    # 昨天###############
    yesterday = (datetime.datetime.now() + datetime.timedelta(days=-1)).date()
    ret = models.User.objects.filter(create_time__gte=str(yesterday) + ' 00:00:00',
                                         create_time__lte=str(today) + ' 23:59:59')
    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=str(weekdelta) + ' 00:00:00',
                                         create_time__lte=str(today) + ' 23:59:59')
    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):
    now = datetime.datetime.now()
    a_month = now - datetime.timedelta(days=50)
    select = {'day': 'date(create_time)'}
    count_data = models.User.objects. \
        filter(create_time__range=(a_month, now)). \
        extra(select=select).values('day').distinct().order_by("day").annotate(number=Count('create_time'))

    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/

效果如下：

 

 

访问最近一个月分组数据

http://127.0.0.1:8001/month/

效果如下：

 

 

访问最近一年的分组数据

http://127.0.0.1:8001/year/

效果如下：