DATE、DATETIME 和 TIMESTAMP 类型
DATE
,DATETIME
和TIMESTAMP
类型都有关。本节描述了它们的特征,它们如何相似以及如何不同。MySQL的认识DATE
,DATETIME
以及TIMESTAMP
多种格式的值,在描述“日期和时间文字”。对于DATE
和DATETIME
范围描述,“ supported ”表示尽管较早的值可能有效,但不能保证。
该DATE
类型用于具有日期部分但没有时间部分的值。MySQL检索并DATE
以格式显示值。支持的范围是到。'YYYY-MM-DD'
'1000-01-01'
'9999-12-31'
该DATETIME
类型用于包含日期和时间部分的值。MySQL检索并DATETIME
以格式显示值。支持的范围是到。'YYYY-MM-DD hh:mm:ss'
'1000-01-01 00:00:00'
'9999-12-31 23:59:59'
该TIMESTAMP
数据类型被用于同时包含日期和时间部分的值。 UTC TIMESTAMP
的范围是UTC。'1970-01-01 00:00:01'
'2038-01-19 03:14:07'
甲DATETIME
或TIMESTAMP
值可以包括在高达微秒(6位)精度的后小数秒一部分。特别是,存储在而不是丢弃插入DATETIME
或TIMESTAMP
列中的值中的任何小数部分。与包括分数部分,这些值的格式,对于范围的值是到,而对于范围的值是到。小数部分应始终与其余时间用小数点分隔;没有其他小数秒定界符被识别。有关MySQL中小数秒支持的信息,请参见'YYYY-MM-DD hh:mm:ss[.fraction]'
DATETIME
'1000-01-01 00:00:00.000000'
'9999-12-31 23:59:59.999999'
TIMESTAMP
'1970-01-01 00:00:01.000000'
'2038-01-19 03:14:07.999999'
“时间值的分数秒”。
该TIMESTAMP
和DATETIME
数据类型提供自动初始化和更新到当前的日期和时间。有关更多信息,请参见“ TIMESTAMP和DATETIME的自动初始化和更新”。
MySQL将TIMESTAMP
值从当前时区转换为UTC进行存储,然后从UTC返回当前时区进行检索。(对于其他类型,例如DATETIME
。不会发生这种情况。)默认情况下,每个连接的当前时区是服务器的时间。可以在每个连接的基础上设置时区。只要时区设置保持不变,您将获得与存储相同的值。如果您存储一个TIMESTAMP
值,然后更改时区并检索该值,则检索到的值与您存储的值不同。发生这种情况是因为没有在两个方向上使用相同的时区进行转换。当前时区可作为time_zone
系统变量。有关更多信息,请参见“ MySQL服务器时区支持”。
从MySQL 8.0.19开始,您可以在向表中插入TIMESTAMP
和DATETIME
值时指定时区偏移量。偏移量被附加到datetime文字的日期部分,中间没有空格,并且使用与设置time_zone
系统变量相同的格式,但以下情况除外:
- 如果小时值小于10,则需要前导零。
- 该值
'-00:00'
被拒绝。 - 时区名称,例如
'EET'
和'Asia/Shanghai'
不能使用;'SYSTEM'
也不能在这种情况下使用。
本示例说明了使用不同设置将具有时区偏移量的datetime值插入TIMESTAMP
and DATETIME
列time_zone
,然后检索它们:
mysql>CREATE TABLE ts ( -> id INTEGER NOT NULLAUTO_INCREMENT PRIMARY KEY , -> col TIMESTAMP NOT NULL -> )AUTO_INCREMENT = 1; mysql>CREATE TABLE dt ( -> id INT NOT NULLAUTO_INCREMENT PRIMARY KEY , -> col DATETIME NOT NULL -> )AUTO_INCREMENT = 1; mysql>SET @@time_zone = 'SYSTEM'; mysql>INSERT INTO ts (col)VALUES ('2020-01-01 10:10:10'), -> ('2020-01-01 10:10:10 +05:30'), ('2020-01-01 10:10:10-08:00'); mysql>SET @@time_zone = ' +00:00'; mysql>INSERT INTO ts (col)VALUES ('2020-01-01 10:10:10'), -> ('2020-01-01 10:10:10 +05:30'), ('2020-01-01 10:10:10-08:00'); mysql>SET @@time_zone = 'SYSTEM'; mysql>INSERT INTO dt (col)VALUES ('2020-01-01 10:10:10'), -> ('2020-01-01 10:10:10 +05:30'), ('2020-01-01 10:10:10-08:00'); mysql>SET @@time_zone = ' +00:00'; mysql>INSERT INTO dt (col)VALUES ('2020-01-01 10:10:10'), -> ('2020-01-01 10:10:10 +05:30'), ('2020-01-01 10:10:10-08:00'); mysql>SET @@time_zone = 'SYSTEM'; mysql>SELECT @@system_time_zone; +-------------------- + | @@system_time_zone | +-------------------- + | EST | +-------------------- + mysql>SELECT col, UNIX_TIMESTAMP(col)FROM dtORDER BY id; +--------------------- +--------------------- + | col | UNIX_TIMESTAMP(col) | +--------------------- +--------------------- + | 2020 -01 -01 10:10:10 | 1577891410 | | 2019 -12 -31 23:40:10 | 1577853610 | | 2020 -01 -01 13:10:10 | 1577902210 | | 2020 -01 -01 10:10:10 | 1577891410 | | 2020 -01 -01 04:40:10 | 1577871610 | | 2020 -01 -01 18:10:10 | 1577920210 | +--------------------- +--------------------- + mysql>SELECT col, UNIX_TIMESTAMP(col)FROM tsORDER BY id; +--------------------- +--------------------- + | col | UNIX_TIMESTAMP(col) | +--------------------- +--------------------- + | 2020 -01 -01 10:10:10 | 1577891410 | | 2019 -12 -31 23:40:10 | 1577853610 | | 2020 -01 -01 13:10:10 | 1577902210 | | 2020 -01 -01 05:10:10 | 1577873410 | | 2019 -12 -31 23:40:10 | 1577853610 | | 2020 -01 -01 13:10:10 | 1577902210 | +--------------------- +--------------------- +
选择日期时间值时,即使插入时使用了偏移量,也不显示偏移量。
支持偏移值的范围是-14:00
到 +14:00
,包容性。
包含时区偏移量的Datetime文字被准备好的语句接受为参数值。
如果SQL模式允许此转换,则无效DATE
,DATETIME
或TIMESTAMP
值将转换为适当类型(或)的“零”值。确切的行为取决于是否启用了严格SQL模式和SQL模式中的哪一个;请参见“服务器SQL模式”。'0000-00-00'
'0000-00-00 00:00:00'
NO_ZERO_DATE
注意MySQL中日期值解释的某些属性:
MySQL允许对指定为字符串的值使用“宽松”格式,其中任何标点符号都可以用作日期部分或时间部分之间的分隔符。在某些情况下,这种语法可能是欺骗性的。例如,诸如的值由于
'10:11:12'
可能会看起来像时间值:
,但'2010-11-12'
如果在日期上下文中使用,则将其解释为年份。该值'10:45:15'
被转换为,'0000-00-00'
因为'45'
它不是有效的月份。在日期和时间部分与小数秒部分之间唯一识别的分隔符是小数点。
- 服务器要求月份和日期值有效,而不仅仅是分别在1到12和1到31范围内。禁用严格模式后,无效日期
'2004-04-31'
将转换为,'0000-00-00'
并生成警告。启用严格模式后,无效日期会产生错误。要允许这样的日期,请启用ALLOW_INVALID_DATES
。有关更多信息,请参见“服务器SQL模式”。 - MySQL不接受
TIMESTAMP
在day或month列中包含零的值或不是有效日期的值。唯一的例外是特殊的“零”值'0000-00-00 00:00:00'
,如果SQL模式允许该值。确切的行为取决于NO_ZERO_DATE
是否启用了严格SQL模式和SQL模式中的哪一个;请参见“服务器SQL模式”。 包含两位数年份值的日期是不明确的,因为世纪是未知的。MySQL使用以下规则解释两位数的年份值:
- 范围内的年值
00-69
变为2000-2069
。 - 范围内的年值
70-99
变为1970-1999
。
- 范围内的年值