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!

[#2]

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

[#3]

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

[#4]

Hi Rebecca,

This should work if the format is correct.

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

or

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

[#5 · Accepted solution]

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);

[#6]

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!

[#7]

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

[#8]

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

[#9]

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.

[#10]

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.

[#11]

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!