topic Calculating Time between time stamps in QlikView

Calculating Time between time stamps

Thu, 26 Dec 2013 17:04:46 GMT

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!

Re: Calculating Time between time stamps

jpapador — Thu, 26 Dec 2013 17:09:04 GMT

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.

Re: Calculating Time between time stamps

Thu, 26 Dec 2013 17:13:29 GMT

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

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

Re: Re: Calculating Time between time stamps

Anonymous — Thu, 26 Dec 2013 17:21:59 GMT

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

Re: Calculating Time between time stamps

Thu, 26 Dec 2013 18:54:38 GMT

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.

Re: Calculating Time between time stamps

Thu, 26 Dec 2013 19:27:10 GMT

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?

Re: Re: Calculating Time between time stamps

Anonymous — Thu, 26 Dec 2013 19:43:13 GMT

Rebecca

Try this :

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

Best Regards,, Bill

Re: Re: Calculating Time between time stamps

Thu, 02 Jan 2014 20:22:36 GMT

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?

Re: Calculating Time between time stamps

Thu, 02 Jan 2014 20:52:31 GMT

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

Re: Re: Calculating Time between time stamps

Thu, 02 Jan 2014 21:08:13 GMT

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.....

Re: Calculating Time between time stamps

Thu, 02 Jan 2014 21:47:49 GMT

Hi Rebecca,

Try this code to combine your date and time.

date#(ReceiveDateODBC , 'DD-MMM-YY') + Timestamp#(ReceiveTimeODBC, 'hh:mm:ss') as ReceivedDTS

=(date#('22-Nov-13', 'DD-MMM-YY')) + Timestamp#('16:26:00', 'hh:mm:ss')

>> 41600.684722222

Date(floor(41600.684722222), 'MM/DD/YYYY') >> 11/22/2013

Timestamp(frac(41600.684722222), 'hh:mm:ss') >> 16:26:00

you can add or subtract two different dates and then calculate the time.

i.e.

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

=(date#('6-Dec-13', 'D-MMM-YY')) + Timestamp#('15:43:00', 'hh:mm:ss')

(date#('22-Nov-13', 'D-MMM-YY')) + Timestamp#('16:26:00', 'hh:mm:ss') >> 15.33958333332

=Timestamp(frac(15.33958333332), 'hh:mm:ss') >> 08:09:00

15 days and 8 hours and 9 minutes

Re: Re: Calculating Time between time stamps

Thu, 02 Jan 2014 22:49:44 GMT

Still trying:

Here are all the different versions in my load statement:

date#(Timestamp#(ReceiveDateODBC & ' '& ReceiveTimeODBC,'DD/MM/YYYY hh:mm:ss')) as ReceiveDTS,
date#(Timestamp#(ReceiveDate & ' '& ReceiveTime,'DD/MM/YYYY hh:mm:ss')) as ReceiveDTS1,
Timestamp#(ReceiveDateODBC & ' '& ReceiveTimeODBC,'DD/MM/YYYY hh:mm:ss') as ReceiveDTS2,
TimeStamp ( Timestamp#(ReceiveDateODBC + ReceiveTimeODBC,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as ReceiveDTS3,
TimeStamp ( Timestamp#(ReceiveDate + ReceiveTime,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as ReceiveDTS4,

Output for DTS3 and DTS4 were null

And here is the output. You can see the output is still off and does not convert to an accurate DTS. There is a space and a leading zero between the date (41614) and the time (0.040972) on the created DTS.

ReceiveDateODBC	ReceiveDate	ReceiveTimeODBC	ReceiveTime	ReceiveDTS	ReceiveDTS2
6-Dec-13	13855	0:59:00	0059	41614 0.040972222222222	41614 0.040972222222222
6-Dec-13	13855	0:59:00	0059	41614 0.040972222222222	41614 0.040972222222222
6-Dec-13	13855	3:27:00	0327	41614 0.14375	41614 0.14375
6-Dec-13	13855	3:27:00	0327	41614 0.14375	41614 0.14375

Re: Calculating Time between time stamps

Fri, 03 Jan 2014 00:13:39 GMT

Hi Rebecca,

This should work if the format is correct.

date#(ReceiveDateODBC , 'D-MMM-YY') + Timestamp#(ReceiveTimeODBC, 'hh:mm:ss') as ReceivedDTS

Timestamp#(ReceiveDateODBC & '_' & ReceiveTimeODBC, 'D-MMM-YY_hh:mm:ss') as ReceivedDTS

However, combining date and time will increase your data size. Try to calculate the difference and save the result if needed or calculate the time from your expression.

Or you can try to save the data separately

num(date#(ReceiveDateODBC , 'D-MMM-YY')) as ReceivedDate

num(Timestamp#(ReceiveTimeODBC, 'hh:mm:ss')) as ReceivedTime

ie. Your date and time format from the database: 6-Dec-13 and 00:59:00

num(date#('6-Dec-13' , 'D-MMM-YY')) = 41614

num(Timestamp#('00:59:00' , 'hh:mm:ss')) = 0.040972222224809

Your date and time format from the database: 2013-12-06 and 00:59:00

num(date#('2013-12-06' , 'YYYY-MM-DD')) = 41614

num(Timestamp#('00:59:00' , 'hh:mm:ss')) = 0.040972222224809

ReceivedDate+ReceivedTime = 41614.040972222224809

Re: Re: Re: Calculating Time between time stamps

Fri, 03 Jan 2014 01:48:29 GMT

Please find the attached qvw for reference.

SAMPLE:

LOAD * , Num(DIFF_IN_SEC/3600,'#####0.00') AS Hours_Diff;

LOAD * ,

Num#(Text(Interval(ResultTS-ReciveTS , 's'))) AS DIFF_IN_SEC

;

LOAD BatTstCode,

RecDateODBC,

ReceiveTimeODBC,

Timestamp#(RecDateODBC & ' ' & ReceiveTimeODBC , 'DD-MMM-YYYY mm:hh:ss') AS ReciveTS ,

ResultDateODBC,

ResultTimeODBC,

Timestamp#(ResultDateODBC & ' ' & ResultTimeODBC , 'DD-MMM-YYYY mm:hh:ss') AS ResultTS

FROM timestamp.txt (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Re: Re: Re: Calculating Time between time stamps

Mon, 06 Jan 2014 20:12:07 GMT

Thank you for this feedback. One of my coworkers just suggested I mention I'm using a Cache database and that this might change your responses to me?

I have tried the various concatonation, joins, num, interval, timestamp#,date# and still am not able to create a valid DTS.

Still at it if anyone has any new suggestions.

Thank you kindly!

Re: Calculating Time between time stamps

Mon, 06 Jan 2014 20:16:46 GMT

It would be more helpful, if you provide some samples.

Re: Re: Calculating Time between time stamps

Mon, 06 Jan 2014 22:42:25 GMT

Here is some of the sample data. You can see I have a calendar and a statistical date and time fields.

My goal is to properly concatonate the Date and Time fields so I can calculate the time difference, in minutes, between various other time based events.

I have tried both the DateODBC and Date fields - the results are the same -- either nothing, or a DTS that has a space and does not properly calculate for a time difference.

Thank you kindly

Re: Re: Re: Calculating Time between time stamps

Tue, 07 Jan 2014 02:42:00 GMT

Hi Rebecca, Firstly, we convert the into timestamps and then I am calculating the time diff in minutes. Please find the attached qvw file:

LOAD * ,

Fabs(Num#(Text(Interval(Timestamp(ResultDate + Hour(Time#(ResultTime,'hhmm'))/24 + Minute(Time#(ResultTime,'hhmm'))/1440)-Timestamp(ReceiveDate + Hour(Time#(ReceiveTime,'hhmm'))/24 + Minute(Time#(ReceiveTime,'hhmm'))/1440),'mm')))) AS TIME_MIN,

fabs(Num#(Text(Interval(Timestamp(num(ResultDateODBC) + Hour(ResultTimeODBC)/24 + Minute(ResultTimeODBC)/1440)-Timestamp(num(ReceiveDateODBC) + Hour(ReceiveTimeODBC)/24 + Minute(ReceiveTimeODBC)/1440),'mm')))) AS TIME_MIN_ODBC

;

LOAD BatTstCode,

ReceiveDate,

ReceiveTime,

ReceiveDateODBC,

ReceiveTimeODBC,

ResultDate,

ResultTime,

ResultDateODBC,

ResultTimeODBC

FROM TimeSampleData.xls (biff, embedded labels, table is [Sheet1$]);

Note: Fabs function converts -ve values into +ve values.

Re: Re: Re: Calculating Time between time stamps

Tue, 07 Jan 2014 02:44:02 GMT

Hi Rebcca,

Somehow QlikView converts Date and Time into numbers and format was not needed.

Anyway, I have converted your excel file to a csv file and implemented the formatting.

Some results are strange. Your ResultDate or ResultTime is earlier than ReceiveDate or Time.

Re: Re: Re: Calculating Time between time stamps

Tue, 07 Jan 2014 15:13:26 GMT

Yes, I have tried this, unfortunately, some of the times cross calendar days and the calculation does not show the true time difference. Thank you for this help!