Unanswered: Number of seconds precision returned from function
Hello Oracle gurus.
My task is to determine number of minutes falling on weekend within any time period defined by start and date.
I wrote the following function which returns number of seconds falling on weekend day within any time period defined by start_date and end_date.
CREATE OR REPLACE FUNCTION GET_WEEKEND_SECONDS
CURSOR cur_weekend_minutes IS
CASE WHEN (weekend_day_num = MIN(weekend_day_num) OVER(PARTITION BY START_DATE)/*START_EDGE_ROW. This determines first weekend in order from old to most recent*/ )
THEN CASE WHEN start_date <= (MIN(WEEKENDDAY_START) OVER(ORDER BY START_DATE))/*first_weekend_start*/THEN 24*60
WHEN START_DATE > (MIN(WEEKENDDAY_START) OVER(ORDER BY START_DATE))/*first_weekend_start*/
THEN ROUND((MIN(WEEKENDDAY_END) OVER(PARTITION BY START_DATE)/*first_weekend_end*/ - START_DATE)*24*60, 0)
ELSE 0 END
WHEN (weekend_day_num = MAX(weekend_day_num) OVER(PARTITION BY END_DATE)/*END_EDGE_ROW. This determines last weekend in order from old to most recent*/)
THEN CASE WHEN END_DATE >= MAX(weekendday_end) OVER(PARTITION BY end_date)/*last_weekend_end*/THEN 24*60
when END_DATE < max(WEEKENDDAY_END) over(partition by END_DATE)/*last_weekend_end*/
THEN ROUND((END_DATE - MAX(WEEKENDDAY_START) OVER(PARTITION BY END_DATE)/*last_weekend_start*/)*24*60 , 0)
ELSE 0 END
ELSE 24 END AS WEEKEND_MINUTES
,trunc(p_START_DATE) + level - 1 WEEKENDDAY_START
,TO_DATE(TO_CHAR((TRUNC(p_START_DATE) + level - 1), 'DD-MM-YYYY') || ' 23:59', 'DD-MM-YYYY HH24:MI') as WEEKENDDAY_END
CONNECT BY LEVEL < = CEIL((p_END_DATE - p_START_DATE)) + 1
WHERE TO_CHAR (WEEKENDDAY_START, 'DY') IN ('SAT', 'SUN');
v_total_weekend_minutes NUMBER(23,0) := 0;
FETCH cur_weekend_minutes INTO v_weekend_minutes_rec;
EXIT when cur_weekend_minutes%NOTFOUND;
v_total_weekend_minutes := v_total_weekend_minutes + v_weekend_minutes_rec.WEEKEND_MINUTES;
RETURN v_total_weekend_minutes * 60.00;
I know it can be improved on multiple levels, this is my POC at the moment. I am not concerned with performance either, I'll improve it later.
Code successfully compiles and does the job, almost...
I am experiencing trouble with precision of returned data.
It returns 780 seconds which is exactly 13 minutes while I expect 823 seconds which is 13 minutes and 43 seconds.
I am losing 43 seconds.
This is the amount of time falling on weekend day (Sunday in this example) between:
start_date - 22-JUN-2014 23:46:17
end of first weekend day in my time interval - 22-JUN-2014 23:59:59
select (to_date('22-JUN-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS') - to_date('22-JUN-2014 23:46:17', 'DD-MON-YYYY HH24:MI:SS'))*24*60*60+1 from dual;
I played with conversion to seconds within my cursor, removed rounding - precision was not better than I see with posted code.
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.