Determing number of hours between two dates

[#1]

I have two fields: "start_time" and "end_time"

Format of data in both fields is: "2009-07-21 16:00:00.000". The start and end times for each record are always on the same date/day.

I added the following code to subtract the start time from the end time : "end_time - start_time as duration"

It works, but the it puts the result in date/time format instead of numeric. For example, if the duration for a record that occurs on 7/21/2009 is 3 hours, it shows it as "2009-07-21 3:00:00.000" instead of "3".

I'm a VERY basic developer. Any suggestions on how to simply accomplish this??

[GabrielAraya]

Hi,

Did you try with:

=

Interval(Dat1 - Dat2 ,'hh')

Gabriel

[johnw]

Internally, the timestamps are stored as the number of days since December 30, 1899. Your example timestamp is stored as 40015.666666667. So when you subtract, if you have a period of three hours, it is stored numerically as 3/24, or 0.125 (well, technically as the IEEE double-precision floating point binary representation of 0.125, but close enough).

I can think of two basic ways to turn this into a number of hours. One is what Gabriel suggested, go ahead and keep it as an interval, and display the interval in hours:

interval(end_time - start_time,'hh') as duration

The other way is to multiply the result by 24 and store it as a number:

num((end_time - start_time)*24) as duration

Which is better for your specific case depends on what you plan to do with it later. Some things will be more easily done with an interval, others with a number. But as long as you remember that the interval is 1/24 of the number of hours, it'll be easy to do anything you want on the fly regardless of which format you choose for the field.

[#4]

Thanks so much for you response!

When I added the code you suggested, I got an error when I tried to reload the script. The script code for the table in question is provided below. Do you see any reason why I should be getting an error? I chose your "Num" format b/c I need to do some calcs with the number.

The error message says: 'num' is not a recognized function name.

IP_Classres_link:

Qualify

Unqualify

SQL SELECT "cls_session" AS ysessionkey,

"crs_session",

"end_time",

num((end_time

- start_time)*24) as duration,

"link_type",

"start_time",

xcode,

xlocator,

ylinkkey,

yreskey

FROM

LMS."lms_data"."classres_link";

Unqualify

*; ysessionkey,yreskey,xlocator;*;

[GabrielAraya]

Hi, You need to write the Num statemente in a Load precedent, for example:

Load
*,
Num(.....) as Duration;
SQL Select ...........................

Gabriel

[#6]

Gabriel,

Thanks again for the reply. Please hang in there with me. I think we're getting close to a solution. I get an "unknown statement" error when I move the "Num" command to the top of the command. I'm pulling this data through an ODBC connection to a database. Therefore, there is no "Load" command as you mentioned in your post. U think the fact that I don't have a "Load" command somehow prevents the "Num" command from working (see code below)?

IP_Classres_link:

Qualify *;

Unqualify ysessionkey,yreskey,xlocator;

Num((end_time - start_time)*24) as duration;

SQL SELECT "cls_session" AS ysessionkey,

"crs_session",

"end_time",

"link_type",

"start_time",

xcode,

xlocator,

ylinkkey,

yreskey

FROM LMS."lms_data"."classres_link";

Unqualify *;

[GabrielAraya · Accepted solution]

Hi, Here the issue is the following, when we use QlikView functions like Num, Date, Trim, Index, etc, these functions are not in a standard SQL, to use its is necessary to write a LOAD statement ( you need to write directly in the script, so the precedent LOAD statement will work with the NUM function
Load
*,
Num ( ...............) as Duration;
Sql Select ....
From LMS.....;

Gabriel

[hector]

try this

IP_Classres_link:

Qualify *;

Unqualify ysessionkey,yreskey,xlocator;

Load

*,

Num((end_time - start_time)*24) as duration

;

SQL SELECT "cls_session" AS ysessionkey,

"crs_session",

"end_time",

"link_type",

"start_time",

xcode,

xlocator,

ylinkkey,

yreskey

FROM LMS."lms_data"."classres_link";

Unqualify *;

[#9]

That got it!! Thanks much, Gabriel and Hector.

[amit_shetty78]

Hi,

I have tried using Interval function as suggested above in my load script.

I have dates loading in the format 'MM/DD/YYYY hh:mm:ss[.fff]' and I am doing

Interval(End_Date, Start_Date,'hh')
Sample values getting loaded:
Interval('03/30/2009 14:29:10', '03/30/2009 14:29:26','hh') = 00
Interval('09/16/2009 19:00:14', '09/16/2009 19:00:27','hh') = 00

The output for both is 00. But when I add a list box for this field then I get 2 rows with 00 instead of just 1.

Any idea how to get around this?

Thanks,

A.