sql - 复杂的 MySQL 定时器

Question

如果我们有一个从 CURRENT_TIMESTAMP - 1 小时开始的计时器，我们可以简单地计算差异并返回 3600 秒已经过去了。

但是，如果我们希望计时器只在一天中的特定时间计数，并且只在工作日或指定的日子工作，该怎么办。查看下面的代码以查看 Create 语句以获得更好的理解

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';

注意:开始时间和结束时间表示计时器在计算两个日期的秒数时将考虑的小时数。上面表示的计时器没有结束时间，这意味着它永远不会停止

现在有人说它可以在查询中完成，我相信他们是对的，但我个人相信它在存储函数中会更容易和更好:

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

如果 startTime 为 NULL，上述函数仅返回自计时器启动后经过的秒数。

现在让我们做插入查询

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 查询，(Order By 在这里很重要)

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

总的来说:我想知道在输入的时间分配的时间内经过的秒数

任何对本主题中基础数学的引用都将受到高度赞赏。那我就可以解决了

谢谢:D

Answer 1

如果我理解正确，我认为在您的存储过程中使用的一个好的算法是:

1. 从零开始计数。
2. 如果今天与 dateReg 的日期相同，并且对应的星期几为真，则将 max(startTime, dateReg 的时间) - min(endTime, 当前时间) 添加到计数中。
3. 否则如果今天晚于 dateReg 的日期:
   1. 如果 dateReg 对应的星期几为真，并且 dateReg 的时间早于 endTime，则将 max(startTime, dateReg 的时间) - endTime 添加到计数中。
   2. 如果今天对应的星期几为真，并且当前时间在 startTime 之后，则将 startTime - min(endTime, current time) 添加到计数中。
4. 在 wholeDay 中存储 endTime - startTime。
5. 如果 monday 为真，则计算 dateReg 的日期和当前日期之间的星期一数(不含)。* 将该数字乘以 wholeDay 添加到计数中。重复一周中的每一天。
6. 返回计数。

* 这本身就是另一个问题。一种方法是计算 dateReg 的一周之后和当前周之前的完整周数，然后如果 dateReg 的 DoW 之后的那一天(星期几)在所讨论的 DoW 之前，则加 1，如果当前 DoW 的前一天在相关 DoW 之后。