Question
How to calculate DateTime values in report using operator's timezone?
I have a report where I must select cases that were resolved in a specific timeframe
I'm using functions like Day of the week of and Difference in days between to compare dates in the required logic, but I see it doesn't take the operator's timezone (MST), this results in incorrect report results where some cases that fall in one day in MST but are actually in the next one in GMT are not being selected.
This report is being used in the My Reports tab and thus, can't implement a response data transform in a data page, and there doesn't seem to be a way to transform the dates using activities or java functions either.
I'll answer my own post for future reference.
Since both function aliases are final, I created custom ones using the OOTB as a base, then wrapped the DateTime params as described below:
Edit: Please note this SQL function is for Postgresql only, and your app might use a different DB system.
OOTB:
Custom:
What this does is simply set GMT as base timezone to the datetime value from the param, then convert it to the operator's defined timezone, I had to do the first part because we use Postgresql and this system stores datetime values with no defined timestamp. I hope this helps anyone until Pega supports timezones in their function aliases for Report Definitions.