topic Re: How to calculate difference between to dates in Seconds in QlikView

How to calculate difference between to dates in Seconds

Tue, 30 Jun 2015 08:48:26 GMT

Hi All,

i need to calculate the difference between two dates in Seconds. start date: 6/8/2015 23:32:35 and End Date 6/8/2015 23:32:50.

Please suggest me the formula.

Re: How to calculate difference between to dates in Seconds

swuehl — Tue, 30 Jun 2015 09:06:15 GMT

In the script, your timestamp format should be set accordingly:

SET TimestampFormat = 'M/D/YYYY hh:mm:ss';

Then you can load your values just use interval():

=interval( [End Date]-[Start Date],'ss')

or if you want to do further calculcation in units seconds:

=num#(interval( [End Date]-[Start Date],'ss'))

Re: How to calculate difference between to dates in Seconds

Tue, 30 Jun 2015 09:30:11 GMT

Hi Swuehl,

In my document the time format is set like this TimestampFormat='M/D/YYYY hh:mm:ss[.fff] TT'; and in the script i have given Interval(END_DATE-START_DATE,'ss'). But I am getting the result as 00 only.

Re: How to calculate difference between to dates in Seconds

buzzy996 — Tue, 30 Jun 2015 09:34:25 GMT

try tis way,

=Interval(Interval(Sum(ProProductiveTime),'hh:mm:ss') - Interval(Sum(ProNonProductiveTime),'hh:mm:ss'),'hh:mm:ss')

Re: How to calculate difference between to dates in Seconds

swuehl — Tue, 30 Jun 2015 09:36:53 GMT

You need to interpret the field values correctly before you can perform numeric calculations. If you don't want to change your global script settings, try

interval( timestamp#([End Date],'M/D/YYYY hh:mm:ss')-timestamp#([Start Date],'M/D/YYYY hh:mm:ss'),'ss')

Re: How to calculate difference between to dates in Seconds

senpradip007 — Tue, 30 Jun 2015 09:46:22 GMT

Try this in script.

LOAD *,

Interval(timestamp#(End_Date, 'mm/dd/yyyy hh:mm:ss') - timestamp#(start_date, 'mm/dd/yyyy hh:mm:ss'), 'ss') as IntervalT

Inline [

start_date , End_Date

6/8/2015 23:32:35, 6/8/2015 23:32:50

];

Re: How to calculate difference between to dates in Seconds

Tue, 30 Jun 2015 12:32:10 GMT

Hi All,

I couldnt solve the issue using any of the above Suggestions. I am getting '_' as a result of this. Please help me to solve this

Re: How to calculate difference between to dates in Seconds

buzzy996 — Tue, 30 Jun 2015 12:35:39 GMT

can u share ur app and expected output?

Re: How to calculate difference between to dates in Seconds

Tue, 30 Jun 2015 12:45:16 GMT

Hi All,

the Issue is fixed now. Due to some reasons the time format was wrong and that was causing the issue. And now i am able to find the difference using the Interval function alone. Thank you all for the valuable time.

Re: How to calculate difference between to dates in Seconds

sasiparupudi1 — Tue, 30 Jun 2015 12:45:27 GMT

load StartDate,EndDate,

Interval(Timestamp#(EndDate,'D/M/YYYY hh:mm:ss')-Timestamp#(StartDate,'D/M/YYYY hh:mm:ss'),'ss')

Inline

[

StartDate,EndDate

6/8/2015 23:32:35,6/8/2015 23:32:50

];