linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

MySQL 中的日期时间类型

时间:2019-05-10  来源:未知  作者:admin666
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 格式即日期时间中时间的部分,输出格式为 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 表示日期中年的部分,是一个 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.3 Date and Time Types 5.1.11 Server SQL Modes 9.1.3 Date and Time Literals Year 2038 problem Should I use the datetime or timestamp data type in MySQL? MySQL Datetime vs Timestamp column types which one I should use?

ss

linux Oracle 分页查询与数据去重实例 理解 MySQL 中的数字类型
友情链接
  • JupyterHub 1.0 发布,命名服务器与TLS加密支持
  • Linux Kernel 5.1 正式发布,大量新功能
  • Red Hat Enterprise Linux 8.0(RHEL8)正式发布了
  • RStudio 1.2发布,改进对Python chunks,R脚本等的测试和支持
  • Firefox 66.0.4 发布,解决由于证书过期而导致的插件损坏问题
  • Ubuntu 14.04(Trusty Tahr)达到生命终点,现在升级到Ubuntu 18
  • 微软宣布推出“Windows Terminal”,受Linux风格启发终端
  • 印度将于5月成立国防网络局
  • Linux Mint 17达到了生命终结,用户需要升级到Linux Mint 18或19
  • Btrfs在Linux 5.2内核中获得一些有用的修复/改进
  • Unigine 2.8 发布,更好的植被与异步数据流
  • macOS版本的Microsoft Edge 浏览器下载地址泄露
  • GNU Linux
  • Linux Kernel 5.1 应该会在今天发布,更快的zRAM
  • Linux版的Microsoft Edge浏览器可能在工作中
  • Ubuntu Linux现在支持微软的第二代Windows Linux子系统(WSL 2 )
  • Visual Studio Code添加Java 12支持,Java代码操作和语言功能
  • 微软宣布推出Windows Subsystem for Linux 2(WSL 2)
  • Ubuntu 19.10代号为“Eoan Ermine”,将于10月17日发布
  • Brackets 1.14 发布,LSP支持(Ubuntu下安装方法)
  • 微软为Windows 10带来原生Linux内核,将成为WSL 2的一部分
  • 2019年GitHub上最受欢迎的7种编程语言
  • Linux 5.2应该可以解决许多AMD Ryzen笔记本电脑触摸屏/触摸板无
  • 适用于Linux的最佳Photoshop替代品
  • 谈判失败:Oracle 杀死 Java EE
  • DXVK 1.1.1 发布,解决召回的DXVK 1.1问题
  • Ant Design 3.17.0 发布,企业级UI设计语言
  • 音乐播放器 Qmmp 1.3.2 发布,修复Wayland会话问题
  • Linux新闻 第970页
  • Oracle 经典面试题分享
  • Oracle 中的SELECT 关键字(查询、检索)
  • Linux Kernel 5.1正式版发布,非长期支持版本
  • 微软在Windows 10中搞了个真正的Linux内核
  • 已是9102年,你熟知的C#还是你刚认识的模样吗?
  • 微软宣布.NET 5计划,支持跨平台、移动开发
  • PingCAP宣布TiDB Binlog组件正式开源
  • 知名Python库Requests作者Kenneth Reitz被指骗捐
  • Google将推出自动删除用户数据的功能
  • 谈判失败:Oracle杀死Java EE
  • 如何在Linux中将命令输出保存到文件
  • Python面向对象之单例模式
  • Ubuntu 18.04 Linux上安装Etherpad,基于Web的实时协作编辑器
  • Python3使用TCP编写一个简易的文件下载器
  • Slimbook - Ubuntu的电池优化工具
  • Python Numpy 数组的基本操作示例
  • 全盛娱乐游戏每天送6元
  • 现金娱乐平台
  • 真钱炸金花
  • 澳门威尼斯人度假村酒店
  • 3d之家