Complex MySQL Timer

Question

If we have a timer that starts from lets say CURRENT_TIMESTAMP - 1 Hour we can simply calculate the difference and return 3600 seconds have passed.

But what if we want the timer to only count during certian times of the day and only lets say work during weekday or specified days. Look at the below code to see the Create statment to get a better understanding

CREATE TABLE `timer` (
  `Id`             BIGINT        NOT NULL  AUTO_INCREMENT,
  `title`          VARCHAR(100)  NOT NULL,
  `startAt`        DATETIME      NOT NULL,

  `startTime`      TIME                    DEFAULT NULL,
  `endTime`        TIME                    DEFAULT NULL,

  `monday`         BOOLEAN                 DEFAULT 1,
  `tuesday`        BOOLEAN                 DEFAULT 1,
  `wednesday`      BOOLEAN                 DEFAULT 1,
  `thursday`       BOOLEAN                 DEFAULT 1,
  `friday`         BOOLEAN                 DEFAULT 1,
  `saturday`       BOOLEAN                 DEFAULT 1,
  `sunday`         BOOLEAN                 DEFAULT 1,

  `dateReg`        TIMESTAMP     NOT NULL  DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY  (`Id`)

) ENGINE=InnoDB AUTO_INCREMENT=1  COMMENT 'Timer';

Note: The startime and endtime represent the hours the timer will take into account when counting seconds from two dates. The timer as represented above does not have a endtime meaning it will never stop

Now one says that it can be done in a query, and im sure they are right but i personally belaive it will be alot easier and better in a stored function:

CREATE FUNCTION `TIMEPASSED`(iId BIGINT(100)) RETURNS BIGINT(20)
BEGIN
    DECLARE sTime TIME;
    DECLARE eTime TIME;
    DECLARE startAt DATETIME;

    #Get the Results from the Database and put them into the variables
    SELECT timer.startTime, timer.endTime, timer.startAt INTO sTime, eTime, startAt FROM tickets
    WHERE timer.Id = iId;

    #if the start time is null then return the difference between the reset time and now
    IF sTime IS NULL
       THEN RETURN (UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - UNIX_TIMESTAMP(startAt));
    END IF;

RETURN NULL;

END

the above function is only returning the full ammount of seconds that have passed scince the timer was started if the startTime IS NULL.

Now lets do the insert query

INSERT INTO timer VALUES(NULL, 'mytimer', CURRENT_TIMESTAMP, 09:00, 18:00, 1, 1, 1, 1, 1, 0, 0, CURRENT_TIMESTAMP);

The above query suggest that the timer starts from CURRENT_TIME and only counts seconds between 09:00 and 18:00 and only works for monday-friday.

The SELECT Query, (Order By is important here)

SELECT *, TIMEPASSED(Id) as passed FROM timer ORDER BY passed DESC

In General: I want to know the seconds passed during the time allocated by the time entered

Any Refrences to basic maths in this subject will be much apreciated. I can work it out then

Thanks :D

Answer 1

If I understand correctly, I think a good algorithm to use in your stored procedure would be:

1. Start a tally at zero.
2. If today is the same date as dateReg, and the corresponding day of the week is true, add to the tally max(startTime, dateReg's time) - min(endTime, current time).
3. Else if today is after the date of dateReg:
   1. If dateReg's corresponding day of the week is true, and dateReg's time is before endTime, add to the tally max(startTime, dateReg's time) - endTime.
   2. If today's corresponding day of the week is true, and the current time is after startTime, add to the tally startTime - min(endTime, current time).
4. Store endTime - startTime in wholeDay.
5. If monday is true, calculate the number of Mondays between dateReg's date and the current date, exclusive.* Add that number multiplied by wholeDay to the tally. Repeat for each day of the week.
6. Return the tally.

* This is another problem on its own. One way to do it would be to calculate the number of complete weeks after dateReg's week and before the current week, then add 1 if the day after dateReg's DoW (day of the week) is before the DoW in question, and add 1 again if the the day before the current DoW is after the DoW in question.