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

相关文章

来自专栏我和未来有约会

Silverlight第三方控件专题

这里我收集整理了目前网上silverlight第三方控件的专题,若果有所遗漏请告知我一下。 名称 简介 截图 telerik 商 RadC...

4025
来自专栏闻道于事

js登录滑动验证,不滑动无法登陆

js的判断这里是根据滑块的位置进行判断,应该是用一个flag判断 <%@ page language="java" contentType="text/html...

6828
来自专栏Ceph对象存储方案

Luminous版本PG 分布调优

Luminous版本开始新增的balancer模块在PG分布优化方面效果非常明显,操作也非常简便,强烈推荐各位在集群上线之前进行这一操作,能够极大的提升整个集群...

3145
来自专栏张善友的专栏

LINQ via C# 系列文章

LINQ via C# Recently I am giving a series of talk on LINQ. the name “LINQ via C...

2645
来自专栏菩提树下的杨过

Flash/Flex学习笔记(23):运动学原理

先写一个公用的小球类Ball: package{ import flash.display.Sprite; //小球 类 public class B...

25310
来自专栏hbbliyong

WPF Trigger for IsSelected in a DataTemplate for ListBox items

<DataTemplate DataType="{x:Type vm:HeaderSlugViewModel}"> <vw:HeaderSlug...

4064
来自专栏陈仁松博客

ASP.NET Core 'Microsoft.Win32.Registry' 错误修复

今天在发布Asp.net Core应用到Azure的时候出现错误InvalidOperationException: Cannot find compilati...

4848
来自专栏张善友的专栏

Miguel de Icaza 细说 Mix 07大会上的Silverlight和DLR

Mono之父Miguel de Icaza 详细报道微软Mix 07大会上的Silverlight和DLR ,上面还谈到了Mono and Silverligh...

2707
来自专栏芋道源码1024

熔断器 Hystrix 源码解析 —— 断路器 HystrixCircuitBreaker

本文主要基于 Hystrix 1.5.X 版本 1. 概述 2. HystrixCircuitBreaker 3. HystrixCircuitBreaker....

5327
来自专栏张善友的专栏

Mix 10 上的asp.net mvc 2的相关Session

Beyond File | New Company: From Cheesy Sample to Social Platform Scott Hansel...

2577

扫码关注云+社区