How to load Date columns with nanoseconds

[alex_cr]

Hello. 

I need to get some fields from an Oracle DB which have type TIMESTAMP(6) and then load them into another database and table with nanosecons acuracy. Is theare any way to do this.

I have some thoughts about it. Patch tOracleInput and tOracleOutput components to make them using java.sql.Timestamp type.

Is it possible to do that by other means?

[gjeremy1617088143]

HI,

I think java.time in java 8 doesn't support nanosecond

Send me Love and Kudos

[gjeremy1617088143]

I'ts seems you can workaround with dbtype Timestamp and java Type Object.

[Anonymous]

Hello,

java.sql.Timestamp supports nanosecond for DB, that is a SUN workaround for JDBC, and the date format have to be "yyyy-mm-dd hh:mm:ss.[fff...]"

Since java.util.Date is used in Talend, the micros / nanosecond precision is not available. However some components might check if the incoming Object type is java.sql.Timestamp in which case the nanoseconds are available as well. For this to be leveraged the Object type should be selected in the schema definition, and the source / target components needs to support it.Experiments worth execution

• Using a tFileOutput components dump / read these values. This way the Timezone could be controlled better.
• Use the -Duser.timezone parameter to see if this changes the behavior. More on this: https://docs.oracle.com/javase/9/troubleshoot/time-zone-settings-jre.htm#JSTGD359 
• Consult the JDBC drivers documentation how these timezones are handled.
• use tLogRow with the following pattern / result: "yyyy-MM-dd'T'HH:mm:ss.SSSXXX"
• 2001-07-04T12:08:56.235-07:00

Then the target database may or may not able to store the Timestamp part of the above examples, which combined with the timezone could lead to days shifting.

Possible Workarounds

Use Strings and see if the database or their driver can handle the transformation. With String what you see is what you have, no extra hidden information.

Convert between timezones using Java code in a tJavaRow

String pattern = "yyyy-MM-dd";

log.info(TalendDate.formatDate(pattern, input_row.startDate));

output_row.startDateUTC = TalendDate.parseDateInUTC("yyyy-MM-dd zzz", TalendDate.formatDate("yyyy-MM-dd", input_row.startDate)+" UTC");

Let us know if it helps.

Best regards

Sabrina