MySQL 时间类型 DATE、DATETIME和TIMESTAMP

1.DATE、DATETIME和TIMESTAMP 表达的时间范围

Type	Range	Remark
DATE	'1000-01-01' to '9999-12-31'	只有日期部分，没有时间部分
DATETIME	'1000-01-01 00:00:00' to '9999-12-31 23:59:59'	时间格式为 YYYY-MM-DD hh:mm:ss，默认精确到秒
TIMESTAMP	'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'UTC	默认精确到秒

2.DATETIME和TIMESTAMP 最大时间精确度

5.7 之后的版本（其实应该说5.6.5），在默认的秒精确度上，可以带小数，最多带6位小数，即可以精确到 microseconds (6 digits) precision。

Type	Range	Remark
DATETIME	'1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'	'YYYY-MM-DD hh:mm:ss[.fraction]'
TIMESTAMP	'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'	'YYYY-MM-DD hh:mm:ss[.fraction]'

 

3.DATETIME和TIMESTAMP 区别

（1） 时间范围不一样，TIMESTAMP 要小很多 ，且最大范围为2038-01-19 03:14:07.999999，到期也不远了。

（2）对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)
By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. 
As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, 
the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. 
The current time zone is available as the value of the time_zone system variable.

　可能上面的几句英文不好理解，我们举个例子。

创建2张测试表:

 create table testtime(id int,hiredate timestamp);

 create table testtime1(id int,hiredate datetime);

向这两个测试表中分别插入一笔测试数据

insert into testtime values(1,'20151208000000');

insert into testtime1 values(1,'20151208000000');

 

查看这种显示的时区时间设置

查询命令

 show variables like '%time_zone%'; 

 

上述“CST”指的是MySQL所在主机的系统时间，是中国标准时间的缩写，China Standard Time UT+8:00

修改time_zone

 set time_zone='+0:00';

通过结果可以看出，testtime中返回的时间提前了8个小时，而testtime1中时间则不变。

 如果新建一个客户端连接，这个时区的修改不影响新连接。

 

4.TIMESTAMP在新旧版本上的重大区别

TIMESTAMP 在mysql 5.6.5之后，TIMESTAMP（fraction）中的fraction代表的是小数位数，即默认秒，以秒为单位的小数点位数。 up to microseconds (6 digits) precision，最大为6.

超过6则报错：

ERROR 1426 (42000): Too-big precision 7 specified for 'hiredate'. Maximum is 6.

在比较久的版本上，这个数字就代表不同的意义，以下内容为旧版本的关于TIMESTAMP的知识。

TIMESTAMP（fraction）中fraction值显示尺寸的格式如下表所示：

列类型	显示格式
TIMESTAMP(14)	YYYYMMDDHHMMSS
TIMESTAMP(12)	YYMMDDHHMMSS
TIMESTAMP(10)	YYMMDDHHMM
TIMESTAMP(8)	YYYYMMDD
TIMESTAMP(6)	YYMMDD
TIMESTAMP(4)	YYMM
TIMESTAMP(2)	YY

就版本中“完整”TIMESTAMP格式是14位，但TIMESTAMP列也可以用更短的显示尺寸，创造最常见的显示尺寸是6、8、12、和14。

在创建表时可以指定一个任意的显示尺寸，但是定义列长为0或比14大均会被强制定义为列长14。列长在从1～13范围的奇数值尺寸均被强制为下一个更大的偶数。