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

TypeRangeRemark
DATE'1000-01-01' to '9999-12-31'只有日期部分,没有时间部分
DATETIME'1000-01-01 00:00:00' to '9999-12-31 23:59:59'时间格式为 *Y*YYY-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。

TypeRangeRemark
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.)<br>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. <br>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, <br>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. <br>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');

img

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

查询命令

 show variables like '%time_zone%';

img

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

修改time_zone

 set time_zone='+0:00';

img

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

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

img

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.

img

在比较久的版本上,这个数字就代表不同的意义,以下内容为旧版本的关于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范围的奇数值尺寸均被强制为下一个更大的偶数。