Formating output to Teradata TIMESTAMP(6)

One Star

Formating output to Teradata TIMESTAMP(6)

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".
One Star

Re: Formating output to Teradata TIMESTAMP(6)

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.
One Star

Re: Formating output to Teradata TIMESTAMP(6)

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

Re: Formating output to Teradata TIMESTAMP(6)

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

Re: Formating output to Teradata TIMESTAMP(6)

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.
One Star

Re: Formating output to Teradata TIMESTAMP(6)

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