Python Django MySQL，时区、日期、时间戳（USE_TZ=True时的时间存储问题）

Python Django MySQL，时区、日期、时间戳，写下这个标题的时候，头脑里面回荡着火车上的经典广告词：啤酒、饮料、矿泉水，花生、瓜子、八宝粥。当然本文跟这些零食吃喝没有关系，我们主要来聊聊时间问题。

---

环境说明：

1、约定：

  本文中的“时间”，如未特别说明均指“日期+时间”，即形如“%Y-%m-%d %H:%M:%S”，或“yyyy-mm-dd HH:MM:SS” 等包含日期和时间点的值，可能包含形如 “.fraction” 的毫秒级的数值以及时区标识。

2、软件版本：

  Python：2.7.10

  Django：1.11.12 final

  MySQL：5.7.18

3、基础数据：

  Django Model：

class IcsServiceStatusModel(models.Model):
    class Meta:
        db_table = 'ics_service_status'
        app_label = 'ics_meta'

    objects = SelfDefinedManager()

    id = models.AutoField(primary_key=True)
    pub_id = models.CharField(max_length=15)
    service_mode = models.SmallIntegerField(max_length=5)
    current_session = models.CharField(max_length=50)
    last_msg_dt = models.DateTimeField()
    created_at = models.DateTimeField()
    updated_at = models.DateTimeField()

  MySQL 数据表：

CREATE TABLE IF NOT EXISTS `ics_service_status` (
	`id` int(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`pub_id` varchar(15) NOT NULL,
	`service_mode` tinyint(5) NOT NULL,
	`current_session` varchar(50) NOT NULL,
	`last_msg_dt` datetime NOT NULL,
	`created_at` datetime NOT NULL,
	`updated_at` datetime NOT NULL,
	PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='智能客服服务状态跟踪表' $$

---

知识点一、计算机上的时间表示

1、时间的常规表示

时间通常分为日期和时刻，表示为 y 年 m 月 d 日 H 时 M 分S 秒。几个单位之间的换算简单计为1年=365天（闰年366天），1个月=28天、29天、30天或31天（分别对应平年的2月，闰年的2月，3、6、9、11月，1、3、5、7、8、10、12月），1天=24小时，1小时=60分钟，1分钟=60秒。经典格式：yyyy-mm-dd HH:MM:SS。

2、时区

太阳一升一落一天过去了。毛爷爷说“青年人好像早晨八九点钟的太阳”，我们可以很直观地感受到八九点正式太阳升起后两三个小时正挂在半空的时候。同样的这句话换作是西半球的美国人、南半球的巴西人、赤道上的印尼人来理解也都是不会有问题的。因为我们日常生活中采用的计时方式都是参考太阳的移动周期来确定时刻的（比如中国的日晷）：一天分为24个小时，太阳的照射下，物体的影子最短的时候为正午12点，前后均分，各12个小时，由此确定的时间为地方时，或本地时间（Local Time）。地理知识告诉我们，地球的自传、绕太阳公转使得让物体的影子最短的太阳需要由东到西逐次照射到地球的每个角落。所以我们看到的八九点钟的太阳跟外国人眼中的八九点钟的太阳已经不是同一个。这种时间表示方式方便在世界各地的人们相对统一地建立时间与环境的对应关系，由此产生便于沟通交流的意识。然而不同经度上的人们，尤其是跨度比较大的两个地方，如果都使用各自本地的时间交流的话就要出乱子了。已知华盛顿的正午比北京的正午来的晚12个小时。假如华盛顿飞北京要13个小时，身居北京的小丽晚上8点吃过晚饭后就给美国的好朋友凯特打电话说：“明天晚上8点去我家参加生日派对，傍晚6点去机场接你”。凯特是个很守时的小朋友。他想傍晚6点要飞到北京，总共又要飞13个小时，那岂不是要早晨5点就出发了？于是乎第二天凯特起了个大早，5点天还蒙蒙亮飞机准时起飞了。手上捧着精心挑选的礼物，期待着快点送给小丽。13个小时过后飞机平稳地降落在北京的机场，在机场的到达厅他没有看到小丽，太阳刚刚升起，路边的摊贩正在叫卖早餐。凯特打电话给小丽，却被臭骂了一顿“你为什么不准时参加我的生日派对？” 这是为啥捏？

​

为了便于不同地区的人们交流时间，地球人需要一个统一的标准时间(UTC, Coordinated Universal  Time, 协调世界时，诞生于1972年；注1：与世界时UT相差±0.9s；注2：UTC 基于TAI，即International Atomic Time，国际原子时，计算秒)。大伙约定以东经0°（也是西经0°，俗称本初子午线）所在地区的地方时的中午12点作为世界标准时间的0点。同时东经180°（也是西经180°）设为标准日期变更的界线。这么看世界标准时间更像是国际日期变更线上的阿留申群岛居民的地方时。其他地区的本地时间与世界标准时间则用时差进行换算：由本初子午线往东，每隔15°经度时间加1小时；往西，每隔15°经度时间减去1小时。这样以15°为单位划分的360°÷15°=24个区域就是时区。

回到小丽请客的问题，假设他们打电话的时刻是位于东八区的北京时间2018年5月6日晚上8点，那么此时正是世界标准时间的5月6日中午12点，而位于西四区的华盛顿，凯特的家，当地的时间则为5月6日的上午8点。而小丽说的生日派对的时间为北京时间5月7日晚上8点，对应世界标准时间则为5月7日中午12点，华盛顿凯特家的5月7日上午8点。所以凯特应该在当地时间5月6日下午5点出发，才能赶上小丽在机场接他。然而他是华盛顿时间5月7日早晨5点出发的，对应世界标准时间为5月7日上午9点，是小丽所在的北京时间的5月7日下午5点了，一个小时可不是要错过吗？

所以小丽在说时间的时候一定要强调是北京时间的明晚8点，表示为：2018-05-07 20:00:00+8:00:00 （或2018-05-07 20:00:00 UTC+8），而凯特在做行程计划的时候也要记得把约会时间转换为华盛顿的次日早上8点：2018-05-07 08:00:00-4:00:00，从而推算出发时间是华盛顿时间的当天下午5点，即：2018-05-06 17:00:00-4:00:00。当然，如果他们统一用世界标准时交流的话会更简单。小丽只需要说“2018年5月7日的12点我生日，过来 happy，10点去机场接你”，卡特也只要买好世界标准时间2018年5月6日21点的机票就可以按时赴约了。

总之呢，交流时间的时候声明是哪里的时间是很重要滴。一个明确的日期时间可以表示为：

yyyy-mm-dd HH:MM:SS UTC±n

其中“+”表示“东”，“-”表示“西”，结合 n 一起表示时区的编号。UTC±n 声明了前面的时间是哪个时区的地方时。转换为世界标准时间只需要 ±n 小时即可。如果不写后面的 UTC±n，默认情况下就是 UTC±0，也就是标准世界时间了。

世界时区查询：http://www.shijian.cc/shiqu/

3、时间戳

时间的表示经常使用一个公认的参考点，比如日常使用中默认的“（公元后）几几年”，需要明确说明的“公元前几几年”、“民国几几年”、“顺治几几年”，它们分别用公认的元年、新国家的成立日期、统治者的上位日期等作为参考点。在计算机里面我们以 1970年1月1日0点作为参考点，用偏差值来记录具体的时间，精确到秒。参考时间点的设置有时是为了便于记忆、也便于使用。而计算机上参考点的设置还受到一个客观因素的约束。

早期的CPU和操作系统以32位为主。如果用一个整数完整地表示公元后的时间，精确到秒，那么y年m月d日H时M时S秒需要用整数

(y-1)×365×24×3600+(m-1)×30×24×3600+(d-1)×24×3600+H×3600+M×60+S)

来表示（假设一年365天、一个月30天），以2018年7月17日15点12分46秒为例，对应的整数值为

2017×365×24×3600+6×30×24×3600+16×24×3600+15×3600+12×60+46 = 63,665,190,766

然而32位处理器能够表示的最大整数值为：无符号数，2^32-1，即 4,294,967,295；有符号数，2^31-1，即 2,147,483,647，远不及 63,665,190,766。一年（按365天算）等于 31,536,000 秒，32位处理器能表示的最大整数值只能表示 (2^31-1)÷(365×24×3600) ≈ 68(年) 也就是只能表示公元前68年到公元68年之间的日期。

那么该怎么满足使用计算机处理时间的需求呢？“计算机计时元年” 的概念由此诞生。UNIX操作系统考虑到计算
机产生的年代和应用的时限综合取了1970年1月1日作为UNIX TIME的纪元时间。于是y年m月d日H时M时S秒，用该时间与纪元时间的整数差值表示为

(y年m月d日与1970年1月1日的日期差)×24×3600+H×3600+M×60+S

该差值也称为时间戳。同样的，哪怕是时间差值，32位的处理器也只能表示1970年1月1日前后68年的时间，也就是 1901年12月13日20时45分52秒到2038年01月19日03时14分07秒。所以呢，使用32位处理器的老机器们届时将面临类似“千年虫”的“2038年问题”。

知识点二、MySQL 中的时间表示

1、datetime 和 timestamp

在 MySQL 中时间可以用 datetime 和 timestamp 两种类型的字段表示。（date 类型可以存储日期，time 类型可以存储时间）

二者的相同点：可通过设置默认值自动更新和初始化，默认显示格式都为：YYYY-MM-dd HH:mm:ss

二者的不同点：

    ① timestamp 类型的字段实际存储的是距离1970年1月1日0点的秒数，用4字节存储，可以根据时区设置转换为指定时区的时间值，存储范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。

    ② datetime 类型的字段用8字节存储，对时区设置无感知，存储范围从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。

2、MySQL 中获取时间可以用以下函数

    ① now()：当前日期时间，例如："2018-07-18 16:07:23"

    ② curdate()：当天日期，例如："2018-07-18"

    ③ curtime()：当前时间，例如："16:07:23"

    ④ timestamp、current_timestamp、current_timestamp()、localtime()、localtimestamp()、unix_timestamp(now())

    ⑤ date_sub(curdate(),interval 1 day)：日期减（date_add、timediff）

3、在实验中理解 datetime 和 timestamp 的不同

（1）查看 explicit_defaults_for_timestamp，即，“是否明确地给 timestamp 类型的字段设置默认值”：

​

    注：MySQL 5.6.6 版本启用了系统变量 explicit_defaults_for_timestamp，高于 5.6.6版本的 MySQL 则有该特性。

​

    当 explicit_defaults_for_timestamp=false 时，按照如下规则"初始化"：

    ① 未明确声明为 NULL 属性的 TIMESTAMP 列被分配为 NOT NULL 属性。 （其他数据类型的列，如果未显式声明为 NOT NULL，则允许 NULL 值。）将此列设置为NULL将其设置为当前时间戳。

    ② 表中的第一个 TIMESTAMP 列（如果未声明为 NULL 属性或显式 DEFAULT 或 ON UPDATE 子句）将自动分配 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性。

    ③ 第一个之后的 TIMESTAMP 列（如果未声明为 NULL 属性或显式 DEFAULT 子句）将自动分配 DEFAULT '0000-00-00 00:00:00'（“零”时间戳）。 对于不指定此列的显式值的插入行，该列将分配 “0000-00-00 00:00:00”，并且不会发生警告。

    当 explicit_defaults_for_timestamp=true 时，按照如下规则"初始化"：

    ① 未明确声明为 NOT NULL 的 TIMESTAMP 列允许 NULL 值。 将此列设置为 NULL，而不是当前时间戳。

    ② 没有 TIMESTAMP 列自动分配 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP 属性。 必须明确指定这些属性。

    ③ 声明为 NOT NULL 且没有显式 DEFAULT 子句的 TIMESTAMP 列被视为没有默认值。 对于不为此列指定显式值的插入行，结果取决于 SQL 模式。 如果启用了严格的 SQL 模式，则会发生错误。 如果未启用严格的 SQL 模式，则会为列分配隐式默认值 “0000-00-00 00:00:00”，并发出警告。 这类似于 MySQL 如何处理其他时间类型，如 DATETIME。

    不同 TIMESTAMP 默认值的作用：

    ① CURRENT_TIMESTAMP

        在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它

    ② ON UPDATE CURRENT_TIMESTAMP

        在创建新记录的时候把这个字段设置为0，以后修改时刷新它

    ③ CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  

        在创建新记录和修改现有记录的时候都对这个数据列刷新

    ④ ‘yyyy-mm-dd hh:mm:ss’ ON UPDATE CURRENT_TIMESTAMP

        在创建新记录的时候把这个字段设置为给定值，以后修改时刷新它

（2）查看 time_zone，即，时区设置：

​

（3）创建表，插入数据：

SHOW VARIABLES LIKE '%explicit_defaults_for_timestamp%';
SHOW VARIABLES LIKE '%time_zone%';

CREATE DATABASE test;
USE test;

CREATE TABLE IF NOT EXISTS `z_test` (
    `msg_dt` varchar(19) NOT NULL,
    `create_dt` datetime NOT NULL,
    `update_dt` TIMESTAMP NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试时间表示';

INSERT INTO z_test VALUES (now(), now(), now());
INSERT INTO z_test VALUES (unix_timestamp(now()), unix_timestamp(now()), unix_timestamp(now()));
INSERT INTO z_test VALUES ('2018-07-18 11:55:32', '2018-07-18 11:55:32', '2018-07-18 11:55:32');
INSERT INTO z_test VALUES (localtime, localtime, localtime);
INSERT INTO z_test VALUES (localtimestamp(), localtimestamp(), localtimestamp());

SELECT * FROM fspis_meta.z_test;

SET time_zone = '+4:00';

SELECT * FROM fspis_meta.z_test;

SET time_zone = 'SYSTEM';

UPDATE z_test SET msg_dt='plain text';

SELECT * FROM fspis_meta.z_test;

------------------------------------------------------------------------------
SET explicit_defaults_for_timestamp = 'ON';

-- 再做一遍上述操作

 

​

​

​

    可见，数据类型为TIMESTAMP 的 update_dt 字段被设置了默认值“CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”

（4）时区修改为“+4:00”后的查询结果：

​

（5）将时区还原为“SYSTEM”，即“+8:00”，更新 msg_dt 字段：

    由于 update_dt 字段的 default 值为 “CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”，于是自动更新。

​

​

---

（6）当 explicit_defaults_for_timestamp=true 时，实验结果：

​

​

​

    更新 msg_dt 字段：

    由于 update_dt 字段的 default 值为 null，此时更新记录并没有影响 update_dt 字段的值

​

​

知识点三、Django 中时间的使用

1、Python 中的时间函数

    import datetime, time, pytz 模块

    （1）datetime 模块提供：datetime、date、time、timezone、tzinfo、timedelta、struct_time 等类

        其中 datetime 类包含：now()、utcnow()、utcoffset()、tzname()、time()、timestamp()、date()、ctime() 等函数用于操作时间，datetime.now() 以数组的形式即 struct_time 表示时间；

    （2）time 模块提供：struct_time 等类；其中，time.time() 以时间戳的形式表示时间，time.localtime() 以数组的形式即 struct_time 表示时间；

    （3）pytz 提供：timezone() 等函数

    （4）时间格式化代码

代码	作用	代码	作用
%a	星期几的简写	%A	星期几的全称
%b	月分的简写	%B	月份的全称
%c	标准的日期的时间串	%C	年份的后两位数字
%d	十进制表示的每月的第几天	%D	月/天/年
%e	在两字符域中，十进制表示的每月的第几天	%F	年-月-日
%g	年份的后两位数字，使用基于周的年	%G	年分，使用基于周的年
%h	简写的月份名	%H	24小时制的小时
%I	12小时制的小时	%j	十进制表示的每年的第几天
%m	十进制表示的月份	%M	十时制表示的分钟数
%n	新行符	%p	本地的AM或PM的等价显示
%r	12小时的时间	%R	显示小时和分钟：hh:mm
%S	十进制的秒数	%t	水平制表符
%T	显示时分秒：hh:mm:ss	%u	每周的第几天，星期一为第一天 （值从0到6，星期一为0）
%U	第年的第几周，把星期日做为第一天（值从0到53）	%V	每年的第几周，使用基于周的年
%w	十进制表示的星期几（值从0到6，星期天为0）	%W	每年的第几周，把星期一做为第一天（值从0到53）
%x	标准的日期串	%X	标准的时间串
%y	不带世纪的十进制年份（值从0到99）	%Y	带世纪部分的十制年份
%z	，%Z 时区名称，如果不能得到时区名称则返回空字符。	%%	百分号

    Python 的 datatime.datetime对象有一个 tzinfo 属性，该属性是 datetime.tzinfo 子类的一个实例，他被用来存储时区信息。当某个 datetime 对象的 tzinfo 属性被设置并给出一个时间偏移量时，我们称该 datetime 对象是 aware (已知) 的。否则称其为 naive (原生) 的。 可以使用 is_aware() 和 is_naive() 函数来判断某个 datetime 对象是 aware 类型或 naive 类型。

2、Django Utils 中的时间函数

    import django.utils import timezone, tzinfo, datetime_safe 模块

    timezone 模块也提供了 datetime、tzinfo、timedelta 等类和 local() 等函数

    timezone.make_aware(datetime.datetime.now(), timezone.get_default_timezone()) 可以为 naive 类型的 datetime 添加时区属性

3、Django 默认关闭时区支持，开启时区支持，需要在 settings 中设置 USE_TZ = True 。最好同时安装 pytz 模块(pip install pytz) 。Django 的 settings.py 中与时间相关的设置

    TIME_ZONE = 'Asia/Shanghai'

    USE_TZ = True

    当设置了 TIME_ZONE 则 Django（Django 默认的 TIME_ZONE = 'America/Chicago' 或 system 时区）将使用指定的时区，它将影响 datetime.locale、now()等函数的返回值。

    当设置了 USE_TZ 为 True 时，Django 与其他系统或服务的交流将强制使用 UTC 时间。

    可能踩的坑：

    当 USE_TZ=True 时，把时间存储到数据库的时候 “INSERT INTO table_name VALUES('datetime_str' 或 datetime实例)” Django 将会把 'datetime_str' 和 datetime 实例转换为 UTC 时间。由于MySQL 的 datetime 类型字段对时区是无感知的，所以会直接存储由 Django 传递过去的 UTC 形式的时间。在中国，这个问题表现为存储到数据库里面的时间会晚8个小时。

    解决方法：dt.replace(tzinfo=pytz.utc)，也就是在存储前将 datetime 的时区信息改为 UTC。

def datetime_for_db(dt=None, dt_str=None):
    if dt:
        return dt.replace(tzinfo=pytz.utc)
    elif dt_str:
        try:
            dt = datetime.strptime(dt_str, '%Y-%m-%d %H:%M:%S')
            return dt.replace(tzinfo=pytz.utc)
        except Exception:
            raise Exception
    else:
        return datetime.now().replace(tzinfo=pytz.utc)

    一般不跨时区的应用，可以不使用时区，即在settings.py设置 USE_TZ=False

启用 USE_TZ = True 后，处理时间方面，有两条 “黄金法则”：

1. 保证存储到数据库中的是 UTC 时间；

2. 在函数之间传递时间参数时，确保时间已经转换成 UTC 时间；

比如，通常获取当前时间用的是：

  import datetime

  now = datetime.datetime.now()

启用 USE_TZ = True 后，需要写成：

  import datetime

  from django.utils.timezone import utc

  now = datetime.datetime.utcnow().replace(tzinfo=utc)

或：

  from django.utils import timezone

  now = timezone.now()

保证 now 变量存放的是 UTC 时间。

再如 fromtimestamp() 这个函数，启用 USE_TZ = True 后应使用 utcfromtimestamp() 函数替代。

附录

Django 官方网站对 timezone 的说明：

https://docs.djangoproject.com/en/1.11/topics/i18n/timezones/

参考链接：

https://blog.csdn.net/wy00703/article/details/45071277

https://blog.csdn.net/qq_37049781/article/details/79347278