SQL日期差异无视夜晚
问题描述:
使用Oracle数据库,我需要知道任何两个日期(以秒为单位)之间的差异,而不考虑从晚上6点到早上8点的时间间隔。SQL日期差异无视夜晚
例如,2011年11月1日下午1点至2011年11月11日1点之间的差值达20小时(或分别为72000秒)。我也需要忽略周末和节假日,但我已经有了PLSQL的功能。
预先感谢您!
问候, 亚历
编辑
同时,我想出了一个解决办法我自己,这是说实话不是很复杂的,但做的工作:
CREATE OR REPLACE FUNCTION "GET_WORKTIME"
(
startdate_in in date,
enddate_in in date
)
RETURN NUMBER
AS
days_between number(4);
duration number;
end_of_first_day date;
start_of_last_day date;
startdate date;
enddate date;
weekday number(1);
temp_date date;
holidays day_array;
is_holiday boolean;
BEGIN
duration := 0;
startdate := startdate_in;
enddate := enddate_in;
IF (startdate IS NULL OR enddate IS NULL OR startdate >= enddate) THEN
RETURN 0;
END IF;
days_between := trunc(enddate) - trunc(startdate);
end_of_first_day := to_date(concat(to_char(startdate, 'dd.mm.yyyy'), ' 18:00:00'), 'dd.mm.yyyy hh24:mi:ss');
start_of_last_day := to_date(concat(to_char(enddate, 'dd.mm.yyyy'), ' 08:00:00'), 'dd.mm.yyyy hh24:mi:ss');
temp_date := startdate;
FOR i IN 0..days_between LOOP
-- if it is the first day, just calculate the time until the end of that day
IF i = 0 THEN
duration := duration + greatest((end_of_first_day - startdate), 0) * 24 * 3600;
-- if it is the last day, just calculate the time until that point in time
ELSIF i = days_between THEN
duration := duration + greatest((enddate - start_of_last_day), 0) * 24 * 3600;
-- for every other day, simply add 10 hours (6pm - 8am) if it is neither
-- saturday or sunday nor a holiday
ELSE
weekday := to_number(to_char(temp_date, 'D'));
is_holiday := false;
-- weekend?
IF (NOT weekday IN (6,7)) THEN
-- holiday?
FOR j IN extract(year FROM startdate)..extract(year FROM enddate) LOOP
holidays := get_holidays_for_year(j);
FOR k IN 1..holidays.count LOOP
IF (holidays(k) = trunc(temp_date)) THEN
is_holiday := true;
END IF;
END LOOP;
END LOOP;
-- add 10 hours to the duration
IF (NOT is_holiday) THEN
duration := duration + 10 * 3600;
END IF;
END IF;
END IF;
temp_date := temp_date + 1;
END LOOP;
RETURN duration;
END;
功能也无视周末和假期,所以它看起来比实际上更全面一些。感谢您的帮助!
问候, 亚历
答
在Oracle中,你可以简单地减去两个日期。 可以通过将结果除以(24 * 60 * 60)
select (last_date - first_date)/(24*60*60) as seconds from table
甲特技格式化差来计算的秒数是这样的:
select to_char(trunc(sysdate) + (last_date - first_date), 'hh24:mi:ss') as time_between from table
答
这可能是最容易打破这种成3个部分:
- 第一局部天(如果有的话)
- 最后部分天(如果有的话)
- 其间的整个天(如果有的话)
上的第一部分天是从后来的(开始时间上午8点)至(LAST_DATE,下午6点第一天)的比较早,所以我们减去那些获取时间为第一天:
greatest (least (trunc(first_date)+18/24
, last_date
) - greatest(first_date
, trunc(first_date)+8/24
)
, 0) * 24 as last_day_hours
外greatest
功能确保我们没有得到一个负值如如果开始时间是晚上6点以后。
如果最后一天是不一样的第一天,然后最后的部分日是从上午08点到(结束时刻)的最后一天:
case when trunc(last_date) != trunc(first_date)
then greatest (least(last_date
, trunc(last_date)+18/24
)
- (trunc(last_date)+8/24)
, 0) * 24
else 0 end as last_day_hours
居间整个日子(以小时计) :
greatest ((trunc(last_date) - (trunc(first_date)+1))
, 0
) * 10 as intervening_days_in_hours
10为的上午08点之间小时下午6点的数量和
因此添加这些3个值一起给出的总小时数的周期,并且通过3600总相乘给出了开环nds:
(
(greatest (least (trunc(first_date)+18/24
, last_date
) - greatest(first_date
, trunc(first_date)+8/24
)
, 0) * 24)
+ case when trunc(last_date) != trunc(first_date)
then greatest (least(last_date
, trunc(last_date)+18/24
)
- (trunc(last_date)+8/24)
, 0) * 24
else 0 end
+ (greatest ((trunc(last_date) - (trunc(first_date)+1))
, 0
) * 10)
) * 3600 as total_seconds
我不禁感觉它应该比这更容易!当然,这不会考虑周末。
感谢您的回答,我已经这样做了日期之间的差异。然而,问题(对我来说)是减去6pm到8am之间的时间间隔,因为它与计算无关。 – alexhanschke