Support Article
BIX -u/-U time filtering not working as expected
Summary
Business Intelligence Exchange (BIX 2.3.1) when run from the command line allows users to provide -u/-U parameters to specify a start and end time range of the pxUpdateDateTime column to filter the extraction on.
-u <start date>
Only extract instances with a pxUpdateDateTime equal to or greater than <start date>. <start date> should be in the format “20110823T164017.000”, following the Java pattern “yyyyMMddTHHmmss.SSS”.
Note: Uncheck the Use Last Updated Time as Start checkbox on the Filter tab of the Extract form when you use this parameter, as it does not override the checkbox setting.
-U <end date>
Only extract instances with a pxUpdateDateTime equal to or less than <end date>. <end date> should be in the format “20110823T164017.000”, following the Java pattern “yyyyMMddTHHmmss.SSS”.
Note: Uncheck the Use Last Updated Time as Start checkbox on the Filter tab of the Extract form when you use this parameter, as it does not override the checkbox setting.
Since PRPC stores time data in Greenwich Mean Time (GMT) and there is no way to specify timezone with these parameters, User is expecting that these values should be a GMT but when running the extraction the wrong data is being returned.
Error Messages
Not Applicable
Steps to Reproduce
Run extraction from command line using -u/-U parameters
Root Cause
The root cause of this problem is software use/operation error. The timezone of the -u/-U parameters is the timezone of the machine that is running PRPC not GMT.
Resolution
The explanation for this behavior is as follows:
In the BLOB database column all date/time properties are saved in GMT. But if the column is exposed as it is with pxUpdateDateTime, then the data in the timezone of the application server in the exposed column.
For example, in blob if the value is 20131013 T 05 58 55.438 GMT for pxUpdateDateTime
if the default time zone in PRPC is EST then the value in the pxUpdateDateTime column is 20131013 T 01 58 55.438(GMT-4).
if the default timezone is IST then the value in the pxUpdateDateTime column is 20131013 T 11 28 55.438(GMT+5.5)
Since -u and -U will use the exposed pxUpdateDateTime column, when running BIX from the command line make sure you are running in the same timezone as the PRPC application server and that the -u/-U parameters are appropriate to this timezone.
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.