Calculating Time between time stamps

[#1]

I've been trying to modify this statement in this post:

http://community.qlik.com/thread/79893

I'm dealing with pure time stamps, no dates, and I need to calculate the time between multiple time stamps.

I read somewhere there is a problem with using MM for minutes.

Can I modify this or other statements to calculate time:

=Ceil(Interval(Date#([Field2], 'MM/DD/YYYY hh:mm:ss TTT') - Date#([Field1], 'MM/DD/YYYY hh:mm:ss TTT'), 'DD' ))

or are there other options?

Thank you for your help!

[jpapador]

Have you tried, Num(Field2) - Num(Field1).  That will give you the difference in days.  You can multiply by 60 once or twice to get it into hours or minutes. 

[#3]

Thank you, I will try that.  Looking for time in minutes.

I do get negative numbers when the time crosses a calendar date.  Thoughts?

[Anonymous]

Rebecca

MM is for a 2 digit month.

mm is for minutes.

Interval() is a good function to use, as per this example.

Interval (

  date#( '12/26/2013 12:12:12' , 'MM/DD/YYYY hh:mm:ss')

  -

  date#( '12/25/2013 22:22:22' , 'MM/DD/YYYY hh:mm:ss')

, 'mm' )

Best Regards,     Bill

[#5]

Thank you Bill.

I am working with pure timestamps, so I am now modifying my load statement to create DTS:

Hopefully then I can try your suggestion.

[#6]

I added the following to my load statement to my load statement to obtain DTS.

(Timestamp#(ReceiveDateODBC & ' '& ReceiveTimeODBC,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYY hh:mm:ss') as ReceiveDTS,

The results are as follows:

BatTstCode	RecDateODBC	ReceiveTimeODBC	ReceivedDTS	ResultDateODBC	ResultTimeODBC	ResultDTS
ADMIN	22-Nov-13	16:26:00	41600 0.68472222222222	6-Dec-13	15:43:00	41614 0.65486111111111
ADMIN	22-Nov-13	16:26:00	41600 0.68472222222222	22-Nov-13	16:27:00	41600 0.68541666666667
UCUL	22-Nov-13	16:26:00	41600 0.68472222222222	23-Nov-13	8:40:00	41601 0.36111111111111
UCUL	22-Nov-13	16:26:00	41600 0.68472222222222	24-Nov-13	10:14:00	41602 0.42638888888889

Any pointers?

[Anonymous]

Rebecca

Try this :

TimeStamp ( Timestamp#(ReceiveDateODBC & ' '& ReceiveTimeODBC,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')  as ReceiveDTS,

Best Regards,,    Bill

[#8]

Thank you Bill for all your help.  The output is still not usable for calculating the time between events.  If I could work strictly from time, there would be no issue, but the crossing of calendar day gives me bad results.

Any other pointers?

[#9]

Generally we can use the Interval function to calculate the difference b/n time stamps. But the actual format of Interval function is time format. For example if you want calculate the difference the time in Hours, first calculate in Seconds and then convert this seconds into Number. Once we get the difference in Seconds we can convert into any hours, days or Minutes.

Num#(Text(Intervale(Timestamp1 - Timestamp2 , 's'))) AS DIFF_IN_SEC

[#10]

Thank you for your response.

I have tried the interval function, but will try this syntax as well. Unfortunately, my time stamps can cross days so I'm getting inaccurate results.....