topic Re: Formating output to Teradata TIMESTAMP(6) in Talend Studio

Formating output to Teradata TIMESTAMP(6)

Anonymous — Sat, 16 Nov 2024 12:43:31 GMT

Does anyone have experience formating TIMESTAMP fields when loading to Teradata. I do not see any format string beyond milliseconds and I need a scale of 6 such as in "2011-12-31 11:39:02.012345".

Re: Formating output to Teradata TIMESTAMP(6)

Anonymous — Tue, 23 Aug 2011 02:09:38 GMT

In Teradata, this time unit is called microseconds. I have not tried this Java method, but it may point you in the right direction (see link).
http://download.oracle.com/javase/6/docs/api/java/util/concurrent/TimeUnit.html#toMicros(long)
IIRC, the year portion of the date in Teradata by default is stored as years from 1900.
So December 31st, 2011 is formatted as YYYMMDD or 1111231. 111 because 2011-1900=111.

Re: Formating output to Teradata TIMESTAMP(6)

Anonymous — Tue, 23 Aug 2011 14:00:33 GMT

Thank you. That is helpful. Does anyone know if there is a format string available for microseconds?

Re: Formating output to Teradata TIMESTAMP(6)

Anonymous — Tue, 23 Aug 2011 17:30:12 GMT

If you are unable to generate the precision, you could fill the format with zeros. hhmmssSSS000

Re: Formating output to Teradata TIMESTAMP(6)

Anonymous — Tue, 23 Aug 2011 21:54:57 GMT

Understand and I was able to do that immediately with no issue. However, I very often MUST deal with all 6 digits of scale so I need a good answer eventually.

Re: Formating output to Teradata TIMESTAMP(6)

Anonymous — Fri, 01 Nov 2013 15:26:36 GMT

We are performing something similar where we bring in the timestamp from a db2 system (up to the microsecond precision) and writing it to a Teradata database using timestamp(6). The Date (java.util.Date) only gives you millisecond precision so the last 3 decimal digits are replaced with zeros. We got around this by using the "Object" Java type and "Timestamp" DB types in the schema definitions in the input, tmap and output components and it worked flawlessly. I assume the Java Type stored is probably a java.sql.Timestamp so if you need to manipulate in the tmap, you will most likely need to Java cast it. In our case, we didn't have to. We are currently using TOS 5.0.2.
Noel