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轻松学(learnmysql)

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏hbbliyong

错误:该行已经属于另一个表

从一个TABLE中取一行放到另一个TABLE里报错: 该行已经属于另一个表。的解决办法 用下面来个方法就OK了。 DataTable dt = new Da...

3264
来自专栏数据库新发现

关于shared pool的深入探讨(六)

http://www.eygle.com/internal/shared_pool-6.htm

972
来自专栏数据库新发现

字符集问题的初步探讨(六)-----乱码的产生

http://www.eygle.com/special/NLS_CHARACTER_SET_06.htm

892
来自专栏ASP.NET MVC5 后台权限管理系统

Bootstrap Metronic 学习记录(二)菜单栏

1.简介 1)  .环境配置 2)  .提取页面 2).动态生成菜单(无限级别树) 2.系统环境配置 项目需要程序数据支撑,这里选择MVC5.0+EF6.0[S...

2278
来自专栏Java帮帮-微信公众号-技术文章全总结

Hibernate_day03总结

Hibernate_day03总结 今日内容 l Hibernate的检索方式 l Hibernate的抓取策略 l Hibernate的批量检索 l 事务的并...

2373
来自专栏更流畅、简洁的软件开发方式

我的数据访问函数库的源代码(二)—— SQL语句部分

/* 2008 4 25 更新 */ 我的数据访问函数库的源码。整个类有1400行,原先就是分开来写的,现在更新后还是分开来发一下吧。 第二部分:SQL语句...

1889
来自专栏哲学驱动设计

Rafy 框架 - 执行SQL或存储过程

有时候,开发者不想通过实体来操作数据库,而是希望通过 SQL 语句或存储过程来直接访问数据库。Rafy 也提供了一组 API 来方便实现这类需求。 IDbAcc...

22110
来自专栏跟着阿笨一起玩NET

SQL Server 2008使用自定义表类型

本文转载:http://www.cnblogs.com/chenxizhang/archive/2009/04/28/1445234.html

1432
来自专栏ASP.NET MVC5 后台权限管理系统

构建ASP.NET MVC4+EF5+EasyUI+Unity2.x注入的后台管理系统(10)-系统菜单栏[附源码]

似乎我们需要更多的模块了,我们有一个样例程序,可以帮助我们以后的系统开发做很多对照,我们稍后还有系统日志和系统异常的记录,这时浏览发生了困难,我们这一节来完成一...

3189
来自专栏跟着阿笨一起玩NET

错误:该行已经属于另一个表

551

扫码关注云+社区