Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    34

    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.

    Code:
    CREATE OR REPLACE FUNCTION GET_WEEKEND_SECONDS
    (
          P_START_DATE DATE,
          p_END_DATE DATE
    )
    RETURN NUMBER
    IS
    CURSOR cur_weekend_minutes IS
    SELECT 
    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
    FROM
    (
     SELECT
     ROWNUM WEEKEND_DAY_NUM
     ,P_START_DATE START_DATE
     ,p_END_DATE END_DATE
     ,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
     FROM DUAL
     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;
    v_weekend_minutes_rec cur_weekend_minutes%ROWTYPE;
    
    BEGIN
    OPEN cur_weekend_minutes;
    LOOP
      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;
    END LOOP;
    CLOSE cur_weekend_minutes;
    RETURN v_total_weekend_minutes * 60.00;
    END;
    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.

    Here is my example.
    Code:
    SELECT GET_WEEKEND_MINUTES(TO_DATE('22-JUN-2014 23:46:17', 'DD-MON-YYYY HH24:MI:SS'), TO_DATE('23-JUN-2014 01:51:52', 'DD-MON-YYYY HH24:MI:SS')) FROM DUAL;
    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:
    Code:
    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;
    
    WEEKEND_SECONDS
    823
    I played with conversion to seconds within my cursor, removed rounding - precision was not better than I see with posted code.


    Could you please help?

    Thank you,
    Pit.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •