MySQL日期和时间类型笔记

最近在看《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');

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券