How to load Date columns with nanoseconds

alex_cr — Fri, 15 Nov 2024 23:58:54 GMT

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?

Re: How to load Date columns with nanoseconds

gjeremy1617088143 — Mon, 05 Jul 2021 13:57:11 GMT

HI,

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

Send me Love and Kudos

Re: How to load Date columns with nanoseconds

gjeremy1617088143 — Mon, 05 Jul 2021 14:03:59 GMT

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

Re: How to load Date columns with nanoseconds

Anonymous — Tue, 06 Jul 2021 03:54:14 GMT

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

topic Re: How to load Date columns with nanoseconds in Talend Studio

How to load Date columns with nanoseconds

Re: How to load Date columns with nanoseconds

Re: How to load Date columns with nanoseconds

Re: How to load Date columns with nanoseconds