Function, which is passed to two times and returns the working time.

Create or replace function fun_get_hour (starttime in varchar2, endtime in varchar2) return number
Is
N_day number; -- days separated
V_start_day varchar2 (10): = substr (starttime, 1, 10 );
V_end_day varchar2 (10): = substr (endtime, 1, 10 );
V_start_time varchar2 (10): = substr (starttime, 12 );
V_end_time varchar2 (10): = substr (endtime, 12 );
Up_time varchar2 (10): = '08: 30: 00 ';
Down_time varchar2 (10): = '17: 30: 00 ';
N_timer number;
N_minute number;
N_hours number;
N_second number;
Begin
Select to_date (v_end_day, 'yyyy-MM-DD ')-to_date (v_start_day, 'yyyy-MM-DD') into n_day from dual;
N_day: = n_day + 1;
If n_day = 1 and v_start_time <up_time and v_end_time <up_time then
Return 0;
End if;
For X in 1 .. n_day Loop
If x = 1 then
If v_start_time> down_time then
N_timer: = 0;
Else
If v_start_time <up_time then
V_start_time: = up_time;
End if;
If n_day = 1 and down_time> v_end_time then
Down_time: = v_end_time;
End if;
N_minute: = to_number (to_char (to_date (down_time, 'hh24: MI: ss'), 'mi ')-to_number (to_char (to_date (v_start_time, 'hh24: MI: ss'), 'mi '));
N_hours: = to_number (to_char (to_date (down_time, 'hh24: MI: ss'), 'hh24')-to_number (to_char (to_date (v_start_time, 'hh24: MI: SS '), 'hh24 '));
N_second: = to_number (to_char (to_date (down_time, 'hh24: MI: ss'), 'ss')-to_number (to_char (to_date (v_start_time, 'hh24: MI: ss'), 'ss '));
N_timer: = n_minute * 60 + n_hours * 60*60 + n_second; -- submission time
End if;
Elsif x = n_day then
If v_end_time <up_time then
N_timer: = n_timer + 0;
Else
If v_end_time> down_time then
V_end_time: = down_time;
End if;
N_minute: = to_number (to_char (to_date (v_end_time, 'hh24: MI: ss'), 'mi ')-to_number (to_char (to_date (up_time, 'hh24: MI: ss'), 'mi '));
N_hours: = to_number (to_char (to_date (v_end_time, 'hh24: MI: ss'), 'hh24')-to_number (to_char (to_date (up_time, 'hh24: MI: SS '), 'hh24 '));
N_second: = to_number (to_char (to_date (v_end_time, 'hh24: MI: ss'), 'ss')-to_number (to_char (to_date (up_time, 'hh24: MI: ss'), 'ss '));
N_timer: = n_timer + n_minute * 60 + n_hours * 60*60 + n_second;
End if;
Else
N_minute: = to_number (to_char (to_date (down_time, 'hh24: MI: ss'), 'mi ')-to_number (to_char (to_date (up_time, 'hh24: MI: ss'), 'mi '));
N_hours: = to_number (to_char (to_date (down_time, 'hh24: MI: ss'), 'hh24')-to_number (to_char (to_date (up_time, 'hh24: MI: SS '), 'hh24 '));
N_second: = to_number (to_char (to_date (down_time, 'hh24: MI: ss'), 'ss')-to_number (to_char (to_date (up_time, 'hh24: MI: ss'), 'ss '));
N_timer: = n_timer + n_minute * 60 + n_hours * 60*60 + n_second;
End if;
End loop;
Return n_timer;
End fun_get_hour;

Function, which is passed to two times and returns the working time.