Date and Time on Clarity servers do not match

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

We notice a mismatch in App and DB server times when the following is done.

1. We fetch DB date time using a query through Gel Script
2. We compare with the time at which the process gets executed.

A sample screenshot of how the comparison is made is provided below.

Environment

Version: 15.9.3

Resolution

1. To check if there is a mismatch in DB and App server timings, you can navigate to Administration -> Security and Diagnostics -> Health Report -> "Data Warehouse" tab, and look at the "Application and Database Timezone Match" row. Any mismatch would be reported here
2. If comparison is being made through Gel Script, ensure that the proper time function is used. Conversions into character data type can sometimes turn incorrect, and can show an apparent mismatch.
   1. A sample Gel Script (to be used against a Postgres DB) would be as follows. For other DBs, use the appropriate time functions.
      

      <gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
      		xmlns:core="jelly:core"
      		xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      		xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
      		xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
      		xmlns:sql="jelly:sql"
      		xmlns:xog="http://www.niku.com/xog"
      		xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">	
      	<gel:setDataSource dbId="niku"/>
      	<sql:query var = "result">
      		<![CDATA[
      			select now() time_test
      		]]>
      	</sql:query>
      	<gel:log> ${result.rows[0].time_test} </gel:log>
      </gel:script>​

   2. Comparison using its result is marked in Green below