Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

[resolved] Transform bigdecimal to timestamp

Highlighted
One Star

[resolved] Transform bigdecimal to timestamp

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!

Accepted Solutions
Highlighted
One Star

Re: [resolved] Transform bigdecimal to timestamp

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!

View solution in original post


All Replies
Highlighted
One Star

Re: [resolved] Transform bigdecimal to timestamp

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?
Highlighted

Re: [resolved] Transform bigdecimal to timestamp

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

Re: [resolved] Transform bigdecimal to timestamp

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

Re: [resolved] Transform bigdecimal to timestamp

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?
Highlighted

Re: [resolved] Transform bigdecimal to timestamp

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

Re: [resolved] Transform bigdecimal to timestamp

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!

View solution in original post

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog