Oracle: Date Time Truncation

This will require Data Admin privileges to setup and Builder for implementation.

Issue:

In Oracle, the DATE data type is commonly used to store both a date and time in a table. For example, an Oracle DATE column could be used to store the value:

'2007-08-17 15:42:40'

Under some circumstances, when iDashboards reads data from an Oracle DATE column, only the date portion (year-month-day) is returned, and the time component is truncated, which means the value above would be read as:

'2007-08-17 00:00:00'

Resolution:

This is due to changes Oracle made to their JDBC driver, as of version 9.2. Prior versions of the driver behave in the expected manner. Fortunately, Oracle provides a way to cause newer versions of their JDBC driver to return the time component of a DATE column as well as the date component. This is accomplished by setting the following driver property for the Oracle data source:

oracle.jdbc.V8Compatible=true

In iDashboards Admin or Data Admin: Select your Oracle Data Source, open its properties screen, and enter the above line in the field labeled Optional Driver Properties.

If iDashboards is reading the data from its repository database, the driver property can be set by adding the following line to the ivizgroup.properties file, and restarting the application server:

db.driverProperties=oracle.jdbc.V8Compatible=true

When multiple driver properties must be set, the name=value pairs should be separated by semicolons:

oracle.jdbc.J2EE13Compliant=true;oracle.jdbc.V8Compatible=true

For More Information:

• Oracle JDBC Driver Download
• Oracle Database Documentation
• Oracle JDBC Driver Documentation
• iDashboards Admin Manual 10. Managing Data Sources

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.