当前位置：Gxlcms > 数据库问题 > MySQL 中的日期时间类型

MySQL 中的日期时间类型

时间：2021-07-01 10:21:17 帮助过：38人阅读

INSERT INTO todo (title,created_on) VALUES (‘blah‘,‘2019-09-31‘); ERROR 1292 (22007): Incorrect date value: ‘2019-09-31‘ for column ‘created_on‘ at row 1 mysql> SET SESSION sql_mode = ‘ALLOW_INVALID_DATES‘; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO todo (title,created_on) VALUES (‘blah‘,‘2019-09-31‘); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT * FROM todo; +----+------+------------+ | id | title | created_on | +----+------+------------+ | 1 | blah | 2019-09-31 | +----+------+------------+ 1 rows in set (0.00 sec)

某些场景下你可能需要保存部分日期，比如用户只输入了年没输入月日。所以 MySQL 是支持将月日设置成 0，比如 2019-00-00。但这种情况下就无法从日期相关的操作中获得到准确的结果，比如使用 DATE_SUB() 或 DATE_ADD() 函数时。禁用月日的零值可通过开启 MySQL 的 NO_ZERO_IN_DATE 模式。

除了月日可零，MySQL 还支持设置年月日都零的值 0000-00-00，对于日期非必填的情况比较有用，因为此时它比单纯的 NULL 更有语义。可通过开启 MySQL 的 NO_ZERO_DATE 模式来禁用这个全零的值。

各日期时间零值格式如下，但实际时用时，直接简写成一个 0 效果是等效的。

Data Type	“Zero” Value
DATE	‘0000-00-00‘
TIME	‘00:00:00‘
DATETIME	‘0000-00-00 00:00:00‘
TIMESTAMP	‘0000-00-00 00:00:00‘
YEAR	0000

DATE，DATETIME，及 TIMESTAMP

三者具有相关性，都支持多种格式的自动解析，详见 Date and Time Literals。

DATE 日期格式不带时间 TIME 部分，查询时输出格式为 YYYY-MM-DD，取值范围为 1000-01-01 到 9999-12-31。

DATETIME 包含日期及时间，输出格式为 YYYY-MM-DD hh:mm:ss，取值范围 1000-01-01 00:00:00 到 9999-12-31 23:59:59。

TIMESTAMP 同 DATETIME，但取值范围基于 UTC 时间，较 DATETIME 要小，为 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。所以使用 TIMESTAMP 格式的时间，到 2038 年会溢出，这就是 Year 2038 problem。关于该问题的讨论和解决可参见这个 StackOverflow 的回答。

既然如此，为何要使用这个取值范围更小的呢。TIMESTAMP 存储的值是带时区的。在存储时会根据当前时区转成 UTC（universal time zone）存储，查询时也会根据时区从 UTC 转换到具体的时间。对于支持多语及国际化全球部署的应用来说，显得尤为方便。需要注意的是，这里操作基于的时区默认为服务器的时区，可通过改变 time_zone SET GLOBAL time_zone=time_zone 来修改。时区的设置也可以是以连接为单位，这样来自不同时区的请求可得到不同的时间。

TIMESTAMP 和 DATETIME 都可包含至多 6 位的小数来表示时间中毫秒（microseconds）的部分。所以，带上毫秒时完整的格式是 YYYY-MM-DD hh:mm:ss[.fraction]。前者取值范围为 1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999，后者为 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999。

在写入时，对于非法的日期时间值，将自动存成零值，即 ‘0000-00-00‘ 或 ‘0000-00-00 00:00:00‘。

关于日期时间需要注意的点：

因为 MySQL 支持比较宽松的格式来设置日期时间，所以理论上你可以用你想用的值来做为数字之间的分界符，但使用时需要关注其解析的原理。比如给一个日期格式的列设置 10:11:12，虽然这个值看起来像时间类型，但还是可以正确在被解析成目标列的格式，即日期。如果这这个日期列设置 10:45:15 则会认为是非法值，因为 45 不是一个合法的月份值，所以存储时变成零值 0000-00-00。
日期时间与毫秒的分界符必需是小数点。
默认 MySQL 除了检查日月值是否有有效范围 1~ 31,1~12。还会将两者结合进来检查，比如 4 月没有 31。所以对于日期 2004-04-31 算是非法的，会变成零值 0000-00-00。如果不需要这样的约束检查，可开启 MySQL 的 ALLOW_INVALID_DATES 模式。

日期时间的自动初始化及更新

TIMESTAMP 和 DATETIME 还支持自动初始化（auto-initialized）和更新到当前时间（auto-updated）。

创建表定义列时，指定 DEFAULT CURRENT_TIMESTAMP 来使相应的日期时间列自动初始化。
指定 ON UPDATE CURRENT_TIMESTAMP 来使相应的日期时间列自动更新。

两者可同时作用于一个日期时间列，表示插入记录时自动初始化成当前时间，后续记录更新时自动更新到当前时间。

其中 CURRENT_TIMESTAMP 指代当前时间，与其有相同效果的还有 CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP 以及 LOCALTIMESTAMP()。

DEFAULT 除了可指定成当前时间外，也可指定一个任意的固定值，比如 DEFAULT 0 或 `DEFAULT ‘2000-01-01 00:00:00‘。

对于指定了自动初始化的列，插入时如果没指定该列的值，则会自动设置为当前的时间。

对于指定为自动更新的列，一旦一条记录中有字段变更，该日期会自动更新成变更时的时间。如果不想它更新，可在插入其他值时手动设置一下该日期列为原有的值，让其保持不变。

TIMESTAMP 和 DATETIME 在列的定义时，如果指定了小数部分，那么在配合使用 CURRENT_TIMESTAMP(fsp) 时，这个小数部分的精度需要保持一致。比如：

-- ?
CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

-- ??
CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

TIME

时间 TIME 格式即日期时间中时间的部分，输出格式为 hh:mm:ss 或时间较大时为 hhh:mm:ss，取值范围 -838:59:59 到 838:59:59。同样地，也是支持带至多 6 位小数表示毫秒。

设置时也是支持将多种格式自动解析。对于带冒号的情况，比如 11:12 解析成 11:12:00 而不是 00:11:12。不带冒号的情况，将最右边的两位数字解析成秒（按逝去的时间来解析），比如 ‘1112‘ 和 1112 不是 11:12:00 而会解析成 00:11:12。同理，‘12‘ 和 12 会解析成 00:00:12。

YEAR

YEAR 表示日期中年的部分，是一个 1 字节大小的类型，可通过 YEAR 或 YEAR(4) 来声明，其展示宽度（display width）为 4。查询时输出格式为 YYYY，取值范围 1901 到 2155。 0000 也是合法的值。

支持使用以下格式进行设置：

使用 1901 ~ 2155 间的四位数字值。
或将上面的数字以字符串形式给定。
1 ~ 99 之间的数字，此时 1 ~ 69 解析成 2001 ~ 2069，70 ~ 99 解析成 1970 ~ 1999.
其他可返回合法值的方法，比如 NOW()。

MySQL 中的日期时间类型

DATE，DATETIME，及 TIMESTAMP

日期时间的自动初始化及更新

TIME

YEAR

相关资源

人气教程排行