Background

Was after a method to be able to record a start stop watch time function, but needed to be in a SQL database so that it could be used in a web app, it should also cope with a pause and resume function.

Problem

Imagine within an app there is a big red button, when the button is pressed it starts a timer.  After a while they then hit a stop/pause button and it will calculate the elapsed time between the start and stop buttons.  The added complication is that they need to be able to hit a start/resume button, and then hit a stop button so record an the total elapsed time.

Solved

This function can be achieved within mysql by using a trigger, which is activated just before the table is updated.

Create the table which will be used to store the timing function:

CREATE TABLE TaskStatus
(
  ID                  INT UNSIGNED        NOT NULL  AUTO_INCREMENT,
  User                INT UNSIGNED        NOT NULL,
  Job                 INT UNSIGNED        NOT NULL,
  Task                INT UNSIGNED        NOT NULL,
  Note                TEXT                NOT NULL,

  Date                DATETIME            NOT NULL  DEFAULT CURRENT_TIMESTAMP,

  TimerStart          DATETIME,
  TimerEnd            DATETIME,
  TimeSpent           DECIMAL(10,2)       NOT NULL DEFAULT '0.00'
) 
ENGINE = InnoDB, CHARSET = utf8, COLLATE = utf8_general_ci;

Next we need to create the trigger, this trigger is activated upon just before the table is updated.

DELIMITER //
CREATE TRIGGER update_timespent BEFORE UPDATE ON TaskStatus
  FOR EACH ROW BEGIN
    DECLARE duration DECIMAL(10,2);

    IF (NEW.TimerStart IS NOT NULL AND OLD.TimerEnd <> NEW.TimerEnd) THEN
      SELECT CAST((TIMESTAMPDIFF(SECOND, NEW.TimerStart, NEW.TimerEnd) / 3600) AS DECIMAL(10,2))
        INTO duration;

      SET NEW.TimeSpent = (OLD.TimeSpent + duration);
      SET NEW.TimerStart = NULL;
      SET NEW.TimerEnd = NULL;
    END IF;

  END //
DELIMITER ;

How It Works

1. Within the web app someone creates the task, the middleware language (php, python nodejs) takes the submit/post/get and will make a SQL insert into the table which has a null for the TimerStart & TimerEnd.  The trigger is not fired here as it is an insert not an update.
2. Within the Web app the user hits a start button this then posts/gets a requires to the webapp that then updates the task TimerStart & TimerEnd with the same current time stamp.  This is an update and will fire the trigger, it processes each line of the table.  As this is the first time it is updates, there is no OLD.TimerEnd so nothing happens on this time.
3.  WIthin the Web app the user hits a stop button, which get posted to the web app that then updates the record this time the TimerEnd is different.  Again the trigger will run it find that the TimerStart is not null and that the OLD.TimerEnd is different from the NEW.TimerEnd, it then calculates the elapsed time between the TimerStart and the NEW.TimerEnd and then adds that to the TimerElapsed field which is express in hours.  It then nulls both TimeStart and TimerEnd values.
4. If the user hit a start button again the Step2 is repeated.

This provides a mechanism for start/pause/resume/stop, for an elapsed time, stored in a SQL server, which is most useful if a user within an app goes away and comes back at a later stage.

References with thanks

https://stackoverflow.com/questions/32468328/timekeeper-mysql-structure