[resolved] Transform bigdecimal to timestamp

[Anonymous]

Hello community,
I need to transform an Oracle db-column of type BigDecimal (in Talend), which represents the number of milliseconds from a certain point in time on, into a human readable format, e.g. a timestamp (2011-11-08 10:59:10.38), using the tMap+Var expression component.
I tried things like that:
row2.millis ==null ? "" : new java.sql.timestamp(row2.millis.longValue()).toString();
This terminates with a compile error. Do I have to import libs to the build path or something?
I am pretty new to talend. Can someone please tell me, how this is done properly? Thanks in advance!

[Anonymous]

Hi, to test if a variable is null, use Relational.ISNULL(row2.millis). Then I don't usertand, you row2.millis is only a BigDecimal which represents the milliseconds or is it a timestamp?

[Anonymous]

Hi I think you could use the following,
but again don't really understand you an example of your bigDecimal could help:
TalendDate.parseDate("yyyy-MM-dd HH:mm:ss.SSS",row2.millis.longValue().toString());
Regards,
Brandon

[Anonymous]

Hey, thanks for your reply! I will change the "isNull-test"!
rows2.millis is a field in a db-table of type Number(35,0), that is translated to BigDecimal by Talend (DB Schema retrieval). This number counts the milliseconds from a certain point in time (e.g. 1.1.1970 00:00:00:000). What I need is to recalculate the timestamp meaning the actual date and time.
Input (BigDecimal): "23483265123" -- > Output (String): "22.11.2011 - 15:30:14:345"
I just dont know how this is done correctly with the talend tMap component.
Cheers

[Anonymous]

Hello Brandon,
your suggestion
TalendDate.parseDate("yyyy-MM-dd HH:mm:ss.SSS",row2.millis.longValue().toString());
does not work, because you can't call .toString() on a primitive type long and i need to calculate the date first, right?

[Anonymous]

Hi wi-ler
you can use the following code in your tmap:
TalendDate.parseDate("ss",String.valueOf(row2.test.divide(new BigDecimal("1000"))))
and make sure you set your output shown in the picture attached.
Regards,
Brandon

[Anonymous · Accepted solution]

Hello Brandon,
my latest solution looks as follows:
Relational.ISNULL(row2.millis) ? null : new java.util.Date(row2.millis+OFFSET);
Before that I convert the BigDecimal into a long with tConvertType. Then I set the date pattern as you said.
Thanks for your help!