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 条评论
登录 后参与评论

相关文章

来自专栏数据科学与人工智能

【陆勤践行】DataSchool 推荐的数据科学资源

Blogs Simply Statistics1: Written by the Biostatistics professors at Johns Hopki...

2059
来自专栏素质云笔记

python︱利用dlib和opencv实现简单换脸、人脸对齐、关键点定位与画图

这是一个利用dlib进行关键点定位 + opencv处理的人脸对齐、换脸、关键点识别的小demo。原文来自于《Switching Eds: Face swapp...

41010
来自专栏数据派THU

自然语言处理领域重要研究及资源全索引!

来源:机器之心 作者:Kyubyong Park 本文长度为3071字,建议阅读6分钟 本文为你整理自然语言处理最新深度研究成果。 自然语言处理(NLP)是人工...

2149
来自专栏腾讯高校合作

【犀牛鸟·视野】SIGGRAPH Asia 2017 (DAY 3):领略前沿poster papers,关注WebXR新技术

今天是SIGGRAPH Asia 2017的第三天,也是Poster papers讲解的最后一天(总共两天,每天中午13:00-14:00)。今年中了poste...

3336
来自专栏CreateAMind

carla 体验效果 及代码

https://github.com/carla-simulator/imitation-learning 使用了 Direct Future Predic...

572
来自专栏专知

【专知荟萃18】目标跟踪Object Tracking知识资料全集(入门/进阶/论文/综述/视频/专家,附查看)

目标跟踪 (Object Tracking/Visual Tracking) 专知荟萃 入门学习 进阶文章 Benchmark 综述 Tutorial 代码 领...

8595
来自专栏专知

【ICCV 2017论文集】计算机视觉顶级会议ICCV2017 Open Access Repository

在这里先整理一些主题系列论文: ICCV 2017- 3D Vision Oral论文如下: Globally-Optimal Inlier Set Maxi...

3598
来自专栏CreateAMind

Suggested Education for Future AGI Researchers

https://sites.google.com/site/narswang/home/agi-introduction/agi-education

832
来自专栏深度学习计算机视觉

pytorch demo 实践

相关环境 python opencv pytorch ubuntu 14.04 pytorch 基本内容 60分钟快速入门,参考:https://blog...

4736
来自专栏专知

【论文推荐】最新六篇目标跟踪相关论文—双重Siamese网络、判别性相关滤波、多目标跟踪、深度多尺度时空判别性、综述、显著性增强

【导读】专知内容组整理了最近六篇目标跟踪(Object Tracking)相关文章,为大家进行介绍,欢迎查看! 1. A Twofold Siamese Net...

3818

扫描关注云+社区