topic Re: talend date conversion issue in Talend Studio

talend date conversion issue

bradsheridan — Mon, 28 Jan 2019 00:57:51 GMT

@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!!!

Re: talend date conversion issue

vapukov — Mon, 28 Jan 2019 01:12:34 GMT

Hi,

what's wrong with:

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

Re: talend date conversion issue

bradsheridan — Mon, 28 Jan 2019 01:19:44 GMT

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

Re: talend date conversion issue

bradsheridan — Mon, 28 Jan 2019 01:45:39 GMT

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

Re: talend date conversion issue

vapukov — Mon, 28 Jan 2019 02:30:44 GMT

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

😉

something wrong

Re: talend date conversion issue

bradsheridan — Mon, 28 Jan 2019 02:38:43 GMT

Re: talend date conversion issue

vapukov — Mon, 28 Jan 2019 02:52:35 GMT

btw, it not important - what it print

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

Re: talend date conversion issue

bradsheridan — Tue, 29 Jan 2019 15:05:53 GMT

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!