Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
bradsheridan
Contributor III
Contributor III

talend date conversion issue

@vapukov hoping you can help me (yet again 0683p000009MACn.png) !

 

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

Labels (3)
1 Solution

Accepted Solutions
bradsheridan
Contributor III
Contributor III
Author

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!

View solution in original post

7 Replies
vapukov
Master II
Master II

Hi,

 

what's wrong with:

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

?

bradsheridan
Contributor III
Contributor III
Author

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 0683p000009MACn.png . Let me give it a whirl....

bradsheridan
Contributor III
Contributor III
Author

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
Master II
Master II

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

😉

 

something wrong

bradsheridan
Contributor III
Contributor III
Author

0683p000009MACn.png

vapukov
Master II
Master II

btw, it not important - what it print

 

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

bradsheridan
Contributor III
Contributor III
Author

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!