talend date conversion issue

[bradsheridan]

@vapukov hoping you can help me (yet again ) !

here is a line from a JSON response that I'm getting from Microsoft TFS:

"Microsoft.VSTS.Common.StateChangeDate": "2017-03-30T18:40:49.953Z"

What I'm trying to do is store this date in my Redshift table which is set as type TIMESTAMP.  I would like the value to be:

2017-03-30 06:40:49

I've tried several routines under the TalendDate section in my tMap Expression Builder, but get all kinds of different errors.

Thoughts??

thanks!!!

[vapukov]

Hi,

what's wrong with:

TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.sss'Z'",row1.str) 

?

[bradsheridan]

Once again @vapukov , thanks so much for the speedy reply.

I'm pretty sure I've tried that...it looks familiar.  That being said, I've tried about 1MM different things . Let me give it a whirl....

[bradsheridan]

Well @vapukov is once again correct!

Here is what the 'test' in the expression builder looks like and what it returned.  But I still need a different output format....

Expression:  TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.sss'Z'",row4.statechangedate )

Test Value:  2017-03-30T18:40:49.953Z

Test Result:  Thu Mar 30 18:55:53 EDT 2017

Almost there....I'm looking for: 2017-03-30 18:55:53 (no day of week, no timezone)

So while the Expression Builder test worked, when i then try to run the job and output to tLogRow, I get the attached error.  Perhaps it's b/c 1) the JSON is actually returning a string that needs to be converted or 2) I have a field type set incorrectly somewhere?

Talend_date_error.docx

[vapukov]

[statistics] connecting to socket on port 3617
[statistics] connected
2017-03-30 18:55:53
[statistics] disconnected

😉

something wrong

[bradsheridan]

[vapukov]

btw, it not important - what it print

if column type datetime - it will insert the proper value into redshift

[bradsheridan · Accepted solution]

somehow I got this solved and without any Date functions....weird.  So I thought I'd share with the Community.

I have:
tRestClient -> tExtractJSONFields -> tMap -> Redshift

The important parts that made my job work with a TZ type timestamp field are:

1) Date fields on database table need to be of TIMESTAMP format

2) the output schema of the tExtractJSONFields need to have a 'date pattern' of: "yyyy-MM-dd HH:mm:ss"

3) the output schema of the tMap need to have a 'date pattern' of: "yyyy-MM-dd'T'HH:mm:ss"

Hope this helps someone with the same struggle I had.

Thanks again to @vapukov for providing guidance!