Sophisticated MySQL timer

Question

If we have a timer that starts with let CURRENT_TIMESTAMP - 1 hour, we can simply calculate the difference and return 3600 seconds.

But what if we want the timer to be calculated only during certian times of day and only allows you to talk on weekdays or on certain days. Take a look at the code below to see Create Statute for 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 they say that this can be done in the request, and I'm sure that they are right, but I am personally convinced that it will be much simpler and better in the 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 returns only the total number of seconds that passed the session, the timer was started if startTimeIS NULL.

Now let's make an insert request

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

, CURRENT_TIME 09:00 18:00 -.

SELECT ( )

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

: , ,

. ,

: D

Answer 1

, , :

• .
• , dateReg, - true, max max (startTime, dateReg time) - min (endTime, ).
• , dateReg:
  • dateReg , true, dateReg - , max max (startTime, dateReg time) - endTime.
  • , - startTime, tally startTime - min (endTime, ).
• endTime - startTime Day.
• - , dateReg , . * , wholeDay . .
• .

* . - Reg , 1, dateReg DoW ( ) DoW , 1 , DoW DoW .