mysql回退到某个时间_MySQL 中的日期时间类型

MySQL 中的日期时间类型

日期时间类型中包含以下几种数据类型:DATE

TIME https://dev.mysql.com/doc/refman/8.0/en/time.html

DATETIME

TIMESTAMP

YEAR https://dev.mysql.com/doc/refman/8.0/en/year.html

各类型都有具体的取值范围, 超出或非法的其他值时, MySQL 会回退到 0.TIMESTAMP 类型是个例外, 给它设置一个超出范围的值时, 将保存上该类型允许的最大值.

MySQL 按标准格式 YYYY-MM-DD hh:mm:ss[.fraction] 输出日期时间, 但设置或进行日期时间相关的比较时却支持灵活的多种格式, 会自动解析. 具体支持的输入格式可参见 Section 9.1.3, "Date and Time Literals". 其中 fraction 部分为秒后面的小数部分, 取值范围为 0~6 位.

虽然 MySQL 支持多种格式进行日期时间的设置, 但日期部分要求必须是 年 - 月 - 日 的形式才能正确解析. 比如 98-09-04 是按年月日顺序解析的, 而不是英文里常用的月日年, 或者日月年.

年在只给了两位数的情况下, MySQL 尝试使用以下规则来补全:

给定的两位数为 70~99 时解析成 1970 ~ 1999.

给定为 00 ~ 69 时解析成 2000 ~ 2069.

所以, 为了避免不可预测的结果, 使用时还是指定全一些.

在需要使用数字的语境下, MySQL 会将日期时间自动转成数字. 同理, 在需要日期时间的相关操作语境下, 会尝试将数字解析成日期时间.

通过设置 MySQL 相关参数, 日期类型可保存原本非法的值, 比如开启 ALLOW_INVALID_DATES 设置项时, 可设置日期类型保存一个 2009-11-31 值, 但正常情况下我们知道 11 月哪来什么 31 号. 此时 MySQL 仅仅只是不检查月分与日期的关联性, 但月分的取值范围 112 及日期的取值范围 131 还是要单独各自做校验的.MySQL>INSERT INTO todo(title,created_on)VALUES('blah','2019-09-31');

ERROR1292(22007):Incorrectdatevalue:'2019-09-31'forcolumn'created_on'at row1

MySQL>SET SESSION sql_mode='ALLOW_INVALID_DATES';

QueryOK,0rows affected(0.00sec)

MySQL>INSERT INTO todo(title,created_on)VALUES('blah','2019-09-31');

QueryOK,1row affected,1warning(0.01sec)

MySQL>SELECT*FROM todo;

+----+------+------------+

|id|title|created_on|

+----+------+------------+

|1|blah|2019-09-31|

+----+------+------------+

1rowsinset(0.00sec)

某些场景下你可能需要保存部分日期, 比如用户只输入了年没输入月日. 所以 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” ValueDATE'0000-00-00'

TIME'00:00:00'

DATETIME'0000-00-00 00:00:00'

TIMESTAMP'0000-00-00 00:00:00'

YEAR0000

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 https://en.wikipedia.org/wiki/Year_2038_problem . 关于该问题的讨论和解决可参见这个 Stack Overflow 的回答 https://stackoverflow.com/a/2012620/1553656 .

既然如此, 为何要使用这个取值范围更小的呢. TIMESTAMP 存储的值是带时区的. 在存储时会根据当前时区转成 UTC(universal time zone) 存储, 查询时也会根据时区从 UTC 转换到具体的时间. 对于支持多语及国际化全球部署的应用来说, 显得尤为方便. 需要注意的是, 这里操作基于的时区默认为服务器的时区, 可通过改变 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().11.3DateandTimeTypes

5.1.11ServerSQLModes

9.1.3DateandTimeLiterals

Year2038problem https://en.wikipedia.org/wiki/Year_2038_problem

ShouldIusethe datetimeortimestamp data typeinMySQL?

MySQLDatetimevsTimestampcolumn types-which one I shoulduse?

ss

来源: http://www.linuxidc.com/Linux/2019-05/158408.htm