开源中文网

您的位置: 首页 > 数据库应用 > MySQL > 正文

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

来源: zm2714的博客  作者: zm2714

问题阐述
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

Tags:TIMESTAMPDIFF
关于开源中文网 - 联系我们 - 广告服务 - 网站地图 - 版权声明