Django使用MySQL存储时间中遇到的问题(在数据库中记录插入时间、更新...--688IT编程网

Django使⽤MySQL存储时间中遇到的问题（在数据库中记录插⼊时间、更新时

间、删除时间）

⼀、MySQL 的时间存储格式

⾸先，把MySQL的时间类型做⼀下解释。在mysql中，表⽰时间值的DATE和时间类型为 DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有⼀个有效值范围和⼀个“零”值，当指定不合法的 MySQL 不能表⽰的值时使

⽤“零”值。TIMESTAMP 类型有专有的⾃动更新特性。

1. DATE，⽇期。⽀持的范围为'1000-01-01'到'9999-12-31'。MySQL 以'YYYY-MM-DD'格式显⽰ DATE 值，但允许使⽤字符串或数字为 DATE 列分配值。

2. DATETIME，⽇期和时间的组合。⽀持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。MySQL 以'YYYY-MM-DD HH:MM:SS'格式显⽰ DATETIME 值，但允许使⽤字符串或数字为 DATETIME 列分配值。

3. TIMESTAMP[(M)]，时间戳。范围是'1970-01-01 00:00:00'到2037年。TIMESTAMP 列⽤于 INSERT

或 UPDATE 操作时记录⽇期和时间。如果你不分配⼀个值，表中的第⼀个 TIMESTAMP 列⾃动设置为最近操作的⽇期和时间。也可以通过分

配⼀个 NULL 值，将 TIMESTAMP 列设置为当前的⽇期和时间。TIMESTAMP 值返回后显⽰为'YYYY-MM-DD HH:MM:SS'格式的字符串，显⽰宽度固定为19个字符。如果想要获得数字值，应在 TIMESTAMP 列添加+0。

注释：MySQL 4.1以前使⽤的TIMESTAMP 格式在 MySQL 5.1中不⽀持；关于旧格式的信息参见 MySQL 4.1 参考⼿册。

4. TIME，时间。范围是'-838:59:59'到'838:59:59'。MySQL以'HH:MM:SS'格式显⽰ TIME 值，但允许使⽤字符串或数字

为 TIME 列分配值。

5. YEAR[(2|4)]，两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000。在两位格式中，允许的值是70到69，表⽰从1970年到2069年。MySQL 以 YYYY 格式显⽰ YEAR 值，但允许使⽤字符串或数字为YEAR 列分配值。

当 MySQL 遇到⼀个⽇期或时间类型的超出范围或对于该类型不合法的值时，它将该值转换为该类的“

零”值。⼀个例外是超出范围的 TIME 值被裁剪到 TIME 范围的相应端点。

下⾯的表显⽰了各类“零”值的格式(请注意如果启⽤ NO_ZERO_DATE SQL 模式，使⽤这些值会产⽣警告)。

列类型“零”值格式

DATETIME'0000-00-00 00:00:00'

DATE'0000-00-00'

TIMESTAME(M)0000000000000000

TIME'00:00:00'

YEAR0000

可以使⽤任何常见格式指定 DATETIME、DATE 和 TIMESTAMP 值：

1.'YYYY-MM-DD HH:MM:SS'或'YY-MM-DD HH:MM:SS'格式的字符串。允许“不严格”语法：任何标点符都可以⽤做⽇期

部分或时间部分之间的间割符。例如，'98-12-31 11:30:45'、'98.12.31 11+30+45'、'98/12/31 11*30*45'和'98@12@31

11^30^45'是等价的。

2.'YYYY-MM-DD'或'YY-MM-DD'格式的字符串。这⾥也允许使⽤“不严格的”语法。例如，'98-12-31'、'98.12.31'、

'98/12/31'和'98@12@31'是等价的。

3.'YYYYMMDDHHMMSS'或'YYMMDDHHMMSS'格式的没有间割符的字符串，假定字符串对于⽇期类型是有意义的。

例如，'19970523091528'和'970523091528'被解释为'1997-05-23 09:15:28'，但'971122129015'是不合法的(它有⼀个没有意义的分钟部分)，将变为'0000-00-00 00:00:00'。

4.数字值应为6、8、12或者14位长。如果⼀个数值是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS格

式，前4位数表⽰年。如果数字是6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式，前2位数表⽰年。其它数字被解释为仿佛⽤零填充到了最近的长度。

以下是对不同存储格式效率⽐较：

插⼊效率：datetime > timestamp > int

读取效率：int > timestamp > datetime

储存空间：datetime > timestamp = int

⼆、关于MySQL的TIMESTAMP

TIMESTAMP 列的显⽰格式与 DATETIME 列相同。换句话说，显⽰宽度固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。当 MySQL 服务器以 MAXDB 模式运⾏时，TIMESTAMP 与 DATETIME 相等。也就是说，如果创建表时服务器以 MAXDB 模式运⾏，TIMESTAMP 列创建为 DATETIME 列。结果是，该列使⽤ DATETIME 显⽰格式，有相同的值范围，并且没有⾃动对当前的⽇期和时间进⾏初始化或更新。

要想启⽤ MAXDB 模式，在启动服务器时使⽤ --sql-mode=MAXDB 服务器选项或在运⾏时通过设置全局 sql_mode 变量将 SQL 服务器模式设置为 MAXDB。

[sql] view plain copy

1. mysql> SET GLOBAL sql_mode=MAXDB；

客户端可以按照下⾯⽅法让服务器为它的连接以MAXDB模式运⾏：

[sql] view plain copy

1. mysql> SET SESSION sql_mode=MAXDB;

MySQL 不接受在⽇或⽉列包括⼀个零或包含⾮法⽇期值的时间戳值。该规则的唯⼀例外是特殊值'0000-00-00

00:00:00'。

下⾯的讨论只适⽤于创建时未启⽤ MAXDB 模式的表的 TIMESTAMP 列。(如前⾯所述，MAXDB 模式使列创建为DATETIME 列，不再讨论）。通过时间戳可以⾮常灵便地确定什么时候初始化和更新 TIMESTAMP 和对哪些列进⾏初始化、更新，可以将当前的时间戳指定为默认值或⾃动更新的值。但只能选择⼀个，或者两者都不选。(不可能⼀个列选择⼀个⾏为⽽另⼀个列选择另⼀个⾏为），此列不需要必须为第1个 TIMESTAMP 列。

控制 TIMESTAMP 列的初始化和更新的规则如下所⽰：

1.如果⼀个表内的第1个 TIMESTAMP 列指定为⼀个 DEFAULT 值，则不能忽略。默认值可以为

CURRENT_TIMESTAMP 或常量⽇期和时间值。

2.DEFAULT NULL 与第1个 TIMESTAMP 列的 DEFAULT CURRENT_TIMESTAMP 相同。对于其它 TIMESTAMP

列，DEFAULT NULL 被视为 DEFAULT 0。

3.表内的任何⼀个 TIMESTAMP 列可以设置为⾃动初始化为当前时间戳或更新。

另外，TIMESTAM 的显⽰值与数据库的时区设置有关，即其存储为标准时区的时间，读取时则通过数据库的时区信息来改变显⽰。

例如：

[sql] view plain copy

1. mysql> SHOW VARIABLES LIKE '%time_zone%';

2. mysql> SELECT @@global.time_zone, @@session.time_zone;

显⽰系统时区为 China Standard Time, 数据库系统时区为 System, 会话连接时区为 System。通过以下命令改变会话时区：

[sql] view plain copy

1. mysql> SET time_zone = '+0:00';

结果会发现以 TIMESTAMP 类型存储的时间改变显⽰为当前时区的时间，⽽以 DATETIME 存储的时间未有改变。

通过以下⽅式改变数据库系统全局时区：

[sql] view plain copy

1. vi myf

2. ......

3. [mysqld]

4. default-time-zone = '+00:00'

5. ......

mysql无法连接到服务器重启 MySQL 服务即可。

TIMESTAMP 与 DATETIME 类型列的存储均以当时会话的 time_zone 为准，即接收到的任何时间，均认为其时区是会话的 time_zone，TIMESTAM 并以此转化到 UTC 时间。

三、SQL实例

在 CREATE TABLE 语句中，可以⽤下⾯的任何⼀种⽅式声明 TIMESTAMP 列：

dos系统基本命令1.⽤ DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP ⼦句，列为默认值使⽤当前的时间戳，并且⾃动更新。

2.不使⽤ DEFAULT 或 ON UPDATE ⼦句，与 DEFAULT CURRENT_TIMESTAMP ON

UPDATECURRENT_TIMESTAMP 相同。

3.⽤ DEFAULT CURRENT_TIMESTAMP ⼦句不⽤ ON UPDATE ⼦句，列为默认值使⽤当前的时间戳但是不⾃动更新。

4.不⽤ DEFAULT ⼦句但⽤ ON UPDATE CURRENT_TIMESTAMP ⼦句，列有默认值0并⾃动更新。

5.⽤常量 DEFAULT 值，列有给出的默认值。如果列有⼀个 ON UPDATE CURRENT_TIMESTAMP ⼦句，它⾃动更新，否则不更新。

换句话说，你可以为初始值和⾃动更新的值使⽤当前的时间戳，或者其中⼀个使⽤，或者两个皆不使⽤。(例如，你可以指定 ON UPDATE 来启⽤⾃动更新⽽不让列⾃动初始化）。在 DEFAULT 和 ON UPDATE ⼦句中可以使⽤

CURRENT_TIMESTAMP、CURRENT_TIMESTAMP() 或者 NOW()。它们均具有相同的效果。两个属性的顺序并不重要。如果⼀个 TIMESTAMP 列同时指定了 DEFAULT 和 ON UPDATE，任何⼀个可以在另⼀个的前⾯。

例如，下⾯这些SQL语句时等效的：

[sql] view plain copy

1. CREATE TABLE ts1 (ts TIMESTAMP);

2. CREATE TABLE ts2 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

3. CREATE TABLE ts3 (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

要为 TIMESTAMP 列⽽不是第1列指定⾃动默认或更新，必须通过将第1个 TIMESTAMP 列显式分配⼀个常量 DEFAULT 值来禁⽤⾃动初始化和更新。(例如，DEFAULT 0 或 DEFAULT'2003-01-01 00:00:00')。然后，对于其它 TIMESTAM P列，规则与第1个 TIMESTAMP 列相同，例外情况是不能忽略 DEFAULT 和 ON UPDATE ⼦句。如果这样做，则不会⾃动进⾏初始化或更新。

例如，下⾯这些 SQL 语句时等效的：

[sql] view plain copy

1. CREATE TABLE ts4 (

2. ts1 TIMESTAMP DEFAULT 0,

3. ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

4. CREATE TABLE ts5 (

5. ts1 TIMESTAMP DEFAULT 0,

6. ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

TIMESTAMP 值以 UTC 格式保存，存储时对当前的时区进⾏转换，检索时再转换回当前的时区。只要时区设定值为常量，便可以得到保存时的值。如果保存⼀个 TIMESTAMP 值，应更改时区然后检索该值，它与你保存的值不同。这是因为在两个⽅向的转换中没有使⽤相同的时区。当前的时区可以⽤作 time_zone 系统变量的值。

可以在 TIMESTAMP 列的定义中包括 NULL 属性以允许列包含 NULL 值。例如：

[sql] view plain copy

1. CREATE TABLE ts6

2. (

3. ts1 TIMESTAMP NULL DEFAULT NULL,

4. ts2 TIMESTAMP NULL DEFAULT 0,

5. ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

6. );

如果未指定 NULL 属性，将列设置为 NULL 则会将它设置为当前的时间戳。请注意允许 NULL 值的 TIMESTAMP 列不会采⽤当前的时间戳，除⾮其默认值定义为 CURRENT_TIMESTAMP，或者 NOW() 或 CURRENT_TIMESTAMP 被插⼊到该列内。

换句话说，只有使⽤如下定义创建，定义为 NULL 的 TIMESTAMP 列才会⾃动更新：

[sql] view plain copy

1. CREATE TABLE ts7 (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP)；

否则，也就是说，如果使⽤ NULL ⽽不是 DEFAULT TIMESTAMP 来定义 TIMESTAMP 列，如下所⽰：

[sql] view plain copy

1. CREATE TABLE ts8 (ts TIMESTAMP NULL DEFAULT NULL);

2. CREATE TABLE ts9 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');

则必须显式插⼊⼀个对应当前⽇期和时间的值。例如：

[sql] view plain copy

1. INSERT INTO ts8 VALUES (NOW());

2. INSERT INTO ts9 VALUES (CURRENT_TIMESTAMP);

四、TIMESTAMP 中 NULL 属性的妙⽤——在⼀张表中设置 create time, update time 列

⼀个表中，有两个字段，create time 和 update time，当 insert 的时候，SQL两个字段都不设置，表中 create time 设置为当前的时间；当 update 的时候，SQL中两个字段都不设置，update time 会变更为当前的时间。

从上⾯的介绍中可以知道，⼀个表中⾄多只能有⼀个字段设置 CURRENT_TIMESTAMP，两列设置 DEFAULT CURRENT_TIMESTAMP 是不⾏的，所以在理论上⽆法实现上述需求。

其它地⽅可以看到有使⽤触发器、在 SQL 中设置好时间等⽅式，在此笔者利⽤上⼀节中所说的⼀个特性，来实现这⼀功能。

⾸先创建⼀个表，主要关⼼ create_at 和 update_at 列：

[sql] view plain copy

1. CREATE TABLE ts (

2. id INT(11) NOT NULL AUTO_INCREMENT,

3. dt_value datetime DEFAULT NULL,

4. create_at TIMESTAMP DEFAULT 0,

5. update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

6. delete_at TIMESTAMP NULL DEFAULT NULL,

7. PRIMARY KEY (id)

8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插⼊⼀条测试数据：

[sql] view plain copy

1. INSERT INTO ts (dt_value,create_at) VALUES (NOW(),NULL);

注意上⾯的 create_at 的给值时 NULL。

查询结果，如下图：

更新⼀条数据：

[sql] view plain copy

1. UPDATE ts SET dt_value=NOW() WHERE id = 1;

再次查询，如下图：

同理，对于delete_at列，也可以使⽤这样的⽅式设置：

[sql] view plain copy

1. UPDATE ts SET delete_at =NOW() WHERE id = 1;

查询结果，如下图：

⽐较⼀下上⾯⼏个图中各列时间戳值的变化可以发现时按照时间更新的，这样，就实现了在统⼀个表中设置多个TIMESTAMP 类型⾃动更新的功能。

五、Django 中使⽤ MySQL 存储时间遇到的问题

ps切图怎么切九宫格在此先说明⼀下，我所⽤的数据库时 CST 时区，与所在的服务器系统时间⼀致，Django 所在的 WEB 服务器也是统⼀台机器。在之前使⽤的过程中，从 Django 中获取到的 localtime 存储到数据库时会被系统⾃动处理增加8⼩时。针对这⼀问题，搜集了各⽅⾯的资料。下⾯就对 Django 的时区机制作个解释。

其实，Django 在配置⽂件 settings.py 中对时间时区有影响的是两个参数，⼀个是 TIME_ZONE，另⼀个是 USE_TZ。根据 USE_TZ 官⽹⽂档中的描述，这⼀属性默认值是 False。如果设置为 Ture，Django 内部将会使⽤对时区敏感的时间，否则 Django 将会使⽤系统本地的原有时间。（注意：为了⽅便，由 django-admin.py startproject 创建⽽来的项⽬ settings.py 中此项值设置为了 Ture）

bordercolor什么意思

与这⼀属性相关的还有 TIME_ZONE, USE_I18N 和 USE_L10N，下⾯我们来看⼀下这⼏个属性。

TIME_ZONE 官⽅⽂档中说，在 Django 1.4 以后的版本中，这⼀属性值的意义是由 USE_TZ 决定的。这并不是服务器必须的，因为⼀个服务器可以服务多个 Django 框架的服务，并拥有各⾃的时区。

⾸先说明⼀点，在开启了 Django 所有关于时区的设置之后，本来以为 Django 将会以 UTC 标准时区连接数据库，但是经笔者测试（在 Django 中引⼊ tion 连接做查询）发现实际连接的时区是数据库系统的全局设置。确认这⼀点⼗分关键，因为它事关全部时间数据的时区问题。

如下是做的⼀些测试以说明问题。

操作参数情形1情形2情形3情形4情形5

Django settings.py TIME_ZONE Asia/Shanghai Asia/Shanghai Asia/Shanghai Asia/Shanghai Asia/Sh

anghai USE_I18N TRUE TRUE TRUE TRUE TRUE

USE_L10N TRUE TRUE TRUE TRUE TRUE

USE_TZ TRUE False False TRUE False DATETIME_FORMAT Y-m-j H:i:s Z Y-m-j H:i:s Z Y-m-j H:i:s Z Y-m-j H:i:s Z Y-m-j H:i:s Z

database time_zone system_time_zone

China

Standard Time

China

Standard Time

China

Standard Time

China

Standard Time

China

Standard Time time_zone SYSTEM SYSTEM+00:00+00:00+00:00

存⼊time.strftime('%Y-%m-%d %X',

time.strftime('%Y-%m-%d %X',

time.localtime())

2014-08-27

23:43:19+08:00

2014-08-27

23:45:27

2014-08-27

15:48:56

2014-08-27

15:50:49+00:00

2014-08-27

15:50:49+00:00

在数据库中查询SET

time_zone = '+00:00'DATETIME

2014-08-27

15:43:19

2014-08-27

23:45:27

2014-08-27

15:48:56

2014-08-27

15:50:49

2014-08-27

15:50:49 TIMESTAMP

2014-08-27