php - 如何以这样的格式计算过去的小时数 hh :mm - hh:mm?

Question

关闭。这个问题需要更多 focused 。它目前不接受答案。

---

想改进这个问题吗？ 更新问题，使其只关注一个问题 editing this post .

已关闭 3 年前。

Improve this question

我有这样的 table

------------------------------------------
   id     |    work hours   |   pause   
------------------------------------------
    1.    |  07:00-17:00    |   30 min
-------------------------------------------
    2.    |  06:00-12:00    |    1h
-------------------------------------------
    3.    |  08:30-16:00    |    30 min

我想获得这种格式 07:00-17:00(即 10 小时)的过去时间，并用“暂停”(10h - 30m) 减去该值。对所有行执行此操作，最后得到所有行的总和。

是否可以使用 mysql 或 PHP 来做到这一点？

Answer 1

您可以使用以下解决方案:

SELECT id, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(TIMEDIFF(to_time, from_time), pause)))) AS time_diff
FROM (
  SELECT id,
    CAST(SUBSTRING_INDEX(work_hours, '-', 1) AS TIME) AS from_time,
    CAST(SUBSTRING_INDEX(work_hours, '-', -1) AS TIME) AS to_time,
    CAST(CASE WHEN INSTR(pause, 'h') THEN CONCAT(TRIM(REPLACE(pause, 'h', '')) , ':00:00') ELSE CONCAT('00:', TRIM(REPLACE(pause, 'min', '')), ':00') END AS TIME) AS pause
  FROM t
) t GROUP BY id WITH ROLLUP

1st demo on dbfiddle.uk
2nd demo on dbfiddle.uk (sum of half hours)

此解决方案在 FROM 部分使用 SELECT 来获取正确格式的列信息。要SUM计算所有时间差异，您可以在id列上使用GROUP BY WITH ROLLUP .

您可以对最长约 838 小时的时间差进行SUM:

TIME values may range from -838:59:59 to 838:59:59. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).
source: https://dev.mysql.com/doc/refman/8.0/en/time.html

<小时/>

我建议更改您的表结构!最好将时间值保存在自己的列中(使用 TIME 数据类型而不是字符串)。

您可以使用以下结构创建表:

CREATE TABLE new_table (
  id INT,
  work_hours_from TIME,
  work_hours_to TIME,
  pause TIME
);

使用以下 INSERT 命令，您可以将当前表的所有(有效)行传输到新表:

INSERT INTO new_table
SELECT id,
  CAST(SUBSTRING_INDEX(work_hours, '-', 1) AS TIME) AS from_time,
  CAST(SUBSTRING_INDEX(work_hours, '-', -1) AS TIME) AS to_time,
  CAST(CASE WHEN INSTR(pause, 'h') THEN CONCAT(TRIM(REPLACE(pause, 'h', '')) , ':00:00') ELSE CONCAT('00:', TRIM(REPLACE(pause, 'min', '')), ':00') END AS TIME) AS pause
FROM old_table

现在您可以编写更简单的 SELECT 查询，而无需使用字符串函数来获得预期结果:

SELECT id, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(TIMEDIFF(work_hours_to, work_hours_from), pause)))) AS time_diff
FROM new_table
GROUP BY id WITH ROLLUP