Question


Pegasystems Inc.
NL
Last activity: 22 Sep 2017 10:01 EDT
DateTime difference function in report definition
Hello,
We would like to have a column in our report definition where we would like to calculate the difference in seconds between two datetime properties. What would be the way to do this?
Thanks,
Nazem
-
Like (0)
-
Share this page Facebook Twitter LinkedIn Email Copying... Copied!


Pegasystems Inc.
IN
You can simply add a column in report definition and use function alias in that column for calculating the difference. You can use "pzGetAppHistoricalComplianceScore" RD as an example to see the use of function alias.
Let us know if this helps :)


Pegasystems Inc.
NL
Thank you for the reply but the problem is I couldn't find a function providing the difference in seconds.


Pegasystems Inc.
IN
Hello Nazem,
You can use the Difference in Minutes and multiple with 60 to convert the difference in seconds. For that First use the Multiply function to Multiply with 60, then use Difference in Minutes function get the difference. Kindly refer the attached screen shots for more info.
Regards,
Praveen


Pegasystems Inc.
NL
Thank you Praveen. I tried your suggested solution but since the two values are of the same minute, the Difference in Minutes function returns 0 so Multiply function does not work accordingly.


Pegasystems Inc.
IN
Hi Nazem,
Adding to Praveen, to get the accurate difference in seconds: you can also try creating a custom function alias by doing save as of "Difference in Minutes" function alias and modifying the source in the function alias to get the result in seconds as below:
Ex:
1. For SQL server: In DATEDIFF function modify interval parameter to 'ss' instead of 'mi'
2. For PostgreSQL: Remove '/60' for ROUND function to get the result in seconds
Let me know if this helps.


Pegasystems Inc.
NL
Hi Sreepriya,
I saved as the function alias to modify but the source is as follows and I couldn't see the parameters you mentioned.
Hi Sreepriya,
I saved as the function alias to modify but the source is as follows and I couldn't see the parameters you mentioned.
<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'">
TIMESTAMPDIFF(4,CHAR({2} -{1}))
</p:when>
<p:when test=".pyParameters(1).pyDBDataType =='TIMESTAMP' && .pyParameters(2).pyDBDataType =='VARCHAR'">
TIMESTAMPDIFF(4,CHAR(TIMESTAMP(SUBSTR({2}, 1, 4)||'-'||SUBSTR({2}, 5, 2)||'-'||SUBSTR({2}, 7, 2)||'-'||SUBSTR({2}, 10, 2)||'.'||SUBSTR({2}, 12, 2)||'.'||SUBSTR({2}, 14, 2)) - {1}))
</p:when>
<p:when test=".pyParameters(1).pyDBDataType =='VARCHAR' && .pyParameters(2).pyDBDataType =='TIMESTAMP'">
TIMESTAMPDIFF(4,CHAR({2} - TIMESTAMP(SUBSTR({1}, 1, 4)||'-'||SUBSTR({1}, 5, 2)||'-'||SUBSTR({1}, 7, 2)||'-'||SUBSTR({1}, 10, 2)||'.'||SUBSTR({1}, 12, 2)||'.'||SUBSTR({1}, 14, 2))))
</p:when>
<p:when test=".pyParameters(1).pyDBDataType =='VARCHAR' && .pyParameters(2).pyDBDataType =='VARCHAR'">
TIMESTAMPDIFF(4,CHAR(TIMESTAMP(SUBSTR({2}, 1, 4)||'-'||SUBSTR({2}, 5, 2)||'-'||SUBSTR({2}, 7, 2)||'-'||SUBSTR({2}, 10, 2)||'.'||SUBSTR({2}, 12, 2)||'.'||SUBSTR({2}, 14, 2)) -
TIMESTAMP(SUBSTR({1}, 1, 4)||'-'||SUBSTR({1}, 5, 2)||'-'||SUBSTR({1}, 7, 2)||'-'||SUBSTR({1}, 10, 2)||'.'||SUBSTR({1}, 12, 2)||'.'||SUBSTR({1}, 14, 2))))
</p:when>
</p:choose>
</p:when>
<p:when test=".pyParameters(1).pyUserEnteredDataType == 'TIMEOFDAY' && .pyParameters(2).pyUserEnteredDataType =='TIMEOFDAY'">
TIMESTAMPDIFF(4,CHAR(TIMESTAMP('1970-01-01-' ||SUBSTR({2}, 1, 2)||'.'||SUBSTR({2}, 3, 2)||'.'||SUBSTR({2}, 5, 2)||'.000') - TIMESTAMP('1970-01-01-' ||SUBSTR({1}, 1, 2)||'.'||SUBSTR({1}, 3, 2)||'.'||SUBSTR({1}, 5, 2)||'.000')))
</p:when>
<p:otherwise>
ERROR : INCOMPATIBLE TYPES ENTERED
</p:otherwise>
</p:choose>


Pegasystems Inc.
IN
Hi Nazem,
If you are using DB2 database, then this SQL source will pick. Please confirm if the same DB is used at your end. If not, in the function alias rule, from actions got to siblings action and open the function alias for your DB and make required changes. For DB2 DB, make the modifications as below:
Instead of TIMETSAMPDIFF(4,-----) try giving TIMESTAMPDIFF(2,----)
For Timestampdifff() function, first paramater describes the output unit.
'4' is for Minutes
'2' is for Seconds
'1' is for milliseconds and so on.
Thanks, let me know if it helps.