DateDiff 输出小时和分钟

Question

我的代码以小时为单位给出了 TOTAL HOURS，但我试图输出类似

TotalHours
  8:36

其中 8 代表小时部分，36 代表分钟部分，表示一个人一天在办公室工作的总小时数。

 with times as (
SELECT    t1.EmplID
        , t3.EmplName
        , min(t1.RecTime) AS InTime
        , max(t2.RecTime) AS [TimeOut]
        , t1.RecDate AS [DateVisited]
FROM  AtdRecord t1
INNER JOIN
      AtdRecord t2
ON    t1.EmplID = t2.EmplID
AND   t1.RecDate = t2.RecDate
AND   t1.RecTime < t2.RecTime
inner join
      HrEmployee t3
ON    t3.EmplID = t1.EmplID
group by
          t1.EmplID
        , t3.EmplName
        , t1.RecDate
)
SELECT    EmplID
        , EmplName
        , InTime
        , [TimeOut]
        , [DateVisited]
        , DATEDIFF(Hour,InTime, [TimeOut]) TotalHours
from times
Order By EmplID, DateVisited

原文由 Evil rising 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

可以做这样的小改动

  SELECT  EmplID
        , EmplName
        , InTime
        , [TimeOut]
        , [DateVisited]
        , CASE WHEN minpart=0
        THEN CAST(hourpart as nvarchar(200))+':00'
        ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as 'total time'
        FROM
        (
        SELECT   EmplID, EmplName, InTime, [TimeOut], [DateVisited],
        DATEDIFF(Hour,InTime, [TimeOut]) as hourpart,
        DATEDIFF(minute,InTime, [TimeOut])%60 as minpart
        from times) source

原文由 DhruvJoshi 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

对于像我一样拥有 MySql 版本 < 5.6 的人，他们没有 TIMESTAMPDIFF 所以，我编写了 MYTSDIFF 一个函数，该函数接受 %s (%m 或 %i) 分钟 %h 标志，以获得秒、分钟和小时之间的差异2个时间戳。

享受！

 DROP FUNCTION IF EXISTS MYTSDIFF;

DELIMITER $$
CREATE FUNCTION `MYTSDIFF`( date1 timestamp, date2 timestamp, fmt varchar(20))
returns varchar(20) DETERMINISTIC
BEGIN
  declare secs smallint(2);
  declare mins smallint(2);
  declare hours int;
  declare total real default 0;
  declare str_total varchar(20);

  select cast( time_format( timediff(date1, date2), '%s') as signed) into secs;
  select cast( time_format( timediff(date1, date2), '%i') as signed) into mins;
  select cast( time_format( timediff(date1, date2), '%H') as signed) into hours;

  set total = hours * 3600 + mins * 60 + secs;

  set fmt = LOWER( fmt);

  if fmt = '%m' or fmt = '%i' then
    set total = total / 60;
  elseif fmt = '%h' then
    set total = total / 3600;
  else
    /* Do nothing, %s is the default: */
    set total = total + 0;
  end if;

  select cast( total as char(20)) into str_total;
  return str_total;

  END$$
  DELIMITER ;

原文由 Sergio Abreu 发布，翻译遵循 CC BY-SA 4.0 许可协议