By default, the DataSync Agent will set datetime fields with the timestamp data type which will not have a time zone. The PostgreSQL JDBC driver as used by the Agent does not support a time zone parameter so any datetime values are passed in based on the time zone of the server where the Agent is running. If you are running the Agent on a server with a non-UTC time zone and want to ensure records are saved properly so you can query the PostgreSQL database in different time zones, do the following: Divbox |
---|
|
UI Steps |
---|
|
UI Step |
---|
Acquire the databases.xml configuration file from Perspectium Support.
Move databases.xml into the Agent's conf folder i.e. <Perspectium_Replicator_Agent_Installed_Directory>/conf Then, in databases.xml, you will need to change the following values in the <database_type>postgresql</database_type> section: Old Value | New Value |
---|
TIMESTAMP | TIMESTAMP WITH TIME ZONE |
Snippet of databases.xml: Code Block |
---|
<database>
<database_type>postgresql</database_type>
<datatypes>
.
<mapping sn_type="93" db_type="93">TIMESTAMP WITH TIME ZONE</mapping>
.
</datatypes>
</database> |
|
UI Step |
---|
Edit the wrapper.conf located in the Agent's conf folder i.e. <Perspectium_Replicator_Agent_Installed_Directory>/conf to add a new Java Additional Parameter. Look for the line # Java Additional Parameters section and the highest number parameter listed and then add the following line: wrapper.java.additional.<Highest Number + 1>=-Duser.timezone=GMT Replacing <Highest Number + 1> with the current highest number parameter and adding one. For example, if the current highest number 3, this value would be 4 i.e. wrapper.java.additional.4=-Duser.timezone=GMT Snippet of wrapper.conf: Code Block |
---|
# Java Additional Parameters
wrapper.java.additional.1=-Djava.util.logging.config.file=../conf/logging.properties
wrapper.java.additional.2=-Dlog4j.configurationFile=../conf/log4j2.xml
wrapper.java.additional.3=-Dfile.encoding=UTF-8
wrapper.java.additional.4=-Duser.timezone=GMT |
This will ensure the PostgreSQL JDBC driver is saving data into the database in UTC time. You can also modify the server the Agent is running on and set its timezone to UTC if you prefer not to make this change to the wrapper.conf. |
|
|
Since ServiceNow's data is sent out is in UTC time, doing the above ensures data is saved properly in UTC time in the database. You can now query in different time zones by using the SET TIME ZONE '<TIME ONE>'; SQL command where <TIME ZONE> is the desired time zone to query in. For example: SET TIME ZONE 'US/Eastern'; Will allow you to query for records in the US Eastern Timezone. See SET for more information. NOTE: Using SET only applies the time zone for the current session. Contact your Database/System Administrator to change the default time zone for your PostgreSQL environment as this may require modifying the postgresql.conf main configuration file and restarting the PostgreSQL service.
|