Django如何处理数据库中的时间

qiang8216 · · 2327 次点击 · · 开始浏览

这是一个创建于的文章，其中的信息可能已经有所发展或是发生改变。

一、数据库中的时间格式化

 1 class Article(models.Model):
 2     """
 3     文章表
 4     """
 5     aid = models.BigAutoField(primary_key=True)
 6     title = models.CharField(verbose_name="文章标题", max_length=128)
 7     summary = models.CharField(verbose_name="文章简介", max_length=255)
 8     read_count = models.IntegerField(default=0)
 9     comment_count = models.IntegerField(default=0)
10     up_count = models.IntegerField(default=0)
11     down_count = models.IntegerField(default=0)
12     create_time = models.DateTimeField(verbose_name="创建时间", auto_now_add=True)
13 
14     blog = models.ForeignKey(verbose_name="所属博客", to="Blog", to_field="bid")
15     category = models.ForeignKey(verbose_name="文章类型", to="Category", to_field="cid", null=True, related_name="articles")
16 
17     type_choice = [
18         (1, "Python"),
19         (2, "Linux"),
20         (3, "OpenStack"),
21         (4, "GoLang"),
22     ]
23 
24     article_type_id = models.IntegerField(choices=type_choice, default=None)
25 
26     tags = models.ManyToManyField(
27         to="Tag",
28         through="Article2Tag",
29         through_fields=("article", "tag"),
30         related_name="articles",
31         )

models.py

sqlite3中处理

def home(request, site):
    """
    博主个人首页
    :param request:
    :param site: 博主的网站后缀如：http://xxx.com/wupeiqi.html
    :return:
    """
 
    # 生成三个字段nid，num，ctime 日期按照%Y-%m年月进行分组
    date_list = models.Article.objects.raw(         # raw执行原生SQL语句
        'select nid, count(nid) as num,strftime("%Y-%m",create_time) as ctime from repository_article group by strftime("%Y-%m",create_time)')
    # date_format(create_time,"%Y-%m")      # 上边是sqllist的方式，mysql把strftime更换为date_format即可
 
    # date_list 提取数据的方法
    for item in date_list:
        print(item.nid,item.num,item.ctime)
    # 4 1 2017 - 01
    # 13 8 2017 - 02
    # 7 1 2017 - 03

mysql中处理

def filter(request, site, condition, val):
     ---snip---
 
    elif condition == 'date':
        # mysql的执行方法
        # article_list = models.Article.objects.filter(blog=blog).extra(
        # where=['date_format(create_time,"%%Y-%%m")=%s'], params=[val, ]).all()
         
        # extra 构造额外的查询条件或者映射，如：子查询
        article_list = models.Article.objects.filter(blog=blog).extra(   # val 传入的日期参数 如：2017-02
            where=['strftime("%%Y-%%m",create_time)=%s'], params=[val, ]).all()    # python两个%表示一个%的字符 var对应%s     
        # 上面语句等同于 select * from article where strftime("%Y-%m",create_time)=2017-02

二、模板中的时间格式化

from django import template
from django.utils.safestring import mark_safe
import time


register = template.Library()


@register.simple_tag
def conversion_time(date, format):
    if format == "%Y-%m-%d %H:%M":
        date = str(date)[:-14]
        date = time.strptime(date, "%Y-%m-%d %H:%M:%S")
        date = time.mktime(date) + 28800
        date = time.gmtime(date + 28800)
        date = time.strftime(format, date)
        return mark_safe(date)
    elif format == "%Y-%m-%d":
        date = str(date)[:-14]
        date = time.strptime(date, "%Y-%m-%d %H:%M:%S")
        date = time.mktime(date) + 28800
        date = time.gmtime(date + 28800)
        date = time.strftime(format, date)
        return mark_safe(date)
    elif format == "%Y-%m":
        date = str(date)[:-14]
        date = time.strptime(date, "%Y-%m-%d %H:%M:%S")
        date = time.mktime(date) + 28800
        date = time.gmtime(date + 28800)
        date = time.strftime(format, date)
        return mark_safe(date)