Mysql⽇期时间类型详解
MySQL 中有多种数据类型可以⽤于⽇期和时间的表⽰，不同的版本可能有所差异，表3-2 中列出了MySQL 5.0 中所⽀持的⽇期和时间类型。
这些数据类型的主要区别如下：
* 如果要⽤来表⽰年⽉⽇，通常⽤DATE 来表⽰。
* 如果要⽤来表⽰年⽉⽇时分秒，通常⽤DATETIME 表⽰。
* 如果只⽤来表⽰时分秒，通常⽤TIME 来表⽰。
* 如果需要经常插⼊或者更新⽇期为当前系统时间，则通常使⽤TIMESTAMP 来表⽰。
TIMESTAMP 值返回后显⽰为“YYYY-MM-DD HH:MM:SS”格式的字符串，显⽰宽度固定为19 个字符。如果想要获得数字值，应在TIMESTAMP 列添加+0。
如果只是表⽰年份，可以⽤YEAR 来表⽰，它⽐DATE 占⽤更少的空间。YEAR 有2 位或4 位格式的年。默认是4 位格式。在4 位格式中，允许的值是1901～2155 和0000。在2 位格式中，允许的值是70～69，表⽰从1970～2069 年。MySQL 以YYYY 格式显⽰YEAR值。
从表3-2 中可以看出，每种⽇期时间类型都有⼀个有效值范围，如果超出这个范围，在默认的SQLMode 下，系统会进⾏错误提⽰，并将以零值来进⾏存储。不同⽇期类型零值的
表⽰如表3-3 所⽰。
  DATE、TIME 和DATETIME 是最常使⽤的3 种⽇期类型，以下例⼦在3 种类型字段插⼊了相同的⽇期值，来看看它们的显⽰结果：mysql帮助文档
  ⾸先创建表t，字段分别为date、time、datetime 三种⽇期类型：
  mysql> create table t (d date,t time,dt datetime);
  Query OK, 0 rows affected (0.01 sec)
  mysql> desc t;
  +-------+----------+------+-----+---------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +-------+----------+------+-----+---------+-------+
  | d | date | YES | | NULL | |
  | t | time | YES | | NULL | |
  | dt | datetime | YES | | NULL | |
  +-------+----------+------+-----+---------+-------+
  3 rows in set (0.01 sec)
  ⽤now()函数插⼊当前⽇期：
  mysql> insert into t values(now(),now(),now());
  Query OK, 1 row affected (0.00 sec)
  查看显⽰结果：
  mysql> select * from t;
  +------------+----------+---------------------+
  | d | t | dt |
  +------------+----------+---------------------+
  | 2007-07-19 | 17:41:13 | 2007-07-19 17:41:13 |
  +------------+----------+---------------------+
  1 row in set (0.00 sec)
  显⽽易见，DATETIME是DATE和TIME的组合，⽤户可以根据不同的需要，来选择不同的⽇期或时间类型以满⾜不同的应⽤。
  TIMESTAMP也⽤来表⽰⽇期，但是和DATETIME有所不同，后⾯的章节中会专门介绍。
  下例对TIMESTAMP类型的特性进⾏⼀些测试。
  创建测试表t，字段id1为TIMESTAMP类型：
  mysql> create table t (id1 timestamp);
  Query OK, 0 rows affected (0.03 sec)
  mysql> desc t;
  +-------+-----------+------+-----+-------------------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +-------+-----------+------+-----+-------------------+-------+
  | id2 | timestamp | YES | | CURRENT_TIMESTAMP | |
  +-------+-----------+------+-----+-------------------+-------+
  1 row in set (0.00 sec)
可以发现，系统给tm ⾃动创建了默认值CURRENT_TIMESTAMP（系统⽇期）。插⼊⼀个NULL值试试：
  mysql> insert into t values(null);
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from t;
  +---------------------+
  | t |
  +---------------------+
  | 2007-07-04 16:37:24 |
  +---------------------+
  1 row in set (0.00 sec)
果然，t中正确插⼊了系统⽇期。注意，MySQL只给表中的第⼀个TIMESTAMP字段设置默认值为系统⽇期，如果有第⼆个TIMESTAMP类型，则默认值设置为0值，测试如下：
  mysql> alter table t add id2 timestamp;
  Query OK, 0 rows affected (0.03 sec)
  Records: 0 Duplicates: 0 Warnings: 0
  mysql> show create table t \G;
  *************************** 1. row ***************************
  Table: t
  Create Table: CREATE TABLE `t` (
  `id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL default '0000-00-00 00:00:00'
  ) ENGINE=MyISAM DEFAULT CHARSET=gbk
  1 row in set (0.00 sec))
当然，可以修改id2的默认值为其他常量⽇期，但是不能再修改为current_timestmap，因为MySQL规定TIMESTAMP类型字段只能有⼀列的默认值为current_timestmap，如果强制修改，系统会报如下错误提⽰：
mysql> alter table t modify id2 timestamp default current_timestamp;
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with
CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
  TIMESTAMP还有⼀个重要特点，就是和时区相关。当插⼊⽇期时，会先转换为本地时区后存放；⽽从数据库⾥⾯取出时，也同样需要将⽇期转换为本地时区后显⽰。这样，两个不同时区的⽤户看到的同⼀个⽇期可能是不⼀样的，下⾯的例⼦演⽰了这个差别。
  （1）创建表t8，包含字段id1（TIMESTAMP）和id2（DATETIME），设置id2的⽬的是为了和id1做对⽐：
  CREATE TABLE `t8` (
  `id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `id2` datetime default NULL
  )
  Query OK, 0 rows affected (0.03 sec)
  （2）查看当前时区：
  mysql> show variables like 'time_zone';
  +---------------+--------+
  | Variable_name | Value |
  +---------------+--------+
  | time_zone | SYSTEM |
  +---------------+--------+
  1 row in set (0.00 sec)
    可以发现，时区的值为“SYSTEM”，这个值默认是和主机的时区值⼀致的，因为我们在中国，这⾥的“SYSTEM”实际是东⼋区
（+8:00）。
  （3）⽤now()函数插⼊当前⽇期：　
  mysql> select * from t8;
  +---------------------+---------------------+
  | id1 | id2 |
  +---------------------+---------------------+
  | 2007-09-25 17:26:50 | 2007-09-25 17:26:50 |
  +---------------------+---------------------+
  1 row in set (0.01 sec)
    结果显⽰id1 和id2 的值完全相同。
  （4）修改时区为东九区，再次查看表中⽇期：
  mysql> set time_zone='+9:00';
  Query OK, 0 rows affected (0.00 sec)
  mysql> select * from t8;
  +---------------------+---------------------+
  | id1 | id2 |
  +---------------------+---------------------+
  | 2007-09-25 18:26:50 | 2007-09-25 17:26:50 |
  +---------------------+---------------------+
  1 row in set (0.00 sec)
  结果中可以发现，id1 的值⽐id2 的值快了1 个⼩时，也就是说，东九区的⼈看到的“2007-09-25 18:26:50”是当地时区的实际⽇期，也就是东⼋区的“2007-09-25 17:26:50”，如果还是以
“2007-09-25 17:26:50”理解时间必然导致误差。
  TIMESTAMP的取值范围为19700101080001到2038年的某⼀天，因此它不适合存放⽐较久远的⽇期，下⾯简单测试⼀些这个范围：mysql> insert into t values (19700101080001);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+---------------------+
| t |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into t values (19700101080000);
Query OK, 1 row affected, 1 warning (0.00 sec
其中19700101080000 超出了tm 的下限，系统出现警告提⽰。查询⼀下，发现插⼊值变成了0 值。
  mysql> select * from t;
  +---------------------+
  | t |
  +---------------------+
  | 1970-01-01 08:00:01 |
  | 0000-00-00 00:00:00 |
  +---------------------+
  2 rows in set (0.00 sec)
再来测试⼀下TIMESTAMP 的上限值：
  mysql> insert into t values('2038-01-19 11:14:07');
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from t;
  +---------------------+
  | t |
  +---------------------+
  | 2038-01-19 11:14:07 |
  +---------------------+
  1 row in set (0.00 sec)
  mysql> insert into t values('2038-01-19 11:14:08');
  Query OK, 1 row affected, 1 warning (0.00 sec)
  mysql> select * from t;
  +---------------------+
  | t |
  +---------------------+
  | 2038-01-19 11:14:07 |
  | 0000-00-00 00:00:00 |
  +---------------------+
  2 rows in set (0.00 sec)
从上⾯例⼦可以看出，TIMESTAMP和DATETIME的表⽰⽅法⾮常类似，区别主要有以下⼏点。
TIMESTAMP⽀持的时间范围较⼩，其取值范围从19700101080001到2038年的某个时间，⽽DATETIME是从1000-01-01 00:00:00到9999-12-31 23:59:59，范围更⼤。
表中的第⼀个TIMESTAMP列⾃动设置为系统时间。如果在⼀个TIMESTAMP列中插⼊NULL，则该列值将⾃动设置为当前的⽇期和时间。在插⼊或更新⼀⾏但不明确给TIMESTAMP列赋值时也会⾃动设置该列的值为当前的⽇期和时间，当插⼊的值超出取值范围时，MySQL认为该值溢出，使⽤“0000-00-00 00:00:00”进⾏填补。
TIMESTAMP的插⼊和查询都受当地时区的影响，更能反应出实际的⽇期。⽽DATETIME则只能反应出插⼊时当地的时区，其他时区的⼈查看数据必然会有误差的。
TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很⼤，本章都是以MySQL5.0为例进⾏介绍，在不同的版本下可以参考相应的MySQL帮助⽂档。YEAR 类型主要⽤来表⽰年份，当应⽤只需要记录年份时，⽤YEAR ⽐DATE 将更节省空间。下⾯的例⼦在表t 中定义了⼀个YEAR 类型字段，并插⼊⼀条记录：
  mysql> create table t(y year);
  Query OK, 0 rows affected (0.01 sec)
  mysql> desc t;
  +-------+---------+------+-----+---------+-------+
  | Field | Type | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | y | year(4) | YES | | NULL | |
  +-------+---------+------+-----+---------+-------+
  1 row in set (0.00 sec)
  mysql> insert into t values(2100);
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from t;
  +------+
  | y |
  +------+
  | 2100 |
  +------+
  1 row in set (0.00 sec)
MySQL 以YYYY 格式检索和显⽰YEAR 值，范围是1901～2155。当使⽤两位字符串表⽰年份时，其范围为“00”到“99”。
“00”到“69”范围的值被转换为2000～2069 范围的YEAR 值
“70”到“99”范围的值被转换为1970～1999 范围的YEAR 值。
细⼼的读者可能发现，在上⾯的例⼦中，⽇期类型的插⼊格式有很多，包括整数（如2100）、字符串（如2038-01-19 11:14:08）、函数（如NOW()）等，⼤家可能会感到疑惑，到底什么样的格式才能够正确地插⼊到对应的⽇期字段中呢？下⾯以DATETIME 为例进⾏介绍。  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”和 11^30^45”是等价的。对于包括⽇期部分间割符的字符串值，如果⽇和⽉的值⼩于10，不需要指定两位数。“1979-6-9”与“1979-06-09”是相同的。同样，对于包括时间部分间割符的字符串值，如果时、分和秒的值⼩于10，不需要指定两位数。“1979-10-301:2:3”与“1979-10-30 01:02:03”相同。
YYYYMMDDHHMMSS 或YYMMDDHHMMSS 格式的没有间割符的字符串，假定字符串对于⽇期类型是有意义的。例
如，“19970523091528”和“970523091528”被解释为“1997-05-23 09:15:28”，但“971122129015”是不合法的（它有⼀个没有意义的分钟部分），将变为“0000-00-00 00:00:00”。
YYYYMMDDHHMMSS 或YYMMDDHHMMSS 格式的数字，假定数字对于⽇期类型是有意义的。例如，
19830905132800和830905132800被解释为“1983-09-05 13:28:00”。数字值应为6、8、12 或者14 位长。如果⼀个数值是8 或14 位长，则假定为YYYYMMDD 或YYYYMMDDHHMMSS 格式，前4 位数表⽰年。如果数字是6 或12位长，则假定为YYMMDD 或YYMMDDHHMMSS 格式，前2 位数表⽰年。其他数字被解释为仿佛⽤零填充到了最近的长度。
函数返回的结果，其值适合DATETIME、DATE 或者TIMESTAMP 上下⽂，例如NOW()或CURRENT_DATE。对于其他数据类型，其使⽤原则与上⾯的内容类似，限于篇幅，这⾥就不再赘述。
  最后通过⼀个例⼦，说明如何采⽤不同的格式将⽇期“2007-9-3 12:10:10”插⼊到DATETIME列中。
  mysql> create table t6(dt datetime);
  Query OK, 0 rows affected (0.03 sec)
  mysql> insert into t6 values('2007-9-3 12:10:10');
  Query OK, 1 row affected (0.00 sec)
  mysql> insert into t6 values('2007/9/3 12+10+10');
  Query OK, 1 row affected (0.00 sec)
  mysql> insert into t6 values('20070903121010');
  Query OK, 1 row affected (0.01 sec)
  mysql> insert into t6 values(20070903121010);
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from t6;
  +---------------------+
  | dt |
  +---------------------+
  | 2007-09-03 12:10:10 |
  | 2007-09-03 12:10:10 |
  | 2007-09-03 12:10:10 |
  | 2007-09-03 12:10:10 |
  +---------------------+
  4 rows in set (0.00 sec)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。