MySQL添加时间戳值

Question

我在MySQL中有一个表，格式如下：(time =插入时的时间戳)

id |    tid |   uid |   time
31 |    1   |   14  |   2011-05-19 05:42:37   //start timestamp)
41 |    1   |   14  |   2011-05-19 07:18:42   //stop timestamp)
45 |    1   |   14  |   2011-05-19 07:18:49   //start timestamp)
46 |    1   |   14  |   2011-05-19 07:28:42   //stop timestamp)

我需要做的是做一个选择，添加像这样的时间差

(41 - 31) + (46 - 45) (为了更好地理解我需要做什么，我使用id而不是实际的time值)

像SELECT (something that does this) AS TotalTimeSpent WHERE tid = '1'这样的东西

Answer 1

如果您坚持使用这种表布局(我真的希望您改变主意，这真的很可怕)，那么您可以在存储过程中使用游标。

伪代码是这样的：

CREATE PROCEDURE gettotaltime()
BEGIN
  DECLARE total, curr, prev DATETIME;
  DECLARE odd INT DEFAULT 1;
  DECLARE done INT DEFAULT 0;

  DECLARE c CURSOR FOR SELECT time FROM tbl;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN c;

  read_loop: LOOP
    FETCH c INTO curr;

    IF odd=0 THEN 
      SET total=dateadd(total,datediff(curr,prev));   -- or something similar, I forget
    END IF;

    SET prev=curr;
    SET odd=1-odd;

    IF done THEN
      LEAVE read_loop;
    END IF;
  END LOOP;

  CLOSE c;

  SELECT total;
END;