My Oracle Support Banner

TIMEZONE WITH TIMESTAMP Milliseconds Truncated When A BEFORE INSERT trigger Is In Place On The Target Table (Doc ID 2305649.1)

Last updated on AUGUST 30, 2023

Applies to:

JDBC - Version and later
Information in this document applies to any platform.


TIMESTAMP WITH TIME ZONE datatype value getting rounded off to nearest second if the value is inserted from JDBC.

When using JDBC driver 11g or 12c, inserting a Timestamp into an Oracle TIMESTAMP WITH LOCAL TIME ZONE datatype truncates the milliseconds portion of the value, when the following set of factors are present:

1.  Inserting the timestamp using the standard Java method PrepardStatement.setTimestamp
2.  A BEFORE INSERT trigger is in place on the target table
3.  The trigger evaluates the incoming Timestamp value for NULL, and if NULL, sets the value at SYSTIMESTAMP

The truncation does NOT reproduce when performing an insert to the target table from SQL*Plus with the trigger in place, when performing without a BEFORE INSERT trigger in place, when inserting SYSTIMESTAMP directly into the table from SQL*Plus, when the target column is a TIMESTAMP rather than a TIMESTAMP WITH TIME ZONE or a TIMESTAMP WITH LOCAL TIME ZONE, or when deliberately inserting NULL from the Java side.




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.