Oracle COMPUTE time-SPAN functions

Displays the span from the current time

--Calculate the time span: Z When the return value is x days y

Declare

Ln_day_count number (10);

Ln_hour_count number (10);

Ln_min_count number (10);

Ls_time_sent_last varchar2 (20): = ' 2015-06-07 08:40:20 ';

Ls_timespan varchar2 (100): = ";

Begin

--Counting days

Select Floor (sysdate-to_date (ls_time_sent_last, ' Yyyy-mm-dd hh24-mi-ss ')) to ln_day_count from dual;

If Ln_day_count! = 0 Then

Ls_timespan: = To_char (ln_day_count) | | ' Day ';

Dbms_output.put_line (Ls_timespan);

End If;

--Calculation of hours

Select mod (Floor (((Sysdate-to_date (Ls_time_sent_last, ' Yyyy-mm-dd hh24-mi-ss ')) *) to Ln_hour_count from dual ;

If Ln_hour_count! = 0 Then

Ls_timespan: = Ls_timespan | | To_char (ln_hour_count) | | ' Time ';

Dbms_output.put_line (Ls_timespan);

End If;

--Calculate the number of minutes

Select mod (Floor (((Sysdate-to_date (Ls_time_sent_last, ' Yyyy-mm-dd hh24-mi-ss ')) * () *, 24*60), +) into Ln_min_ Count from dual;

If Ln_min_count! = 0 Then

Ls_timespan: = Ls_timespan | | To_char (ln_min_count) | | ' min ';

Dbms_output.put_line (Ls_timespan);

End If;

End

Oracle COMPUTE time-SPAN functions