Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

time after midnight

Dear All,

How best can i deal with a situation where end time is after midnight and i want to get the duration of a event in mins, that is end time - start time. my solution had eluded date and i have already loaded lots of data to it, which is why i am looking for a workaround.

he is my script

If (interval(time(end_time)  - Time(start_time) ) < 0, 0, ((time(end_time) - time(start_time))*1440

my start time is 15:01 and end time is 00:37, this is leading to wrong results

Thanks in advance

3 Replies
marcus_sommer

I assume that you have also a date-part to this times and could use something like this:

interval((end_date + end_time)  - (start_date + start_time), 'hh:mm')

- Marcus

Anonymous
Not applicable
Author

yes will do that

MarcoWedel

Hi,

if there is no date information available and the duration can be 1 day at max then one solution might be:

QlikCommunity_Thread_231582_Pic1.JPG

table1:

LOAD *,

    fmod(end_time-start_time+1,1)*1440 as duration_minute

Inline [

start_time, end_time

15:01, 00:37

15:02, 01:37

15:03, 02:37

15:04, 03:37

15:05, 04:37

15:06, 05:37

15:07, 06:37

15:08, 07:37

15:09, 08:37

15:10, 09:37

15:11, 10:37

15:12, 11:37

15:13, 12:37

15:14, 13:37

15:15, 14:37

15:16, 15:37

15:17, 16:37

15:18, 17:37

15:19, 18:37

15:20, 19:37

15:21, 20:37

15:22, 21:37

15:23, 22:37

15:24, 23:37

];

hope this helps

regards

Marco