Five Stars

Oracle timestamp - milliseconds not stored

Hi, 

 

Discussion started on https://www.talendforge.org/forum/viewtopic.php?id=28436 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

 

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

Input_STRING_Timestamp.JPGInput

 

In mapping I do the following:

Mapping01.JPGmapping

Mapping_datatype.JPGmapping datatype

Output_datatype.JPGTarget datatype

target_adv.JPGTarget Adv settings

Result when running job:

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Five Stars

Re: Oracle timestamp - milliseconds not stored

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

 

timestamp.JPGtimestamp

Working now

2 REPLIES
Five Stars

Re: Oracle timestamp - milliseconds not stored

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

java.JPGcode 

Five Stars

Re: Oracle timestamp - milliseconds not stored

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

 

timestamp.JPGtimestamp

Working now