MySQL中日期和时间类型

时间:2021-07-24 18:06:46

1 日期类型

MySql中关于日期的类型有Date/Datetime/Timestamp三种类型。

日期赋值时,允许“不严格”语法:任何标点符都可以用做日期部分或时间部分之间的间割符。例如,'98-12-31 11:30:45'、'98.12.31 11+30+45'、'98/12/31 11*30*45'和'98@12@31 11^30^45'是等价的,对于不合法的将会转换为:0000-00-00 00:00:00

 

1.1 Date格式

此类型的字段,存储数据格式为:YYYY-MM-DD,它支持的范围为'1000-01-01'到'9999-12-31',并且允许使用字符串或数字为此列复制。

例如,日期格式的字段crt_date,通过字符串和数字复制后的结果如下:

update sys_tables set crt_date = 12331212 where table_name = 'sys_dict';
update sys_tables set crt_date = '1233-12-12' where table_name = 'sys_dict_item';

MySQL中日期和时间类型

1.2 Datetime格式

日期和时间的组合,存储格式为:YYYY-MM-DD HH:MM:SS,它支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59',并且允许使用字符串或数字进行分配值。

例如:

update sys_tables set crt_date = 12331212121212 where table_name = 'sys_dict';
update sys_tables set crt_date = '1233-12-12 12:12:12' where table_name = 'sys_dict_item';

MySQL中日期和时间类型

 

1.3 TimeStamp格式

这是时间戳,从1970-01-01 00:00:00到当前的时间差值(注意:当你在Java中输出[new Date(0)] 的时候,输出的是:Thu Jan 01 08:00:00 CST 1970,这是因为时区的概念,中国是东八区,所以对应的是早上八点),它精确到毫秒级别,范围为:1970-01-01 00:00:00 到 2037年(参考2038年问题),当值大于2037年就会抛出: [Error Code: 1292, SQL State: 22001]  Data truncation: Incorrect datetime value: '20381212121212' for column 'crt_date' at row 1。设置值时只允许设置数字类型的值。

在为TimeStamp类型字段赋值的时候,值必须大于19700101000000,否则就会抛出: [Error Code: 1292, SQL State: 22001]  Data truncation: Incorrect datetime value: '10831212121212' for column 'crt_date' at row 1。

例如:

update sys_tables set crt_date = 20081212121212 where table_name = 'sys_dict';
MySQL中日期和时间类型

2 时间类型

MySQL中时间类型用Time表示。

2.1 Time格式

MySQL以'HH:MM:SS'格式检索和显示TIME值(或对于大的小时值采用'HHH:MM:SS'格式)。TIME值的范围可以从'-838:59:59'到'838:59:59'。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时),还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时,或者甚至为负)。

格式说明:

  • 'D HH:MM:SS.fraction'格式的字符串。还可以使用下面任何一种“非严格”语法:'HH:MM:SS.fraction'、'HH:MM:SS'、'HH:MM'、'D HH:MM:SS'、'D HH:MM'、'D HH'或'SS'。这里D表示日,可以取0到34之间的值。请注意MySQL还不保存分数。
  • 'HHMMSS'格式的没有间割符的字符串,假定是有意义的时间。例如,'101112'被理解为'10:11:12',但'109712'是不合法的(它有一个没有意义的分钟部分),将变为'00:00:00'。
  • HHMMSS格式的数值,假定是有意义的时间。例如,101112被理解为'10:11:12'。下面格式也可以理解:SS、MMSS、HHMMSS、HHMMSS.fraction。请注意MySQL还不保存分数。
  • 函数返回的结果,其值适合TIME上下文,例如CURRENT_TIME。
  • 超出TIME范围但合法的值被裁为范围最接近的端点。例如,'-850:00:00'和'850:00:00'被转换为'-838:59:59'和'838:59:59'。

3 年类型

MySQL中用year表示年类型

MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。

可以指定各种格式的YEAR值:

  • 四位字符串,范围为'1901'到'2155'。
  • 四位数字,范围为1901到2155。
  • 两位字符串,范围为'00'到'99'。'00'到'69'和'70'到'99'范围的值被转换为2000到2069和1970到1999范围的YEAR值。
  • 两位整数,范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同,因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'或它被解释为0000。
  • 函数返回的结果,其值适合YEAR上下文,例如NOW()。
  • 非法YEAR值被转换为0000。

4 日期兼容性问题

  1. MySQL服务器采用了Unix的时间功能,对于TIMESTAMP值,可处理的日期至2037年。对于DATE和DATETIME值,可接受的日期可至9999年。
  2. 所有的MySQL日期函数均是在1个源文件sql/time.cc中实现的,并经过了恰当编码以确保2000年安全。
  3. 在MySQL 3.22和以后的版本中,YEAR列类型能够在1个字节内保存0年以及1901~2155年,并能使用两位或四位数字显示它们。所有的两位数字年份均被视为介于1970~2069年之间,这意味着,如果你在YEAR列中保存了01,MySQL服务器会将其当作2001年。