Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate difference between to dates in Seconds

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.


1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

9 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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.

buzzy996
Master II
Master II

try tis way,

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

swuehl
MVP
MVP

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

senpradip007
Specialist III
Specialist III

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

];

Not applicable
Author

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 

buzzy996
Master II
Master II

can u share ur app and expected output?

Not applicable
Author

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.

sasiparupudi1
Master III
Master III

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

];