Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
grizmi
Contributor II
Contributor II

Oracle timestamp - milliseconds not stored

Hi, 

 

Discussion started on https://community.talend.com/t5/Design-and-Development/Load-DATE-type-into-an-Oracle-TIMESTAMP-colum... but I have the same issue and can't find a solution. Can't store the milliseconds in a timestamp target colum in Oracle, but when I store it in a varchar2 column I can see the milliseconds. So it looks like that the datatype "Date" with pattern "yyyy-MM-dd HH:mm:ss.SSS" is losing the SSS part.

Talend version: 6.4.1

Oracle: 11gR2

 

0683p000009Lsri.jpgDatabase Oracle_target

I select an Oracle timestamp source column but I convert into a string using SQL:

 to_char(le.STARTTIME ,'yyyy-MM-dd HH24:MI:ss.FF3') AS SERVICE_STARTTIME

Example: '2017-12-13 15:25:16.656'

0683p000009LsIq.jpgInput

 

In mapping I do the following:

0683p000009Lsm0.jpgmapping

0683p000009Lsrx.jpgmapping datatype

0683p000009Lss2.jpgTarget datatype

0683p000009Lsqh.jpgTarget Adv settings

Result when running job:

0683p000009LsqM.jpgTarget Result

As you can see, when stored as a string I can see the milliseconds but when this is a timestamp it's lost. Any idea's on how to resolve this?

 

Labels (1)
1 Solution

Accepted Solutions
grizmi
Contributor II
Contributor II
Author

Found the reason, in target I must specify 'TIMESTAMP' as DB Type, by default you will have 'DATE'

 

0683p000009LsrU.jpgtimestamp

Working now

View solution in original post

2 Replies
grizmi
Contributor II
Contributor II
Author

Some extra info, this is what I see in the code:

0683p000009LssC.jpgcode 

grizmi
Contributor II
Contributor II
Author

Found the reason, in target I must specify 'TIMESTAMP' as DB Type, by default you will have 'DATE'

 

0683p000009LsrU.jpgtimestamp

Working now