Sql:两个日期之间的差异
Sql:两个日期之间的差异
提问于 2015-04-15 14:02:48
一个简短的问题：
我正在使用这个代码
SELECT trunc (SYSDATE) - TO_DATE('04/10/2015','mm/dd/yyyy') Differenz FROM DUAL;计算两个日期之间的差异。
我想补充的是，它忽略了星期六，星期日和假日(不是学校假期，我指的是像圣诞节这样的节日，东部)。我当地的时区是德国(每个国家都有自己的假期)。
谢谢你帮忙！
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-04-15 18:45:42
不包括周六和周日的天数并不难，你会找到几个解决办法。
考虑到假期更有挑战性。一种解决方案是使用Oracle SCHEDULER。默认情况下，这是用于SCHEDULER JOBS的，但是我认为没有任何理由不将它用于其他目的。
最大的问题是复活节，看这里：计算图。我认为最有效的方法是对日期进行硬编码并手动维护它们。
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE('New_Year', repeat_interval => 'FREQ=YEARLY;BYDATE=0101');
DBMS_SCHEDULER.CREATE_SCHEDULE('Easter_Sunday', repeat_interval => 'FREQ=YEARLY;BYDATE=20150405, 20160327, 20170416, 20170416, 20180401, 20190421, 20200412', comments => 'Hard coded till 2020');
DBMS_SCHEDULER.CREATE_SCHEDULE('Good_Friday', repeat_interval => 'FREQ=YEARLY;BYDATE=20150405-2D, 20160327-2D, 20170416-2D, 20170416-2D, 20180401-2D, 20190421-2D, 20200412-2D');
DBMS_SCHEDULER.CREATE_SCHEDULE('Easter_Monday', repeat_interval => 'FREQ=YEARLY;BYDATE=20150405+1D, 20160327+1D, 20170416+1D, 20170416+1D, 20180401+1D, 20190421+1D, 20200412+1D');
DBMS_SCHEDULER.CREATE_SCHEDULE('Ascension_Day', repeat_interval => 'FREQ=YEARLY;BYDATE=20150405+39D,20160327+39D,20170416+39D,20170416+39D,20180401+39D,20190421+39D,20200412+39D');
DBMS_SCHEDULER.CREATE_SCHEDULE('Pentecost_Monday', repeat_interval => 'FREQ=YEARLY;BYDATE=20150405+50D,20160327+50D,20170416+50D,20170416+50D,20180401+50D,20190421+50D,20200412+50D');
DBMS_SCHEDULER.CREATE_SCHEDULE('Repentance_and_Prayer', repeat_interval => 'FREQ=DAILY;BYDATE=1122-SPAN:7D;BYDAY=WED',
comments => 'Wednesday before November 23th, Buss- und Bettag');
-- alternative solution:
--DBMS_SCHEDULER.CREATE_SCHEDULE('Repentance_and_Prayer', repeat_interval => 'FREQ=MONTHLY;BYMONTH=NOV;BYDAY=3 WED',
-- comments => '3rd Wednesday in November');
DBMS_SCHEDULER.CREATE_SCHEDULE('Labor_Day', repeat_interval => 'FREQ=YEARLY;BYDATE=0501');
DBMS_SCHEDULER.CREATE_SCHEDULE('German_Unity_Day', repeat_interval => 'FREQ=YEARLY;BYDATE=1003');
DBMS_SCHEDULER.CREATE_SCHEDULE('Christmas', repeat_interval => 'FREQ=YEARLY;BYDATE=1225+SPAN:2D');
DBMS_SCHEDULER.CREATE_SCHEDULE('Christian_Celebration_Days', repeat_interval => 'FREQ=DAILY;INTERSECT=Easter_Sunday,Good_Friday,Easter_Monday,Ascension_Day,Pentecost_Monday,Repentance_and_Prayer,Christmas');
-- alternative solution:
-- DBMS_SCHEDULER.CREATE_SCHEDULE('Christian_Celebration_Days', repeat_interval => 'FREQ=Good_Friday;BYDAY=1 MON, 6 THU,8 MON');
DBMS_SCHEDULER.CREATE_SCHEDULE('Political_Holidays', repeat_interval => 'FREQ=DAILY;INTERSECT=New_Year,Labor_Day,German_Unity_Day');
END;
/请参见此处的日历语法：日历语法
然后，您可以使用这样的时间表：
CREATE OR REPLACE FUNCTION DateDiff(end_date IN TIMESTAMP) RETURN INTEGER AS
next_run_date TIMESTAMP := TRUNC(SYSTIMESTAMP);
res INTEGER := 0;
BEGIN
IF end_date > SYSTIMESTAMP THEN
LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY;INTERVAL=1;BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=Christian_Celebration_Days,Political_Holidays', NULL, next_run_date, next_run_date);
EXIT WHEN next_run_date >= end_date;
res := res + 1;
END LOOP;
RETURN res;
ELSE
RAISE VALUE_ERROR;
END IF;
END;
SELECT DateDiff(TO_DATE('04/10/2015','mm/dd/yyyy')) AS Differenz FROM DUAL;输出接下来的20个空位用于测试：
DECLARE
next_run_date TIMESTAMP;
BEGIN
FOR i IN 1..20 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY;INTERSECT='Christian_Celebration_Days,Political_Holidays', NULL, next_run_date, next_run_date);
DBMS_OUTPUT.PUT_LINE(next_run_date);
END LOOP;
END;更新
我甚至找到了一个更紧凑的版本：
BEGIN
-- Start with first celebration day (good Friday), all dependent celebration days have to be after this day for proper calculation of schedule
DBMS_SCHEDULER.CREATE_SCHEDULE('GOOD_FRIDAY', repeat_interval => 'FREQ=YEARLY;BYDATE=20100402,20110422,20120406,20130329,20140418,20150403,20160325,20170414,20180330,20190419,20200410,20210402,20220410,20230407,20240329,20250418,20260403,20270326,20280414,20290330,20300419', comments => 'Hard coded 2010 to 2030');
-- Easter Sunday can be skipped for list of holidays, otherwise 'FREQ=Good_Friday;BYDAY=1 SUN+SPAN:2D'
DBMS_SCHEDULER.CREATE_SCHEDULE('EASTER_MONDAY', repeat_interval => 'FREQ=Good_Friday;BYDAY=1 MON', comments => '1st Monday after Good Friday'
DBMS_SCHEDULER.CREATE_SCHEDULE('ASCENSION_DAY', repeat_interval => 'FREQ=Good_Friday;BYDAY=6 THU', comments => '6th Thursday after Good Friday (40 days after Easter)');
-- Pentecost Sunday can be skipped for list of holidays, otherwise 'FREQ=Good_Friday;BYDAY=8 SUN+SPAN:2D'
DBMS_SCHEDULER.CREATE_SCHEDULE('PENTECOST_MONDAY', repeat_interval => 'FREQ=Good_Friday;BYDAY=8 MON', comments => '8th Monday after Good Friday (50 days after Easter)');
DBMS_SCHEDULER.CREATE_SCHEDULE('EASTER_RELATED_DAYS', repeat_interval => 'FREQ=Good_Friday;BYDAY=1 MON, 6 THU,8 MON'
END;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29652394
复制相关文章
相似问题
腾讯云开发者
