MySQL : Calculate business day difference between two dates column

Question

我的 sql 查询返回两列，第一列是“创建日期”，第二列是“更新日期”，第一列具有相对于第二列的先验时间戳。

我需要添加第三列，它可以显示工作日时间(上午 9:00 到下午 5:00)响应，即如果创建日期是 2012-01-04 09:00:20 并且“更新日期”是下午 4:00同一天然后第三列应该显示 7 小时

如果创建日期是 2012-01-04 16:00:20(下午 4:00)并且“更新日期”是 2012:01:05(1 月 2 日)10:00m，则第三列应显示 2 小时。

应该排除星期六和星期日。

能否请您为此建议适当的 SQL 查询。

Answer 1

作为@Gordon Linoff commented ，您需要创建一个包含您的营业时间的表:

CREATE TABLE business_days (
  start DATETIME NOT NULL,
  end   DATETIME NOT NULL
);

INSERT INTO `business_days`
  (start, end)
VALUES
  ('2011-12-30 09:00', '2011-12-30 15:00'),  -- short day
  ('2012-01-03 09:00', '2012-01-03 17:00'),  -- 31st, 1st were Sat/Sun
  ('2012-01-04 09:00', '2012-01-04 17:00'),  -- 2nd was a public holiday
  ('2012-01-05 09:00', '2012-01-05 17:00'),
  -- etc.

然后你可以这样做:

SELECT   m.*,
         SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(
           LEAST(m.updated, b.end),
           GREATEST(m.created, b.start)
         ))))
FROM     my_table m JOIN business_days b
           ON b.start < m.updated AND m.created < b.end
GROUP BY m.created, m.updated  -- or some other key into my_table

查看 sqlfiddle .