使用TIMESTAMPDIFF计算两个时间戳之间的时间间隔需要注意的细节

问题阐述

A表中有字段如下：

DROP TABLE IF EXISTS `A`;
CREATE TABLE IF NOT EXISTS `A` (
  `userId` int(10) unsigned NOT NULL,
  `flag_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `dayCount` int(10) unsigned DEFAULT '1',
  ......
  PRIMARY KEY (`userId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

需求是，根据flag_time的值和当前时间比较，如果是当天，则更新dayCount值为dayCount+当前次数，并更新flag_time时间；如果不是当天，则将dayCount的值转存到别一个表中，并更新dayCount值为

当前次数。

于是根据TIMESTAMPDIFF函数（计算两个时间戳之间的时间间隔）实现

以下为储存过程部分代码

select a.userId,a.flag_time,a.dayCount into uid,ftm,dct
from A as a left join user as b on a.userId=b.userId
where b.userId=v_userId;
if uid then
  set mm=timestampdiff(day,ftm,now());
  if mm>0 then
    insert into user_count(userId,count,time) values(uid,dct,CURRENT_TIMESTAMP);
    update A set dayCount=v_count,flag_time=CURRENT_TIMESTAMP where userId=uid;
  else
    update A set dayCount=dayCount+v_count,flag_time=CURRENT_TIMESTAMP where userId=uid;
  end if;
end if;

请求时，检测flag_time时间，即使flag_time的时间部分为23:59:59，如果在第二天的00:00:00时间再次请求，根据需求内容，这时都要将dayCount的值转存到另一表中：

假如在2012-08-21 23:56:20传递count值为2到服务器

userId	flag_time	dayCount
1000	2012-08-21 23:56:20	2

假如在2012-08-21 23:58:20传递count值为4到服务器

userId	flag_time	dayCount
1000	2012-08-21 23:58:20	6

在2012-08-22 00:00:02传递count值为5到服务器

userId	flag_time	dayCount
1000	2012-08-22 00:00:02	5

原来的dayCount值6转存到另一个表中。

但是程序并不按照设想那样执行，经过调试后发现，TIMESTAMPDIFF函数容易疏忽的地方。

select timestampdiff(day,"2012-08-22 15:15:16","2012-08-23 15:15:16");
1

select timestampdiff(day,"2012-08-22 15:15:15","2012-08-23 15:15:16");
1

select timestampdiff(day,"2012-08-22 15:15:17","2012-08-23 15:15:16");
0

天数的时间差是以24*60*60（86400）为依据的。假如相差间隔小于86400秒，则为0，如果等于86400秒或大于86400秒且小于86400*2秒，则为1。

如果单位为分钟，那么计算间隔分钟是以60秒为依据的。假如相差间隔小于60秒，则为0，如果等于60秒或大于60秒且小于60*2秒，则为1。

select timestampdiff(minute,"2012-08-22 15:15:16","2012-08-22 15:16:15");
0

select timestampdiff(minute,"2012-08-22 15:15:16","2012-08-22 15:16:16");
1

select timestampdiff(minute,"2012-08-22 15:15:16","2012-08-22 15:16:56");
1

select timestampdiff(minute,"2012-08-22 15:15","2012-08-22 15:16");
1

所以，实现上述的需求，解决办法是只保留年月日，丢弃时分秒。

存储过程更改为

select a.userId,DATE_FORMAT(t.flag_time,"%Y-%m-%d"),a.dayCount into uid,ftm,dct
from A as a left join user as b on a.userId=b.userId
where b.userId=v_userId;
if uid then
  set mm=timestampdiff(day,ftm,curdate());
  if mm>0 then
    insert into user_count(userId,count,time) values(uid,dct,CURRENT_TIMESTAMP);
    update A set dayCount=v_count,flag_time=CURRENT_TIMESTAMP where userId=uid;
  else
    update A set dayCount=dayCount+v_count,flag_time=CURRENT_TIMESTAMP where userId=uid;
  end if;
end if;

也可以使用To_Days函数（返回两日期/时间之间相差的天数）

SELECT To_Days(end_time) - To_Days(start_time);

SELECT To_Days("2012-08-22 00:00:02") - To_Days("2012-08-21 23:58:20");

返回：1