To calculate the time interval between two timestamps using Timestampdiff details to note

Problem description

A table has the following fields:

DROP TABLE IF EXISTS ' A ';
CREATE TABLE IF not EXISTS ' A ' (
  ' userId ' int (ten) unsigned not NULL,
  ' flag_time ' timestamp NULL DEFAULT current_ti Mestamp,
  ' daycount ' int (a) unsigned DEFAULT ' 1 ',
  ...
  PRIMARY KEY (' userId ')
) Engine=myisam DEFAULT Charset=utf8;

The requirement is that, based on the Flag_time value and the current time, if it is the same day, update the Daycount value to daycount+ the current number and update the flag_time time; if not the same day, the Daycount value is transferred to another table. and update the Daycount value to

The current number of times.

The Timestampdiff function (which calculates the time interval between two timestamps) is then implemented based on the

The following is part of the stored procedure Code

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;

When requesting, detects flag_time time, even if flag_time time part is 23:59:59, if again request in 00:00:00 time of the next day, according to the requirement content, then the Daycount value is transferred to another table:

If you pass the count value of 2 to the server at 2012-08-21 23:56:20

Userid	Flag_time	Daycount
1000	2012-08-21 23:56:20	2

If you pass the count value of 4 to the server at 2012-08-21 23:58:20

Userid	Flag_time	Daycount
1000	2012-08-21 23:58:20	6

At 2012-08-22 00:00:02 Pass the count value of 5 to the server

Userid	Flag_time	Daycount
1000	2012-08-22 00:00:02	5

The original Daycount value of 6 is transferred to another table.

However, the program does not follow the intended implementation, after debugging found that the Timestampdiff function is easy to neglect the place.

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

The time difference between days is based on 24*60*60 (86400). If the difference interval is less than 86,400 seconds, then 0, if equal to 86,400 seconds or greater than 86,400 seconds and less than 86400*2 seconds, then 1.

If the unit is minutes, then the interval minutes are calculated based on 60 seconds. If the difference interval is less than 60 seconds, then 0, if equal to 60 seconds or greater than 60 seconds and less than 60*2 seconds, then 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

Therefore, to achieve the above requirements, the solution is to retain only the year and a day, discarded seconds.

The stored procedure is changed to

Select A.userid,date_format (T.flag_time, "%y-%m-%d"), A.daycount into the UID,FTM,DCT from
A as a left join user as B on a.u Serid=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;

You can also use the To_days function (returns the number of days between two dates/times)

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");

Return: 1