How to Control the Time Zone for Which PostgreSQL ODBC Drivers Fetch Timestamps

Description

PostgreSQL supports a column datatype of Timestamp With Time Zone or TSWTZ that stores a timestamp along with its respective time zone. By default, the Platform Analytics Consumer persists all records in the UTC time zone. However, since the time zone information is stored with the timestamp, it can be retrieved easily in any time zone offset. By default, the ODBC driver fetches data at the time zone set at the global level for the server.

Solution

To edit the global server time zone, change the time zone parameter in the postgresql.conf file. See the Client Connection Defaults page in the PostgreSQL documentation for more information about the time zone parameter.

Example of the postgresql.conf file:

To see a list of supported time zones, run the following query on PostgreSQL instance:

SELECT * FROM pg_timezone_names;

Alternate Solution

This can also be controlled at the Strategy database instance level by altering the Report Pre-Statement for the Platform Analytics Database instance. Please note that this only affects reports and OLAP cubes, not MTDI cubes such as the Platform Analytics cube.
You can modify this setting using Strategy Developer or Strategy Command Manager.
In Strategy Command Manager:

1. Run the following command:
   
   

   ALTER VLDBSETTING PROPERTYNAME "Report Pre Statement 1" PROPERTYVALUE "SET TIME ZONE 'US/Eastern'" FOR DBINSTANCE "Platform Analytics";
   

   
   
   
2. Restart the Intelligence server.
   
   

In Strategy Developer:

• Open the project source containing the Platform Analytics project.
  
  
• Navigate to Administration > Configuration Managers > Database Instances.
  
  

• Right-click the Platform Analytics database instance and choose VLDB Properties. 
  
  

• Navigate to Pre-Post Statements > Report Pre Statement 1 (or any other number depending on the existing pre-statements).
  
  

• Uncheck Use default inherited value.
  
  
• Enter SET TIME ZONE 'US/Eastern'; into the Report Pre Statement box, using the appropriate time zone. If necessary, use the command listed above to get a list of time zones.
  
  

• Click Save and Close.
  
  
• Restart the Intelligence server.