time difference in tmap

One Star

time difference in tmap

Hi
I cant find any solution to my problem anywhere...
I have one csv file that contains field with date/time (example HH:mm:ss as 19:01:23).
In the csv i have 2 field called Call_Origination_Time and Call_Termination_Time
I want to insert the time difference between these 2 fields into an mysql db table.
Tha field in the Mysql table where i want the result to be inserted is an Date-Time field.
Hav tried to do this via:
FileInputDelimited-tMap-tMysqlOutput
In my tMap i have tried to do the calculation with TalendDate.diffDate(TalendDate.parseDate("HH:mm:ss",row2.Call_Termination_Time), TalendDate.parseDate("HH:mm:ss",row2.Call_Origination_Time), "HH:mm:ss")
But it seems not possible to use TalendDate.diffDate if the inputfield is an date format?
Is there any solution to this? Should i use some other component to accomplish this?
/ Kristian
Community Manager

Re: time difference in tmap

Hi 
You don't use the function correctly, this function returns the differences between two Dates, you must specify the dataType part which to calculate, the dataType can be one of the following values:
"yyyy","MM","dd","HH","mm","ss","SSS"
You can read the source code of function from Repository-->Code-->Routines-->system-->TalendDate
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: time difference in tmap

Thanks for the answer!
I i underastand you right i cant use TalendDate.diffDate to accomplish what i want?
Is there any other way to accomplish what i want?
I have tried do on mysql update in an new talend job where the select look like this:
SELECT
ID,
`prefix`,
datum,
starttid,
sluttid,
dialednumber,
callingnumber,
username,
samtalstid,
SEC_TO_TIME(TIME_TO_SEC(sluttid) - TIME_TO_SEC(starttid)) AS Samtalstid_calc,
samtalstid_hhmmss
FROM all_calls
WHERE (samtalstid_hhmmss = '') OR samtalstid_hhmmss IS NULL
ORDER BY datum DESC, starttid DESC

My idea was to update just the field samtalstid_hhmmss with content from Samtalstid_calc, but having troubble wit this to...
Anyone having any idea what component and way to use?
/ Kristian

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 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog