MySQL日期和时间类型笔记

  • 2019 年 10 月 13 日
  • 笔记

最近在看《MySQL技术内幕：SQL编程》并做了笔记，这是一篇笔记类型博客，分享出来方便自己复习，也可以帮助其他人

一、日期时间类型所占空间对比

各种日期时间数据类型所占的空间：

类型 所占空间
DATETIME 8字节
DATE 3字节
TIMESTAMP 4字节
YEAR 1字节
TIME 3字节

二、DATETIME和DATE对比

  • DATETIME占用8字节，既显示了日期也显示时间，可以表示的日期范围为“1000-01-01 00:00:00”到“9999-12-31 23:59:59”
  • DATE占用3字节，只显示日期，不显示具体时间，可显示的日期范围为“1000-01-01”到“9999-12-31”

ok，这里特意介绍一下TIMESTAMP秒的小数部分问题

备注：5.6.4+版本才支持秒的小数部分，之前版本是不支持的

# 查询MySQL版本  select version();  # 建表验证问题  create table t (a datetime);  # 写数据秒后面加上小数  insert into t select '2019-10-11 17:16:12.55555';  # 查询，发现并没有查出秒之后的小数  select * from t ;  # 使用microsecond，读取秒之后的小数  select microsecond('2019-10-11 17:16:12.55555') ;  # CAST读取，验证了5.7+版本查询时候会出现四舍五入，如下sql得到2019-10-11 17:16:12，而低版本就不会  SELECT CAST('2019-10-11 17:16:12.5555' AS DATETIME) ;  # CAST读取，5.7+版本查询，四舍五入得到，2019-10-11 17:16:12，低版本正常显示，具体哪个版本开始的不知道，我在5.7+版本验证都是会出现四舍五入的情况  select cast('2019-10-11 17:16:12.1234' as datetime) ;  # 5.6.4+版本支持秒的小数部分  # 支持的类型有TIME、DATETIME、TIMESTAMP，写法是type(size)，size为小数部分精度，最大为6  # 删表，再验证一下  DROP TABLE t;  # 这里指定精度  CREATE TABLE t (a DATETIME(4));  # 秒后加小数，写数据  INSERT INTO t SELECT '2019-10-11 17:16:12.55555';  # 查询，发现可以正常写数据，不过精度只有4  SELECT * FROM t ;

三、TIMESTAMP类型介绍

TIMESTAMP占4个字节，显示的范围为“1970-01-01”UTC 到 “2038-01-19 03:14:07”UTC
备注：UTC：协调世界时间、又称世界统一时间、世界标准时间和国际协调时间

注意：

  • 更新表时，可以设置TIMESTAMP类型的列，自动更新时间为当前时间
  • 列为TIMESTAMP的日期类型可以设置一个默认值，而DATETIME不支持

例子，验证一下，设置默认值和自动更新时间

# 新增一张表  CREATE TABLE t (    a INT,    b TIMESTAMP DEFAULT CURRENT_TIMESTAMP  ) ENGINE = INNODB ;  # 写一条数据  INSERT INTO t (a) VALUES (1);  # 查询，发现自动赋默认值时间  SELECT * FROM t;

在这里插入图片描述

验证自动更新时间问题

# 修改字段为自动更新(数据有改变时候才会自动更新)  ALTER TABLE t MODIFY COLUMN b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;  # 删一下表数据  DELETE FROM t;  # 写初始化数据  INSERT INTO t SELECT 1,CURRENT_TIMESTAMP;  # 查询，先记录下原来时间  SELECT * FROM t;

在这里插入图片描述

  # 修改数据  UPDATE t SET a =2;  # 如果修改为1，是不会改变时间的  SELECT * FROM t;

在这里插入图片描述

四、YEAR和TIME类型对比

  • YEAR类型占用1字节，书中介绍定义时可以指定显示的宽度为YEAR(2)或者YEAR(4)，不过在我的mysql版本5.7+验证，发现只能定义为YEAR或者YEAR(4)，也就是新版是不支持YEAR(2)这种形式的
    对于YEAR(4)，其显示年份的范围为1901~2155；然后超过这个范围的话，mysql还可以写，不过被赋值为0000
  • TIME类型只占3字节，显示的范围为“-838:59:59”~"838:59:59"，TIME的小时是可以大于23也可以为负值的，因为time也可以用来保存时间间隔

五、日期时间函数

  • NOW、CURRENT_TIMESTAMP和SYSDATE函数

介绍一下MySQL比较常用的NOW、CURRENT_TIMESTAMP和SYSDATE函数

给个例子，用sleep函数，然后对比sleep函数执行前后，这几个函数获取的时间对比

SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),SLEEP(2),NOW(),CURRENT_TIMESTAMP(),SYSDATE();

在这里插入图片描述
从图对比可知，NOW()其实就是CURRENT_TIMESTAMP()的近意函数，例子里使用了sleep(2)，隔2秒继续执行，可以对比得知，NOW、CURRENT_TIMESTAMP其实获取的都是整条sql开始执行的时间，不管在sleep函数执行前后，而SYSDATE获取的其实执行sysdate这个函数时候的时间，并非整条sql开始执行的时间，所以在sleep函数执行前后获取的时间是不同的

  • DATE_ADD和DATE_SUB函数

DATE_ADD(date , INTERVAL expr type)和DATE_SUB(datte , INTTERVAL expr type)，expr可以为负数，所以DATE_ADD既可以用于日期相加，也可以用于日期相减。type可以为YEAR、MONTH、DAY、HOUR、MINUTE、SECOND

SELECT NOW()AS NOW,DATE_ADD(NOW(),INTERVAL 1 DAY) AS tomorrow,DATE_SUB(NOW(),INTERVAL 1 DAY);

在这里插入图片描述

备注：闰月问题，如果是闰月就返回29日，不是闰月就返回28日

SELECT DATE_ADD('2004-2-29',INTERVAL 1 YEAR);  SELECT DATE_ADD('2004-2-29',INTERVAL 4 YEAR);

在这里插入图片描述
在这里插入图片描述

  • DATE_FORMAT函数
    DATE_FROMAT和Oracle中的to_char功能有点类型，作用是按照用户定义的格式打印数据
SELECT DATE_FORMAT(NOW(),'%Y%m%d');

VirMach 便宜 VPS

QNews

QNews