mysql - 忽略特定时间之间的 TIMESTAMPDIFF

Question

你有没有可能以分钟为单位计算总和，而不是在某个特定时间之间，比如 2300 到 0700 之间

SELECT SUM( TIMESTAMPDIFF(MINUTE, open_time, close_time)) 
FROM requests;

我上面的代码以分钟为单位计算时间，但也计算一整夜，这是我想避免的。

感谢您的帮助，我根本不是 SQL 专家!

Answer 1

我不知道有任何内置函数会返回指定的结果。而且我不知道有什么简单的表达式可以处理所有可能的情况。

一种替代方法是定义一个函数(MySQL 存储程序)来计算该差异。

例如，如果我们完全忽略日期时间/时间戳的任何“秒”部分，并使用整分钟，则如下所示:

注意:这未经过全面测试。 (考虑处理跨越夏令时变化的打开/关闭。这可能工作得很好，但尚未经过测试。这只是作为一种可能方法的演示。

DELIMITER $$

CREATE FUNCTION `worktimeminutes`(open_time DATETIME, close_time DATETIME)
RETURNS INT
BEGIN
   DECLARE tot_minutes INT;
   DECLARE wrk_clock   DATETIME;
   DECLARE nxt_2300    DATETIME;

   SET tot_minutes := 0;
   SET wrk_clock   := open_time;

   -- exit early if either argument NULL
   IF open_time IS NULL OR close_time IS NULL THEN
      RETURN NULL;
   END IF;

   -- exit early if open_time time is on or after close_time
   IF open_time >= close_time THEN
      RETURN 0;
   END IF;

   myloop: LOOP

      -- if after 11PM, skip clock forward to 7AM
      IF DATE_FORMAT(wrk_clock,'%H%i') >= '2300' THEN
         SET wrk_clock := DATE_FORMAT(wrk_clock,'%Y-%m-%d 07:00') + INTERVAL 1 DAY;
      END IF;
      SET nxt_2300 := DATE_FORMAT(wrk_clock,'%Y-%m-%d') + INTERVAL 23 HOUR;

      -- have we reached close_time yet?
      IF wrk_clock >= close_time THEN
         LEAVE myloop;
      END IF;

      IF close_time < nxt_2300 THEN
         SET tot_minutes := tot_minutes + TIMESTAMPDIFF(MINUTE,wrk_clock,close_time);
      ELSE
         SET tot_minutes := tot_minutes + TIMESTAMPDIFF(MINUTE,wrk_clock,nxt_2300);
      END IF;

      SET wrk_clock := nxt_2300;

   END LOOP myloop;

   RETURN tot_minutes;

END$$

DELIMITER ;

利用MySQL中定义的函数，我们可以运行一些简单的测试用例。我们可以像内置函数一样在 SELECT 语句中引用该函数。

SELECT t.open_time
     , t.close_time
     , worktimeminutes(t.open_time,t.close_time)  AS worktimeminutes
     , TIMESTAMPDIFF(MINUTE,t.open_time,t.close_time) AS tsdiff
  FROM ( SELECT NULL AS open_time, NULL AS close_time
         UNION ALL SELECT '2016-02-16 11:00', '2016-02-16 21:00'
         UNION ALL SELECT '2016-02-16 21:00', '2016-02-16 23:15'
         UNION ALL SELECT '2016-02-16 22:00', '2016-02-17 01:10'
         UNION ALL SELECT '2016-02-16 23:15', '2016-02-17 01:12'
         UNION ALL SELECT '2016-02-16 22:00', '2016-02-17 08:30'
         UNION ALL SELECT '2016-02-16 23:30', '2016-02-17 08:30'
         UNION ALL SELECT '2016-02-16 23:30', '2016-02-18 08:30'
    ) t

返回:

open_time         close_time        worktimeminutes  tsdiff  
----------------  ----------------  ---------------  --------
(NULL)            (NULL)                     (NULL)    (NULL)
2016-02-16 11:00  2016-02-16 21:00              600       600
2016-02-16 21:00  2016-02-16 23:15              120       135
2016-02-16 22:00  2016-02-17 01:10               60       190
2016-02-16 23:15  2016-02-17 01:12                0       117
2016-02-16 22:00  2016-02-17 08:30              150       630
2016-02-16 23:30  2016-02-17 08:30               90       540
2016-02-16 23:30  2016-02-18 08:30             1050      1980