MySQL 时间类型 DATE、DATETIME和TIMESTAMP

程序员文章站 2022-08-17 16:42:48

1.DATE、DATETIME和TIMESTAMP 表达的时间范围 2.DATETIME和TIMESTAMP 最大时间精确度 5.7 之后的版本（其实应该说5.6.5），在默认的秒精确度上，可以带小数，最多带6位小数，即可以精确到 microseconds (6 digits) precision。 ......

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'`	时间格式为 `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。

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');