Question
WellsFargo
US
Last activity: 19 May 2016 9:34 EDT
Report Definition Current Date time SQL function
We are using the OOTB CPM alert functionality. This has a report definition "GetCPMAlerts". This report definition filters out all the alerts that are applicable for the current day.
This uses a SQL function "Current Date Time", and this returns the time in GMT which is a problem.
Since all the user are in the US the GMT does not work properly . Is there an alternative solution for this problem
Thanks
Gowri
Message was edited by: Marissa Rogers - added category
Pegasystems
IN
1) Which version of Pega 7 platform and CPM are you using?
2) Did you check the SQL query to see that it is indeed in GMT? Would it be possible to share the SQL query with its prepared values?
3) Which time zone is the application server and database server in?
WellsFargo
US
Hi Rajiv,
1) Which version of Pega 7 platform and CPM are you using?
CPM 7.13 on Pega 7.18
2) Did you check the SQL query to see that it is indeed in GMT? Would it be possible to share the SQL query with its prepared values?
SELECT "PC0".PXCREATEDATETIME AS "pxCreateDateTime" , "PC0".PYDESCRIPTION AS "pyDescription" , "PC0".PXCREATEOPNAME AS "pxCreateOpName" , PEGADATA.pr_read_from_stream('.Urgent', "PC0".pzInsKey, "PC0".pzPVStream) AS "Urgent" , PEGADATA.pr_read_from_stream('.startDate', "PC0".pzInsKey, "PC0".pzPVStream) AS "startDate" , PEGADATA.pr_read_from_stream('.endDate', "PC0".pzInsKey, "PC0".pzPVStream) AS "endDate" , SYSDATE AS "pyDateTimeValue(1)" , "PC0".PXUPDATEDATETIME AS "pxUpdateDateTime" , PEGADATA.pr_read_from_stream('.Title', "PC0".pzInsKey, "PC0".pzPVStream) AS "Title" , "WG".WORKGROUP AS "WorkGroup" , "PC0".PZINSKEY AS "pzInsKey" FROM PEGADATA.pca_alert "PC0" INNER JOIN PEGADATA.pca_index_alert "WG" ON ( ( "PC0".PZINSKEY = "WG".PXINSINDEXEDKEY ) AND "PC0".PXOBJCLASS LIKE ? AND "WG".PXOBJCLASS = ? ) WHERE ( ROUND(SYSDATE - TO_DATE(SUBSTR(PEGADATA.pr_read_from_stream('.startDate', "PC0".pzInsKey, "PC0".pzPVStream),1,8) , 'YYYYMMDD')) >= ? AND ROUND(SYSDATE - TO_DATE(SUBSTR(PEGADATA.pr_read_from_stream('.endDate', "PC0".pzInsKey, "PC0".pzPVStream),1,8) , 'YYYYMMDD')) <= ? ) ORDER BY 1 DESC
Hi Rajiv,
1) Which version of Pega 7 platform and CPM are you using?
CPM 7.13 on Pega 7.18
2) Did you check the SQL query to see that it is indeed in GMT? Would it be possible to share the SQL query with its prepared values?
SELECT "PC0".PXCREATEDATETIME AS "pxCreateDateTime" , "PC0".PYDESCRIPTION AS "pyDescription" , "PC0".PXCREATEOPNAME AS "pxCreateOpName" , PEGADATA.pr_read_from_stream('.Urgent', "PC0".pzInsKey, "PC0".pzPVStream) AS "Urgent" , PEGADATA.pr_read_from_stream('.startDate', "PC0".pzInsKey, "PC0".pzPVStream) AS "startDate" , PEGADATA.pr_read_from_stream('.endDate', "PC0".pzInsKey, "PC0".pzPVStream) AS "endDate" , SYSDATE AS "pyDateTimeValue(1)" , "PC0".PXUPDATEDATETIME AS "pxUpdateDateTime" , PEGADATA.pr_read_from_stream('.Title', "PC0".pzInsKey, "PC0".pzPVStream) AS "Title" , "WG".WORKGROUP AS "WorkGroup" , "PC0".PZINSKEY AS "pzInsKey" FROM PEGADATA.pca_alert "PC0" INNER JOIN PEGADATA.pca_index_alert "WG" ON ( ( "PC0".PZINSKEY = "WG".PXINSINDEXEDKEY ) AND "PC0".PXOBJCLASS LIKE ? AND "WG".PXOBJCLASS = ? ) WHERE ( ROUND(SYSDATE - TO_DATE(SUBSTR(PEGADATA.pr_read_from_stream('.startDate', "PC0".pzInsKey, "PC0".pzPVStream),1,8) , 'YYYYMMDD')) >= ? AND ROUND(SYSDATE - TO_DATE(SUBSTR(PEGADATA.pr_read_from_stream('.endDate', "PC0".pzInsKey, "PC0".pzPVStream),1,8) , 'YYYYMMDD')) <= ? ) ORDER BY 1 DESC
| pyPreparedValues(1) | 0 |
| pyPreparedValues(2) | 0 |
| pyPreparedValues(3) | PegaCA-Alert% |
| pyPreparedValues(4) | Index-PegaCA-Alert |
Results
| pxResults(1) | |
| Name | Value |
| startDate | 20160502 |
| pyDescription | Test Alert4 |
| endDate | 20160505 |
| Urgent | false |
| Title | Test Alert4 |
| pxUpdateDateTime | 20160502T130658.000 GMT |
| pzInsKey | PEGACA-ALERT A-533 |
| pxCreateDateTime | 20160502T130658.000 GMT |
| pxCreateOpName | Business Admin |
| pxObjClass | PegaCA-Alert |
| pxPages(WG) | |
| Name | Value |
| pxSubscript | WG |
| pxObjClass | Index-PegaCA-Alert |
| WorkGroup | GreatLakesAndHorsham |
| pyDateTimeValue(1) | 20160503T194607.000 GMT |
| pxResults(2) | |
| Name | Value |
| startDate | 20160502 |
| pyDescription | Test Alert5 |
| endDate | 20160504 |
| Urgent | false |
| Title | Test Alert5 |
| pxUpdateDateTime | 20160502T130641.000 GMT |
| pzInsKey | PEGACA-ALERT A-532 |
| pxCreateDateTime | 20160502T130641.000 GMT |
| pxCreateOpName | Business Admin |
| pxObjClass | PegaCA-Alert |
| pxPages(WG) | |
| Name | Value |
| pxSubscript | WG |
| pxObjClass | Index-PegaCA-Alert |
| WorkGroup | GreatLakesAndHorsham |
| pyDateTimeValue(1) | 20160503T194607.000 GMT |
3) Which time zone is the application server and database server in?
--> servers are in UTC timezone
Pegasystems
IN
Both startDate and endDate seem to be unoptimized columns from the SQL query. That is why they are using the UDFs (pr_read_from_stream function) in the query.
In the BLOB, we always store the date or datetime value in GMT. But SYSDATE will always be in the timezone of the database. In your case since the database is in UTC and the value in BLOB is also UTC, the difference in date should be working correctly.
WellsFargo
US
Rajiv,
Thanks for your response. Can we request for the server timezone be changed to EST since all the user group is based out of US?
Can this be a solution not a perfect one though?
Gowri
Pegasystems
IN
So I am still not able to understand why EST (or EDT) as the operator time zone is causing issues for you? Is this because converting it to GMT is changing the date and we are not considering the time part?
WellsFargo
US
Yes, the problem here it will not take the operator timezone. Let us say the operator is in EST zone, After 8 PM EST the current date time returned will be one day ahead as it is in GMT, when this is compared with the end date which is a date value with no time , alerts applicable alerts for that day will not appear
Pegasystems
IN
So this is expected behaviour and will happen the other way around if someone uses a time zone ahead of UTC. Since the data in the database does not have the time part, this will happen. Can we have the data stored in DB along with time so that you get accurate results?