MySQL add timestamp values

Question

I have a table in MySQL with this format: (time = timestamp on insert)

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)

What I need is to make a select that adds the time differences like this

(41 - 31) + (46 - 45) (i'm using the id's instead of the actual time values to better understand what I need to do )

something like SELECT (something that does this) AS TotalTimeSpent WHERE tid = '1'

Do you you have any information about which timestamp is which kind? Is there assumption that the oldest timestamp for a tid/uid combination is the start and they alternate after that? — Tom Hazel
– Tom Hazel, Commented May 19, 2011 at 17:05
Getting the "stop" event given the id of a "start" event is doable, it's ignoring half the events as "start" events that's tricky. Why are you even using this (and I mean this in a very objective way) stupid database design? You should at least store the event types, if not use 2 different tables or even better a single row with 2 time values, start and stop. — Blindy
– Blindy, Commented May 19, 2011 at 17:07
It simply works like start/stop/start/stop , if the COUNT(*) result is not even the select won`t be triggered — Andrei Stanca
– Andrei Stanca, Commented May 19, 2011 at 17:08

Blindy · Accepted Answer · 2011-05-19 17:24:14Z

2

If you insist on using this table layout (and I really hope you change your mind, it is truly horrific), you can do it with cursors in a stored procedure.

Pseudo-code would be something like this:

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;

answered May 19, 2011 at 17:24

Blindy

68k10 gold badges96 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrei Stanca Over a year ago

Thank You. I do not have a choice, it is an already written APP and i have to change a lot of things if i modify tables structures

Blindy Over a year ago

If you'll keep writing reports like these, you'll waste a lot more time than just switching layouts. This whole thing could have been written as SELECT SUM(datediff(end-start)) FROM tbl.

Collectives™ on Stack Overflow

MySQL add timestamp values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related