Calculate datetimediff in hrs mins seconds using function alias in reports(Oracle Type)

General
, ,
1

Sol:

Create custom function Alias

Step 1: Just copy by saving as the OOTB DifferenceinMinutes function alias.

Step 2: replace with below

<p:choose>
<p:when test=“.pyParameters(1).pyUserEnteredDataType == ‘DATETIME’ && .pyParameters(2).pyUserEnteredDataType ==‘DATETIME’”>
<p:choose>

<p:when test=".pyParameters(1).pyDBDataType ==‘TIMESTAMP’ && .pyParameters(2).pyDBDataType ==‘TIMESTAMP’ ">

ROUND(( CAST({2} AS DATE) - CAST({1} AS DATE) )*86400)
</p:when>

</p:choose>
</p:when>

<p:otherwise>
ERROR : INCOMPATIBLE TYPES ENTERED
</p:otherwise>

</p:choose>

  1. Apply in Report definition

  2. In RD row apply custom HTML property (Control)to the property that needs the date in this format days,hrs,mins,secs. ( aka Control that displays as 2days, 3 hrs, 2mins, 6 secs format). For this create below custom control

  3. Saves OOTB “DateDifference” control to your custom name say for ex “DateDifferenceInSeconds”

  4. Then eplace with below code :

<%
double totalSecs=0;
totalSecs = Double.parseDouble(tools.getActive().getStringValue());

String display;

if(totalSecs>0){

double dDays = Math.floor(totalSecs / 3600); // get hours
double dHours = Math.floor(totalSecs / 3600); // get hours
double dMins = Math.floor((totalSecs - (dHours * 3600)) / 60); // get minutes
double dSecs = totalSecs - (dHours * 3600) - (dMins * 60); // get seconds

display = Integer.toString((int)dDays) + " Days, " + Integer.toString((int)dHours) + " Hours, " + Integer.toString((int)dMins) + "Mins, " +Integer.toString((int)dSecs) + “Secs” ;

} else {

display ="0 Days, " + " 0 Hours, " + "0 Mins, "+“0 Secs”;
}

%>

<%=display%>
2

Create a custom function alias by copying the OOTB DifferenceInMinutes function and modify it to calculate the difference between two DATETIME values in seconds using CAST to DATE and multiplying the result by 86400 in Oracle. Then handle validation to ensure both input parameters are of DATETIME type and return an error message if they are incompatible. Next, use this function alias in the Report Definition to get the total duration in seconds between the two datetime fields. After that, create a custom control by copying the OOTB DateDifference control and renaming it. Inside the control, convert the total seconds into days, hours, minutes, and seconds using simple arithmetic calculations in Java. Finally, display the formatted output in the report as “X Days, Y Hours, Z Mins, W Secs” for better readability.