MySQL⽇期和时间数据类型（DATE、TIME、DATETIME、
TIMESTAMP和YEAR）
⽬录
本⽂基于MySQL 5.7版本。
MySQL中表⽰时间值的⽇期和时间数据类型有以下五个：
（1）DATE
（2）TIME
（3）DATETIME
（4）TIMESTAMP
（5）YEAR
⽇期和时间数据类型语法
MySQL允许对TIME、DATETIME和TIMESTAMP使⽤⼩数秒，精度最⾼可达微秒（6位⼩数）。要定义包含⼩数秒⽇期和时间数据类型的列，使⽤
type_name(fsp)，
其中type_name是TIME、DATETIME或TIMESTAMP，⽽fsp是⼩数秒精度，fsp的值，如果给定，必须在0 ~ 6之间。值为0表⽰没有⼩数部分。如果省略，默认精度为0。
例如:
CREATE TABLE date_time_test (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));
Date
⽀持的范围 '1000-01-01' to '9999-12-31'，MySQL以'YYYY-MM-DD'格式显⽰Date类型，但允许使⽤字符串或数字将值赋给DATE列。字⾯常量转换规则如下：
（1）格式为'YYYY-MM-DD'或'YY-MM-DD'的字符串。任何标点字符都可以⽤作⽇期部分之间的分隔符。例如，'2012-12-31'、
'2012/12/31'、'2012^12^31'和'2012@12@31'是等价的。
（2）格式为'YYYYMMDD'或'YYMMDD'的没有分隔符的字符串，只要该字符串具有⽇期的意义。例如，'20070523'和'070523'被解释为'2007-05-23'，但'071332'是⾮法的(它有⽆意义的⽉和⽇部分)，会变成'0000-00-00'。
（3）作为YYYYMMDD或YYMMDD格式的数字，只要该数字作为⽇期有意义。例如，19830905和830905被解释为“1983-09-05”。
DATETIME和DATETIME(fsp)
DATETIME类型是Date和Time的组合。⽀持的范围从'1000-01-01 00:00:00.000000' 到'9999-12-31 23:59:59.999999'。MySQL以'YYYY-MM-DD hh:mm:ss[.fraction]'的格式显⽰DATETIME类型。但允许使⽤字符串或数字向DATETIME列赋值。字⾯常量转换规则如下：
表中的任何TIMESTAMP或DATETIME列都可以具有⾃动初始化和更新属性。
（1）格式为“YYYY-MM-DD hh:mm:ss”或“YY-MM-DD hh:mm:ss”字符串。任何标点字符都可以⽤作⽇期部分或时间部分之间的分隔符。例如，'2012-12-31 11:30:45'、'2012^12^31 11+30+45'、'2012/12/31 11*30*45'和'2012@12@31 11^30^45'是等价的。
（2）在⽇期和时间部分和⼩数秒部分之间识别的唯⼀分隔符是⼩数点。
（3）⽇期和时间部分可以⽤T⽽不是空格分隔。例如，'2012-12-31 11:30:45' '2012-12-31 t11:30:45 '是等价的。
（4）格式为'YYYYMMDDhhmmss'或'YYMMDDhhmmss'⽆分隔符的字符串，只要该字符串具有⽇期的意义。例
如，'20070523091528'和'070523091528'被解释为'2007-05-23 09:15:28'，但'0711********'是⾮法的(它有⼀个⽆意义的分钟部分)，会变成'0000-00-00 00:00:00'。
（5）YYYYMMDDhhmmss或YYMMDDhhmmss格式的数字，只要该数字作为⽇期有意义。例如，19830905132800和830905132800被解释为“1983-09-05 13:28:00”。
TIMESTAMP和TIMESTAMP[(fsp)]
mysql创建表数据类型⽀持的范围'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC（UTC指的是世界标准时
间），TIMESTAMP值存储为⾃纪元('1970-01-01 00:00:00' UTC)以来的秒数，但是它不能表⽰'1970-01-01 00:00:00'，因为这表⽰从纪元开始的0秒，值0保留⽤于表⽰“0000-00-00 00:00:00”，即“零”的TIMESTAMP值。TIMESTAMP的字⾯常量同DATETIME。
上⾯date_time_test表ts列是TIMESTAMP，⼀旦创建后，我们发现ts列默认值为当前TIMESTAMP，⽽且可⾃动更新为当前时间戳，如图：
向date_time_test表中插⼊⼀条数据，如下：
insert date_time_test(t) values('00:00:01');
查看数据，如图：
发现ts列⾃动更新为插⼊数据时的时间戳。
更新当前数据，如图：
update date_time_test set t='00:00:02' where t='00:00:01';
再次查看时间戳，如图：
ts列已经更新为当前时间戳。这和MySQL的系统变量explicit_defaults_for_timestamp有关，
show variables like 'explicit_defaults_for_timestamp';
如图：
explicit_defaults_for_timestamp处于关闭状态，此时，插⼊数据时，如果ts没有指定值，会被初始化为默认值，更新数据时，ts列同时更新。 explicit_defaults_for_timestamp处于打开状态，不会被初始化为默认，也不具备⾃动更新为当前时间戳。也可以对TIMESTAMP 和DATETIME如下显⽰定义默认
值和⾃动更新：
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
TIME和TIME(fsp)
⽀持的范围'-838:59:59.000000' to '838:59:59.000000'，MySQL以'hh:mm:ss[.fraction]'格式显⽰TIME，但允许使⽤字符串或数字向TIME列赋值。TIME类型这个时间范围表明它不仅可以表⽰⼀天的时间，也可以表⽰经过的时间或两个事件之间的时间间隔。字⾯常量转换规则如下：
（1）MySQL将带有冒号的TIME值缩写为⼀天中的时间，例如：'11:12'表⽰'11:12:00'，⽽不是 '00:11:12'；
（2）MySQL将没有冒号的TIME缩写值最右边的两个数字看成秒，例如：'1112'和1112都表⽰'00:11:12'
（3）在时间部分和⼩数秒部分之间识别的唯⼀分隔符是⼩数点；
（4）默认情况下，位于TIME范围之外但在其他⽅⾯有效的值将被裁剪到该范围中最近的端点。例如，'-850:00:00'和'850:00:00'会转换为'-838:59:59'和'838:59:59'。⽆效的TIME值将被转换为“00:00:00”。注意，由于'00:00:00'本⾝是⼀个有效的TIME值，因此⽆法从存储在表中的'00:00:00'值判断原始值是'00:00:00'还是⽆效的。
YEAR和YEAR(4)
⽀持的范围1901到2155，或者0000。MySQL以YYYY格式显⽰YEAR。但允许使⽤字符串或数字向YEAR列赋值。
SUM()和AVG()聚合函数不能处理时间值。要解决这个问题，请将其转换为数字，然后执⾏聚合操作，最后将其转换回时间值。例⼦:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;
YEAR接受如下格式的输⼊：
（1）4位字符串'1901' 到 '2155'；
（2）4位数字1901 到 2155；
（3）1或者2位字符串'0' 到 '99'，MySQL将'0'到'69'转化为2000到2069，将'70'到'99'转化为1970到1999；
（4）1或者2位数字0到 99，MySQL将1到69转换为YEAR值2001到2069，将70到99转化为YEAR值1970到1999，0转化为YEAR 值0000。
注：
如果没有启⽤strict SQL模式，MySQL会将⽆效的YEAR值转换为0000。在严格SQL模式下，试图插⼊⽆效的YEAR值将产⽣错误。
总结如下：
DATE TIME DATETIME TIMESTAMP YEAR
取值范围'1000-01-
01'
到
'9999-12-
31'
'-
838:59:59.000000'
到
'838:59:59.000000'
'1000-01-01
00:00:00.000000' 到
'9999-12-31
23:59:59.999999'
'1970-01-01
00:00:01.000000'
UTC to
'2038-01-19
03:14:07.999999'
UTC
1901
到
2155
或者
0000
⼩数位精度00~60~60~60
显⽰格式YYYY-MM-
DD
hh:mm:ss[.fraction]
YYYY-MM-DD
hh:mm:ss[.fraction]
YYYY-MM-DD
hh:mm:ss[.fraction]
YYYY
赋值允许使⽤符合
格式的字符串
或数字赋值
允许使⽤符合格式的字符
串或数字赋值
允许使⽤符合格式的字符串或
数字赋值
允许使⽤符合格式的字符
串或数字赋值
允许使
⽤符合
格式的
字符串
或数字
赋值
⼩数位分隔符⽆只识别⼩数点.只识别⼩数点.只识别⼩数点.⽆⽇期分隔符任何标点⽆任何标点任何标点⽆时间分隔符⽆任何标点任何标点任何标点⽆
⽆效值处理（strict mode关闭状态）转化为
0000-00-
00
转化为
0000-00-00
00:00:00
转化为
0000-00-00 00:00:00
转换为
00:00:00
转换为
0000

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