PL/SQL function to claculate minutes overlap

I am not good at PL/SQL, I wrote a function which gives minutes overlap between two date ranges. Can this be enhanced further? Are there any function libraries available for date time calculations?


CREATE FUNCTION DATETIME_RANGE_OVERLAP_MINS(startDate1 TIMESTAMP, endDate1 TIMESTAMP ,
startDate2 TIMESTAMP ,endDate2 TIMESTAMP )
RETURN NUMBER IS
overlapMins NUMBER:=0;
startDateMax TIMESTAMP;
endDateMin TIMESTAMP;
BEGIN
IF ( startDate1 > endDate2 OR endDate1 < startDate2) THEN
--No overlap
return overlapMins;
END IF;
–Find maximum of two start dates
startDateMax:=startDate2;
IF startDate1>startDate2 THEN
startDateMax:=startDate1;
END IF;

–Find minimum of two end dates

endDateMin:=endDate2;
IF endDate1
endDateMin:=endDate1;
END IF;

--We will get overlap in days. Convert it to minutes.

overlapMins:=ROUND( TO_NUMBER( CAST(endDateMin AS DATE) - CAST(startDateMax AS DATE)) * 1440) ;

return overlapMins;

END;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s