专栏首页MYSQL轻松学MySQL时间加减的正确打开方式

MySQL时间加减的正确打开方式

1背景介绍

业务会有这样的需求:时间字段需要加1或减1秒。 研发sql:update table set time = time + 1 where id=1; 看似好像挺对的,但是偶尔会出现不是想要的结果。

2模拟测试

新建一个表test1,有3条记录如下,执行+1操作:

CREATE TABLE `test1` (

  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Type` smallint(6) DEFAULT '0',
  `Status` smallint(6) DEFAULT '0',
  `CreateTime` datetime DEFAULT NULL,
  `ModifyTime` timestamp DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


> select CreateTime,ModifyTime from test1;
+-+-------------+-------------+
| Id | CreateTime      | ModifyTime       |
+-+-------------+-------------+
|  1 | 2017-08-01 18:30:59 | 2017-08-01 18:30:59 |
|  2 | 2017-08-01 18:31:01 | 2017-08-01 18:31:01 |
|  3 | 2017-08-01 18:31:02 | 2017-08-01 18:31:02 |
+-+-------------+-------------+


> update test1 set CreateTime=CreateTime+1,ModifyTime=ModifyTime+1;


> select * from test1;
+-+-------------+-------------+
| Id | CreateTime      | ModifyTime       |
+-+-------------+-------------+
|  1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  2 | 2017-08-01 18:31:02 | 2017-08-01 18:31:02 |
|  3 | 2017-08-01 18:31:03 | 2017-08-01 18:31:03 |
+-+-------------+-------------+

测试后我们看到59秒的时候加1秒全部变成了0000-00-00 00:00:00,而其他是正确的,此时我们会觉得是不是跟逢整进位有关系,59秒的时候再加上1秒进位1分钟,结果却变成了0000-00-00 00:00:00,这是为什么?

继续测试:

> update test1 set CreateTime=CreateTime+55,ModifyTime=ModifyTime+105;
> select CreateTime,ModifyTime from test1;

+---------------------+---------------------+
| CreateTime            | ModifyTime          |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 2000-01-05 00:00:00 |
| 2017-08-01 18:31:57 | 2017-08-01 18:32:07 |
| 2017-08-01 18:31:58 | 2017-08-01 18:32:08 |
+---------------------+---------------------+

CreateTime+55,ModifyTime+105后,并不是我们想的逢整进位的关系。

3问题分析

> select ModifyTime from test1 limit 1;                      

+----------------+             
| ModifyTime            |             
+----------------+             
| 2017-08-01 18:30:59 |             
+----------------+   

> update test1 set ModifyTime = ModifyTime + <n>; 

其实只要我们知道datatime类型以'YYYY-MM-DD HH:MM:SS'的形式来显示的,就知道原因了。 例如: n=61,会转换成 '0000-00-00 00-00-61'; n=101,会转换成 '0000-00-00 00-01-01'; n=65535,会转换成 '0000-00-00 06-55-35'; 因为秒只能是0~59,不会有大于59秒的时候存在,如果大于59属于异常,会初始化成'0000-00-00 00-00-00'状态。分钟也一样。 所以如果此时秒正好为0: 当1<=n<60时,可以正常相加; 当60<=n<100时,超过59秒属于异常,初始化成'0000-00-00 00-00-00'; 当n=100时,会转换成 '0000-00-00 00-01-00',也就是1分钟,如果此时为59分,也会初始化成'0000-00-00 00-00-00'; 以此类推,所以并不是所有的都会成功,也不是所有的都会失败,因为这种方式本来就不符合时间加减规范,其他日期类型同理。 所以要杜绝此类问题,研发就不能偷懒,必须使用时间函数。

4正确方式

为日期加上一个时间间隔:date_add() date_add(@dt, interval 1 microsecond); -加1毫秒 date_add(@dt, interval 1 second); -加1秒 date_add(@dt, interval 1 minute); -加1分钟 date_add(@dt, interval 1 hour); -加1小时 date_add(@dt, interval 1 day); -加1天 date_add(@dt, interval 1 week); -加1周 date_add(@dt, interval 1 month); -加1月 date_add(@dt, interval 1 quarter); -加1季 date_add(@dt, interval 1 year); -加1年 为日期减去一个时间间隔:date_sub(),格式同date_add() 改写后:

> update test1 set CreateTime=date_add(CreateTime, interval 1 second),ModifyTime=date_add(ModifyTime, interval 1 second);

> select * from test1;
+-+-------------+-------------+
| Id | CreateTime      | ModifyTime       |
+-+-------------+-------------+
|  1 | 2017-08-01 18:31:00 | 2017-08-01 18:31:00 |
|  2 | 2017-08-01 18:31:02 | 2017-08-01 18:31:02 |
|  3 | 2017-08-01 18:31:03 | 2017-08-01 18:31:03 |
+-+-------------+-------------+

本文分享自微信公众号 - MYSQL轻松学(easymysql),作者:关注

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-08-25

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Generator的正确打开方式

    贾顺名
  • Hadoop的正确打开方式

    关于 Hadoop 所谓的消亡,以及它跌落神坛的报道数不胜数。有很多人放马后炮说,Hadoop 从一开始就没有意义。还有人说“Hadoop 对于小型,临时的工作...

    挖掘大数据
  • Phantomjs的正确打开方式

    專 欄 ❈ 作者:nmask 博客地址: https://thief.one/ ❈ 前段时间分析了Selenium+Phantomjs的使用方法以及性能优化问...

    Python中文社区
  • DependencyProperty.UnsetValue 的正确打开方式

    2017-10-10 15:21

    walterlv
  • 打开orika的正确方式

    缘起 架构分层 开发分布式的项目时,DO持久化对象和DTO传输对象的转换是不可避免的。集中式项目中,DO-DAO-SERVICE-WEB的分层再寻常不过,但分布...

    kirito-moe
  • Generator的正确打开方式

    代码运行后,我们首先会得到一条cooking的log, 然后在3s后会再次得到一条log。

    贾顺名
  • NSNotificationCenter.addObserverForName的正确打开方式

    Apple 在 iOS4 中添加 addObserverForName 方法,给予开发者可以在block中处理 Notification 通知的便捷方法。

    剑行者
  • Pipeline Review的正确打开方式

    Pipeline review这个词可能在外企销售中的刺耳排名中名列前茅。因为它经常会从常规会议变成类似法庭审讯。以下是销售Manager典型审讯句式:

    臭豆腐
  • GoCD的正确打开方式|洞见

    事件:ThoughtWorks在2016年11月发布的技术雷达中将“Jenkins as a deployment pipeline”列为了“暂缓”。 Jenk...

    ThoughtWorks

扫码关注云+社区

领取腾讯云代金券