我正在考虑计算SQL (SQL Server)中两个日期字段之间的差异,这两个字段可能会(也可能不会)与另一个月重叠。
我有一个TimeFrom字段、一个TimeTo字段和一个status字段。这两个时间字段均为日期时间格式,且状态为数字。
以下是日期的示例:
TimeFrom TimeTo Status
2018-04-01 09:03:27 2018-07-15 10:11:13 12
我需要计算这两个日期之间的时间,但在相关的月份,我正在努力自己解决这个问题。
输出将如下所示:
YearMonth | Status | Duration (hh:mm:ss)
2018-04 | 12 | xx:xx:xx
2018-05 | 12 | xx:xx:xx
2018-06 | 12 | xx:xx:xx
发布于 2018-12-13 12:57:14
这应该是可行的:
set serveroutput on;
create or replace procedure d061(d1 in date, d2 in date ) as
dd number;
hh24 number;
mi number;
ss number;
begin
select extract(day from intrvl) into dd
from (
select to_timestamp_tz (d1,'DD MON YYYY hh24:mi:ss')
- to_timestamp_tz (d2,'DD MON YYYY hh24:mi:ss') as intrvl
from dual
);
select extract(hour from intrvl) into hh24
from (
select to_timestamp_tz (d1,'DD MON YYYY hh24:mi:ss')
- to_timestamp_tz (d2,'DD MON YYYY hh24:mi:ss') as intrvl
from dual
);
select extract(minute from intrvl) into mi
from (
select to_timestamp_tz (d1,'DD MON YYYY hh24:mi:ss')
- to_timestamp_tz (d2,'DD MON YYYY hh24:mi:ss') as intrvl
from dual
);
select extract(second from intrvl) into ss from (
select to_timestamp_tz (d1,'DD MON YYYY hh24:mi:ss')
- to_timestamp_tz (d2,'DD MON YYYY hh24:mi:ss') as intrvl
from dual
);
DBMS_OUTPUT.PUT_LINE('DD HH24 MI SS');
DBMS_OUTPUT.PUT_LINE(dd || ' '||hh24||' ' ||mi||' ' ||ss);
end;
/
declare
begin
d061(sysdate+1,sysdate);
end;
/
输出:
DD HH24 MI SS
1 0 0 0
发布于 2018-12-17 00:58:41
建议的解决方案..。
SET DATEFORMAT YMD
DECLARE @aux TABLE (TimeFrom datetime, TimeTo datetime, Status int)
DECLARE @res TABLE (YearMonth varchar(7), Status int, Sc int)
INSERT INTO @aux VALUES ('2018-04-01 09:03:27','2018-07-15 10:11:13',12)
DECLARE @secs int, @dt datetime, @fi datetime, @fo datetime,
@f1 datetime, @f2 datetime, @st int
SELECT TOP 1 @fi = TimeFrom, @fo = TimeTo, @st = status FROM @aux
SELECT @dt = DATEADD(mm, DATEDIFF(mm, 0, @fi), 0), @f1 = @fi
WHILE @dt < @fo
BEGIN
SELECT @f2 = CASE
WHEN DATEADD(mm, 1, @dt) < @fo
THEN DATEADD(mm, 1, @dt)
ELSE DATEADD(ss, 1, @fo)
END
SELECT @secs = DATEDIFF(ss, @f1, DATEADD(s, - 1, @f2))
INSERT INTO @res VALUES (CONVERT(varchar(7), @dt, 120), @st, @secs)
SELECT @f1 = @f2, @dt = DATEADD(mm, 1, @dt)
END
SELECT YearMonth,status
,CAST(Sc / 3600 AS varchar) + '.'
+RIGHT('0' + CAST((Sc - 3600 * (Sc / 3600)) / 60 AS varchar(2)), 2) + '.'
+RIGHT('0' + CAST(Sc % 60 AS varchar(2)), 2) AS [Duration (hhh.mm.ss)]
FROM @res
https://stackoverflow.com/questions/53761901
复制