「MySQL」- 日期时间类型

  CREATED BY JENKINSBOT

对于DATE和DATETIME范围说明,“supported”表示尽管较早的值可能正常工作,但不能保证。

类型 大小(B) 范围 显示格式 用途
YEAR[(4)] 1 0000,1901 ~ 2155 YYYY 年份值
TIME[(fsp)] 3 -838:59:59 ~ 838:59:59 HH:MM:SS[.fraction] 时间值或持续时间。允许使用字符串或数字将值进行插入。
TIMESTAMP[(fsp)] 4 UTC: 1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999 YYYYMMDD HHMMSS 混合日期和时间值,时间戳
DATETIME[(fsp)] 8 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999 YYYY-MM-DD HH:MM:SS[.fraction] 混合日期和时间值。允许使用字符串或数字将值进行插入。
DATE 4 1000-01-01 ~ 9999-12-31 YYYY-MM-DD 日期值。允许使用字符串或数字将值进行插入。

在MySQL中,TIME、DATETIME、TIMESTAMP支持小数秒,精度可以高达微秒(6位)。

关于fsp
要定义包含小数秒的列,使用语法type_name(fsp),其中type_name为TIME,DATETIME或TIMESTAMP,fsp为小数秒精度。fsp值(如果给出了)必须在0到6之间。值为0表示没有小数部分。如果省略,默认精度为0.(与标准SQL默认值6不同,用于于以前的MySQL版本兼容)。

表中的任何TIMESTAMPDATETIME列都可以具有自动初始化和更新属性。可以使用DEFAULT和ON UPDATE子句指定自动初始化更新到DATETIME列的当前日期和时间。

关于TIMESTAMP
TIMESTAMP值存储为自epoch(’1970-01-01 00:00:00’UTC)时代以来经过的秒数。
TIMESTAMP不能代表值“1970-01-01 00:00:00”,因为这相当于从时代开始的0秒,值0被保留以表示“0000-00-00 00:00:00”。
MySQL处理TIMESTAMP定义的方式取决于系统变量explicit_defaults_for_timestamp。
如果启用explicit_defaults_for_timestamp,则不会将DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP属性自动分配给任何TIMESTAMP列。它们必须明确地包含在列定义中。而且,没有明确声明为NOT NULL的任何TIMESTAMP都允许NULL值。
如果禁用explicit_defaults_for_timestamp,那么MySQL按照如下方式处理:

  • 除非另有规定,否则表中的第一个TIMESTAMP列被定义为自动设置为最近修改的日期和时间,如果未明确指定值。这使得TIMESTAMP可用于记录INSERT或UPDATE操作的时间戳。您也可以将TIMESTAMP列设置为当前日期和时间,方法是将其分配给NULL值,除非已使用NULL属性定义了NULL值。
  • 可以使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP列定义子句来指定自动初始化和更新到当前日期和时间。默认情况下,第一个TIMESTAMP列具有这些属性,如前所述。但是,可以将表中的任何TIMESTAMP列定义为具有这些属性。

关于YEAR类型
YEAR(2)数据类型已被弃用,并在MySQL 5.7.5中删除它的支持。要将YEAR(2)列转换为YEAR(4),可以查看Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”

其他注意事项
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;

可以在启用MAXDB SQL模式的情况下运行MySQL服务器。在这种情况下,TIMESTAMP与DATETIME相同。如果在创建表时启用了此模式,则TIMESTAMP列将创建为DATETIME列。因此,这些列使用DATETIME显示格式,具有相同的值范围,并且没有自动初始化或更新到当前日期和时间。参见Section 5.1.8, “Server SQL Modes”

字段默认值

可以为时间和日期字段设置默认值。

但这与具体的版本有关,不同的版本行为不同。

MySQL 5.5

不能将日期列的默认值设置为一个函数,比如NOW()或者CURRENT_DATE等等。

但是可以将CURRENT_TIMESTAMP作为TIMESTAMP列的默认值。

MySQL 5.6.5

可以将TIMESTAMPDATETIME列自动初始化,并更新为当前时间和日期。

还有另外一种用法:ON UPDATE,在更新该列时,自动更新为当前日期:

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

参考文献

Can’t default date to CURRENT_TIMESTAMP in MySQL 5.5
How do you set a default value for a MySQL Datetime column?
11.1.2 Date and Time Type Overview
11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME