Oracle SQL 计算日期之间的天数、小时数和分钟数的总和。

huangapple go评论79阅读模式
英文:

Oracle SQL calculate the sum of days, hours, minutes between dates

问题

以下是翻译好的部分:

"resolved_by" 代理名称和他/她解决的服务数量,以及报告服务的 "creation_Date" 和解决服务的 "last_resolved_date" 之间花费的时间总和,以天、小时和分钟显示该时间,我该如何操作?下面的查询结果很奇怪。

例如:14 天 327 小时 19598 分钟

英文:

I have this query to show an agent name resolved_by, and the count of services he/she resolved, in addition to that I want to show the sum of time spent between when the service is reported creation_Date and when its resolved last_resolved_date for all services resolved per agent,and I want to show that time in days,hours and minutes, how can I do that? the below query gives weird results

select resolved_by,
count(resolved_by) services_resolved,
 
     round(sum((last_resolved_date+0)-(creation_date+0)),0)	 || 'Days' ||
    round(sum(((last_resolved_date+0)-(creation_date+0))*24),0) || 'Hours' ||
    round(sum(((last_resolved_date+0)-(creation_date+0))*24*60),0) || 'Minutes' effort
 from svc_service_requests

where status_type_cd = 'ORA_SVC_RESOLVED'

group by resolved_by





for example : 14 Days 327 Hours 19598 Minutes

答案1

得分: 0

使用 MOD 来获取小时/分钟和 TRUNC(或 FLOOR):

select resolved_by,
       count(resolved_by) AS services_resolved,
       TRUNC(sum(last_resolved_date - creation_date)) || 'Days'
       || TRUNC(MOD(sum(last_resolved_date - creation_date)*24, 24)) || 'Hours'
       || TRUNC(MOD(sum(last_resolved_date - creation_date)*24*60, 60)) || 'Minutes'
         AS effort
from   svc_service_requests
where status_type_cd = 'ORA_SVC_RESOLVED'
group by resolved_by

如果你的列是 TIMESTAMP,那么你可以使用 CAST(last_resolved_date AS DATE)CAST(creation_date AS DATE)

英文:

Use MOD to get the hours/minutes and TRUNC (or FLOOR):

select resolved_by,
       count(resolved_by) AS services_resolved,
       TRUNC(sum(last_resolved_date - creation_date)) || 'Days'
       || TRUNC(MOD(sum(last_resolved_date - creation_date)*24, 24)) || 'Hours'
       || TRUNC(MOD(sum(last_resolved_date - creation_date)*24*60, 60)) || 'Minutes'
         AS effort
from   svc_service_requests
where status_type_cd = 'ORA_SVC_RESOLVED'
group by resolved_by

If your columns are TIMESTAMPs then you can use CAST(last_resolved_date AS DATE) and CAST(creation_date AS DATE).

答案2

得分: 0

以下是代码的翻译部分:

创建一个通用函数,接受日期或时间戳,并返回差异。

CREATE FUNCTION datediff (p_from timestamp, p_to timestamp)
return varchar2 is
  v_from     TIMESTAMP := LEAST(p_from, p_to);
  v_to       TIMESTAMP := GREATEST(p_from, p_to);
  l_years    PLS_INTEGER;
  l_from     TIMESTAMP;
  l_interval interval day(3) to second(6);
begin
  l_years    := TRUNC(MONTHS_BETWEEN(v_to, v_from)/12);
  l_from     := CAST(TRUNC(ADD_MONTHS(v_from, l_years * 12), 'MI') AS TIMESTAMP)
                  + NUMTODSINTERVAL( EXTRACT(SECOND FROM v_from), 'SECOND' );
  l_interval := (v_to - l_from) DAY(3) TO SECOND(6);
  return l_years || ' Years '
    || extract (day from l_interval) || ' Days '
    || extract (hour from l_interval) || ' Hours '
    || extract (minute from l_interval) || ' Minutes '
    || extract (second from l_interval) || ' Seconds';
end datediff;
/

SELECT 
datediff( TO_DATE('2022-04-03 10:11:13','YYYY-MM-DD HH24:MI:SS'), 
TO_DATE('2001-05-10 17:48:09','YYYY-MM-DD HH24:MI:SS')) as  diff FROM DUAL

DIFF
20 Years 327 Days 16 Hours 23 Minutes 4 Seconds
英文:

Here is an all purpose function that accepts either dates or timestamps and returns the difference


CREATE FUNCTION datediff (p_from timestamp, p_to timestamp)
return varchar2 is
  v_from     TIMESTAMP := LEAST(p_from, p_to);
  v_to       TIMESTAMP := GREATEST(p_from, p_to);
  l_years    PLS_INTEGER;
  l_from     TIMESTAMP;
  l_interval interval day(3) to second(6);
begin
  l_years    := TRUNC(MONTHS_BETWEEN(v_to, v_from)/12);
  l_from     := CAST(TRUNC(ADD_MONTHS(v_from, l_years * 12), 'MI') AS TIMESTAMP)
                  + NUMTODSINTERVAL( EXTRACT(SECOND FROM v_from), 'SECOND' );
  l_interval := (v_to - l_from) DAY(3) TO SECOND(6);
  return l_years || ' Years '
    || extract (day from l_interval) || ' Days '
    || extract (hour from l_interval) || ' Hours '
    || extract (minute from l_interval) || ' Minutes '
    || extract (second from l_interval) || ' Seconds';
end datediff;
/

SELECT 
datediff( TO_DATE('2022-04-03 10:11:13','YYYY-MM-DD HH24:MI:SS'), 
TO_DATE('2001-05-10 17:48:09','YYYY-MM-DD HH24:MI:SS')) as  diff FROM DUAL


DIFF
20 Years 327 Days 16 Hours 23 Minutes 4 Seconds

huangapple
  • 本文由 发表于 2023年3月7日 21:54:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/75662879.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定