MySQL 将状态更改日期转换为状态持续时间

Question

所以基本上我有一个表，其中记录了用户状态更改(用户上线、用户离线等)

我想创建一个包含这些状态的持续时间的表(用户从[日期]到[日期]在线，用户从[日期]到[日期]离线)

如何去做这样一件事？仅使用 MySQL 语句是否可以实现？

其他信息:日期存储为 unix 时间戳，数据类型为 int(12)

Answer 1

起初我考虑迭代过程中的行，但后来我意识到 UPDATE 也会迭代它们。

我向现有表添加了三个新列(而不是为此创建新表)，如下所示:

• rowid(用于标识函数中的行)
• 结束时间
• 持续时间

我创建了一个函数来获取一行的结束时间。

以下是我想要实现的目标的步骤(每个步骤都是一个单独的查询)

1:如果存在则删除该函数

DROP FUNCTION IF EXISTS NEXTDATE;

2:创建函数

CREATE FUNCTION NEXTDATE (thisrowid mediumint(9), thistime int(12))
RETURNS int(12)
DETERMINISTIC

BEGIN
    
    SELECT userid INTO @userid FROM status WHERE rowid = thisrowid;
    SELECT status INTO @status FROM status WHERE rowid = thisrowid;
    SELECT MIN(time) INTO @endtime FROM status WHERE userid = @userid AND
        time > thistime AND status != @status;

    RETURN @endtime;

END;

3:更新行

UPDATE status
SET endtime = NEXTDATE(rowid,time), duration = endtime - time