Ok, so there’s this very useful standard function called ‘ADD_MONTHS’ in Oracle, which gives you your date added with a couple of months.

Here’s a situation: two date (time) fields. I wanted to calculate the time difference between the two. And I mean the exact(!) time difference. Hours, minutes and seconds also. Now that’s not the easiest task. I know I’m no guru, but I know my way around SQL, and could not think of a standard function to manage this. And I found out: there isn’t.

If there’s a suggestion I have for next database releases it is to include a number of standard functions and procedures calculating time differences.
Anyway, I came up with a function and a procedure that might come in handy to some people (to me anyway).

First this function return the difference in time between to date fields. Returning either days, hours, minutes, seconds:
——————————————————————————-
CREATE OR REPLACE function F_GET_TIMEDIF ( pid_startdate date
, pid_enddate date
, pit_type varchar2 default ‘H’ )
— D = Days
— H = Hours (standard)
— M = minutes
— S = seconds
RETURN
number

is

ln_absdiff number ;
ln_returndiff number ;

begin

ln_absdiff := pid_enddate - pid_startdate;

if pit_type = ‘D’ then
RETURN ln_absdiff;
elsif
pit_type = ‘H’ then
ln_returndiff := ln_absdiff*24;
RETURN ln_returndiff;
elsif
pit_type = ‘M’ then
ln_returndiff := ln_absdiff*24*60;
RETURN ln_returndiff;
elsif
pit_type = ‘S’ then
ln_returndiff := ln_absdiff*24*60*60;
RETURN ln_returndiff;
end if;

end;
/
——————————————————————————-

Second, I wanted a procedure returning the complete timestring: Days, hours, minutes and seconds:

——————————————————————————-
CREATE OR REPLACE PROCEDURE P_GET_TIJDVERSCHIL ( pid_startdate IN date
, pid_enddate IN date
, pon_days OUT number
, pon_hours OUT number
, pon_minutes OUT number
, pon_seconds OUT number )

IS

ln_absdiff number ;
ln_days number ;
ln_hours number;
ln_minutes number;
ln_seconds number;

begin
ln_absdiff := pid_enddate - pid_startdate;

ln_days := floor(ln_absdiff);
ln_hours:= floor((ln_absdiff-ln_days)*24);
ln_minutes:= floor((((ln_absdiff-ln_days)*24)-ln_hours)*60);
ln_seconds:= floor((((((ln_absdiff-ln_days)*24)-ln_hours)*60)-ln_minutes)*60);

pon_days := lpad(ln_days,2,0);
pon_hours := lpad(ln_hours,2,0);
pon_minutes := lpad(ln_minutes,2,0);
pon_seconds := lpad(ln_seconds,2,0);

end;
/
——————————————————————————-

Popularity: 342 points