Oracle timestamp - milliseconds not stored

Highlighted
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?

 


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

View solution in original post


All 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

View solution in original post

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog