一、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' |
| YEAR | 0000 |
可以使用任何常见格式指定 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。
mysql> SET GLOBAL sql_mode=MAXDB；客户端可以按照下面方法让服务器为它的连接以MAXDB模式运行：
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 的显示值与数据库的时区设置有关，即其存储为标准时区的时间，读取时则通过数据库的时区信息来改变显示。
例如：
mysql> SHOW VARIABLES LIKE '%time_zone%';
mysql> SELECT @@global.time_zone, @@session.time_zone;
显示系统时区为 China Standard Time, 数据库系统时区为 System, 会话连接时区为 System。 通过以下命令改变会话时区：
mysql> SET time_zone = '+0:00';
结果会发现以 TIMESTAMP 类型存储的时间改变显示为当前时区的时间，而以 DATETIME 存储的时间未有改变。
通过以下方式改变数据库系统全局时区：
vi my.cnf
......
[mysqld]
default-time-zone = '+00:00'
......
重启 MySQL 服务即可。
TIMESTAMP 与 DATETIME 类型列的存储均以当时会话的 time_zone 为准，即接收到的任何时间，均认为其时区是会话的 time_zone，TIMESTAM 并以此转化到 UTC 时间。
三、SQL实例
在 CREATE TABLE 语句中，可以用下面的任何一种方式声明 TIMESTAMP 列：
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语句时等效的：
CREATE TABLE ts1 (ts TIMESTAMP);
CREATE TABLE ts2 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
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 语句时等效的：
CREATE TABLE ts4 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE ts5 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
TIMESTAMP 值以 UTC 格式保存，存储时对当前的时区进行转换，检索时再转换回当前的时区。只要时区设定值为常量，便可以得到保存时的值。如果保存一个 TIMESTAMP 值，应更改时区然后检索该值，它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。当前的时区可以用作 time_zone 系统变量的值。
可以在 TIMESTAMP 列的定义中包括 NULL 属性以允许列包含 NULL 值。例如：
CREATE TABLE ts6
(
ts1 TIMESTAMP NULL DEFAULT NULL,
ts2 TIMESTAMP NULL DEFAULT 0,
ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
如果未指定 NULL 属性，将列设置为 NULL 则会将它设置为当前的时间戳。请注意允许 NULL 值的 TIMESTAMP 列不会采用当前的时间戳，除非其默认值定义为 CURRENT_TIMESTAMP，或者 NOW() 或 CURRENT_TIMESTAMP 被插入到该列内。
换句话说，只有使用如下定义创建，定义为 NULL 的 TIMESTAMP 列才会自动更新：
CREATE TABLE ts7 (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP)；
否则，也就是说，如果使用 NULL 而不是 DEFAULT TIMESTAMP 来定义 TIMESTAMP 列，如下所示：
CREATE TABLE ts8 (ts TIMESTAMP NULL DEFAULT NULL);
CREATE TABLE ts9 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
则必须显式插入一个对应当前日期和时间的值。例如：
INSERT INTO ts8 VALUES (NOW());
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 列：
CREATE TABLE ts (
id INT(11) NOT NULL AUTO_INCREMENT,
dt_value datetime DEFAULT NULL,
create_at TIMESTAMP DEFAULT 0,
update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
delete_at TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入一条测试数据：
INSERT INTO ts (dt_value,create_at) VALUES (NOW(),NULL);注意上面的 create_at 的给值时 NULL。
查询结果，如下图：
UPDATE ts SET dt_value=NOW() WHERE id = 1;再次查询，如下图：
同理，对于delete_at列，也可以使用这样的方式设置：
UPDATE ts SET delete_at =NOW() WHERE id = 1;查询结果，如下图：
比较一下上面几个图中各列时间戳值的变化可以发现时按照时间更新的，这样，就实现了在统一个表中设置多个 TIMESTAMP 类型自动更新的功能。
五、Django 中使用 MySQL 存储时间遇到的问题
在此先说明一下，我所用的数据库时 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）
与这一属性相关的还有 TIME_ZONE, USE_I18N 和 USE_L10N，下面我们来看一下这几个属性。
TIME_ZONE 官方文档中说，在 Django 1.4 以后的版本中，这一属性值的意义是由 USE_TZ 决定的。这并不是服务器必须的，因为一个服务器可以服务多个 Django 框架的服务，并拥有各自的时区。
首先说明一点，在开启了 Django 所有关于时区的设置之后，本来以为 Django 将会以 UTC 标准时区连接数据库，但是经笔者测试（在 Django 中引入 django.db.connection 连接做查询）发现实际连接的时区是数据库系统的全局设置。确认这一点十分关键，因为它事关全部时间数据的时区问题。
如下是做的一些测试以说明问题。
| 操作 | 参数 | 情形1 | 情形2 | 情形3 | 情形4 | 情形5 |
| Django settings.py | TIME_ZONE | Asia/Shanghai | Asia/Shanghai | Asia/Shanghai | Asia/Shanghai | Asia/Shanghai |
| 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.gmtime()) OR 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 07:43:19 | 2014-08-27 15:45:27 | 2014-08-27 15:48:56 | 2014-08-27 15:50:49 | 2014-08-27 15:50:49 | |
| 在 Django 中读取 | DATETIME | 2014-08-27 15:43:19+00: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 |
| TIMESTAMP | 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 | |
| 在 template 中显示 | DATETIME | 2014-8-27 23:43:19 28800 | 2014-8-27 23:45:27 28800 | 2014-8-27 15:48:56 28800 | 2014-8-27 23:50:49 28800 | 2014-8-27 15:50:49 28800 |
| TIMESTAMP | 2014-8-27 15:43:19 28800 | 2014-8-27 23:45:27 28800 | 2014-8-27 15:48:56 28800 | 2014-8-27 15:50:49 28800 | 2014-8-27 15:50:49 28800 |
由此可见，使用情形4的配置，设置 USE_TZ = Ture，数据库 time_zone = '+00:00'，时间设置为 time.strftime('%Y-%m-%d %X', time.gmtime())，可以加时区 '+00:00'（数据库不会关心），在数据库中采用 DATETIME 类型存储最为合适。
扫一扫
856
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
