How to calculate difference between 2 time fields.

[Anonymous]

I have 2 time columns in 'hh,mm,Ss' format . which I loaded like this :

Time#(TIME_24_HOUR_HHMMSS_FORMAT) as call_start_time_24

Time#(WEB_END_TIME) as web_end_time;

I want to know How to calculate difference between and I want to know if that difference is less than 15 min.

I am tryting something like this :

if(interval(time(call_start_time_24,'hh:mm:ss')-time(web_end_time,'hh:mm:ss'),'mm')<=15 ,'Online prior to call - web session started and ended before the call - 15 mins').

Dosent seem to working. Am I doing anything wrong here.

[sunny_talwar]

Try this

If(call_start_time_24 - web_end_time <= MakeTime(0, 15) ,'Online prior to call - web session started and ended before the call - 15 mins')

[Anonymous]

I tried I am still getting null results. I thought there might be something wrong with my column format. So I tried loading like this in load script

time(Time#(TIME_24_HOUR_HHMMSS_FORMAT),'hh:mm:ss') as call_start_time_24

time(Time#(WEB_END_TIME),'hh:mm:ss') as web_end_time;

. Still getting null results. Tried changing minute number still getting null.

How can I see what exact data is being loaded into that column. If I want to check?

[Anonymous]

I just checked I am not getting any values in that column Why would that be. An I doing anything wrong while loading .

The columns contains data in varchar format in my source like this. I am importing these columns with time#() function and not getting any values in those columns

Same with other column :

How to import these column in time format and compare.

[sunny_talwar · Accepted solution]

Try this

If(Time#(call_start_time_24, 'hh:mm:ss') - Time#(web_end_time, 'hh:mm:ss') <= MakeTime(0, 15) ,'Online prior to call - web session started and ended before the call - 15 mins')

[vcanale]

try
=if(Interval(Time(call_start_time_24, 'HH:mm:ss') - Time(web_end_time, 'HH:mm:ss'))<='00:15:00', 'Online prior to call - web session started and ended before the call - 15 mins')

[Anonymous]

Thanks that worked.