How to find difference between two timestamps in Talend HI

Question

I am new to Talend I want to find the difference between the two timestamps. I am having two columns start_time and end_time. I want to make a table in destination that will show the difference in both the timestamps, specifically I want to show hours mins and seconds. Also I want time in timestamp not in ling format, how can I achieve this

start_time- 2021-06-18 08:27:52.000000
end_time- 2021-06-18 08:29:59.000000

I tried- creating a variable 'ms' of long type in tmap = TalendDate.diffDate(row181.start_time,row181.end_time,"mm") for converting into hh:mm:ss

String.format("%02d:%02d:%02d.%d", (Var.ms / (1000 * 60 * 60)) % 24, (Var.ms / (1000 * 60)) % 60, (Var.ms / 1000) % 60, Var.ms % 1000)

if I make table as string I am getting this err-

column "call_duration" is of type bigint but expression is of type character varying

Above T-map expression returning zero also I have to use long in the destination column type, but I want date type

Answer 1

• Pattern "MM" refers to months, not minutes. Use "mm" instead.
• How could you return a date type for a difference between two dates ? The result is necessarily a number (long/double...) .
• If you want your output with hours/mins/seconds, you should use diffDate with "ss" pattern to get a long representing the duration in seconds. Then you'll have to transform this to get hours and minutes (e.g 3700 s would give you 1 hour, 1 minute, 40 seconds) . You also have to determine what kind of output you want (one column for each, a string with the concatenation of hours/minutes/seconds...)

Example : with row1.diffDate being your diffdate in seconds in input of a tMap, you could separate in three different columns. Then you'll only have to concatenate all values in a string. if you want a string output with ":" separator.