KB484854: Platform Analytics reports show a difference in tran_date and tran_timestamp values when using PostgreSQL database timezone

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Description

When using the Postgresql ODBC Timezone functionality at a timezone other than UTC, a difference can be observed for the

tran_date

and

tran_timestamp

values of a record that the timezone difference crosses a date threshold.
The following screenshot is an example:

In the above example, the PostgreSQL database is set for US/Eastern and the transaction timestamp itself occurred at 12-NOV-2020 00:56:17 UTC. However, it is observed that the

tran_timestamp

 value has been adjusted to be on November 11th, while the

tran_date

value is still retained as November 12th.

Why is this happening?

The Platform Analytics Consumer handles all data in UTC time and not in the timezone which is set on the PostgreSQL service. When inserting the rows into the repository, the datatype is converted at that time from timestamp to date, giving the UTC date and not the date that corresponds with the converted timezone, which is done at ODBC fetch.

Workaround

Since the timezone specified for conversion during ODBC fetch can be specified as either the database timezone or via parameter passed as part of the connection if the Platform Analytics Consumer inserted at a different non-UTC timezone, then it is still possible to have a discrepancy if the ODBC setting was used to choose a distinct timezone at client connection.
Strategy is currently investigating internally how to better support this PostgreSQL function.
As an immediate resolution, the method of pulling the data can be changed. The out-of-the box Platform Analytics dossiers utilize a set of views to reduce and filter data for better performance when publishing the Intelligent cube. By casting the

datetime

value of

tran_timestamp

to date as part of the fetch call against the views, instead of utilizing the existing

tran_date

column, the matching date will be returned.
Attached to this article is a SQL file for Strategy Secure Enterprise 2020 Update 3 that will backup and input four new view definitions. The view definitions cast the value at fetch time to take advantage of the PostgreSQL timezone conversion function. While the SQL takes a backup, if it is desired to return to the original view definition whenever making any direct changes to a warehouse in use for Strategy Secure Enterprise, a backup should be taken prior.
As part of applying any upgrades, the view definitions inside of the PostgreSQL repository will be updated and the SQL file will need to be re-applied.