createprocedureproc_t()
begin
createtablet_test1(
rowidbigintauto_incrementprimarykey,
user_idvarchar(20),
datetimedatetime
);
truncateTABLEt_test1;
insertintot_test1(user_id,datetime)selectuser_id,datetimefromtest1orderbyuser_id;
select
a.user_id'用户id',a.datetime'当前日期',
(selectc.datetimefromt_test1cwherec.user_ id=a.user_idandc.rowid>a.rowidlimit1)'下行日期',
TIMESTAMPDIFF(HOUR,str_to_date(a.datetime,'%Y-%m-%d%T'),str_to_date((selectb.datetimefromt_test1bwhereb.user_ id=a.user_idandb.rowid>a.rowidlimit1),'%Y-%m-%d%T'))'时间差'
fromt_test1a;
droptablet_test1;
end
运行结果
用户id当前日期下行日期时间差
02014-07-0801:11:002014-07-0807:11:20x096
02014-07-0807:11:202014-07-1000:11:41x0941
02014-07-1000:11:41
12014-07-0803:11:002014-07-0902:10:0022
12014-07-0902:10:00
计算相邻时间差,用的存储过程
时间差单位小时,如果要精确到秒的话自己完善吧
你这个要是有一个自增id的主键的话就不用存储过程,一句sql搞定