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: 
userid128223
Creator
Creator

round date with time difference

I have 2 dates in table:

StartDate: 2017-01-20 00:01:25 345

EndDate:  2017-01-20 00:01:26 235




How can i get the difference between EndDate - StartDate & round it off to 2 decimals.



Thanks

1 Solution

Accepted Solutions
kamal_sanguri
Specialist
Specialist

Hi,

Please find attached document where I have put same condition in script with Where clause to show records where difference of time is more than 30 sec (you can change as per your needs.

Data:

LOAD Time#(EndDate,'YYYY-MM-DD hh:mm:ss fff') as EndDate,

  Time#(StartDate,'YYYY-MM-DD hh:mm:ss fff') as StartDate;

Load * Inline

[

StartDate,EndDate

2017-01-20 00:01:25 235,2017-01-20 00:02:56 235

2017-01-20 00:01:25 235,2017-01-20 00:01:26 235

2017-01-20 00:01:25 235,2017-01-20 00:11:26 235

2017-01-20 01:21:25 235,2017-01-20 01:31:26 235

2017-01-20 01:21:25 235,2017-01-20 01:31:29 235

];

NoConcatenate

NewData:

LOAD *

Resident Data

Where Time(EndDate-StartDate,'hh:mm:ss fff')>Time#('00:00:30:','hh:mm:ss');

DROP Table Data;



View solution in original post

7 Replies
antoniotiman
Master III
Master III

Hi,

SET TimestampFormat='YYYY-MM-DD hh:mm:ss fff';

= Interval(EndDate-StartDate,'ss,ff')  -> 00,89

Regards,

Antonio

userid128223
Creator
Creator
Author

I am getting wierd results when i try below.

interval(EndDate - StartDate ,'ssss') as Date_difference;

it is not calculating seconds difference in some fields properly, I also get E-5 on many fields.

6-1-2017 4-20-42 PM.jpg

kamal_sanguri
Specialist
Specialist

Hi,

Please refer this document. Let me know in case you have any doubt.

userid128223
Creator
Creator
Author

thanks for the script. how do i use to evaluate seconds that exceeds 30 or only show records when it exceeds 5 minutes based on  your answer.

kamal_sanguri
Specialist
Specialist

try this:

=if (Time(EndDate-StartDate,'hh:mm:ss fff')>Time#('00:00:30:','hh:mm:ss'),Time(EndDate-StartDate,'hh:mm:ss fff'),'Less than 30 seconds not recorded.')

I have set it show when difference is more than 30 seconds, you can change it per your needs like for five minutes:

=if (Time(EndDate-StartDate,'hh:mm:ss fff')>Time#('00:05:00:','hh:mm:ss'),Time(EndDate-StartDate,'hh:mm:ss fff'),'Less than 30 seconds not recorded.')

userid128223
Creator
Creator
Author

i am looking to do this in script with where clause.

kamal_sanguri
Specialist
Specialist

Hi,

Please find attached document where I have put same condition in script with Where clause to show records where difference of time is more than 30 sec (you can change as per your needs.

Data:

LOAD Time#(EndDate,'YYYY-MM-DD hh:mm:ss fff') as EndDate,

  Time#(StartDate,'YYYY-MM-DD hh:mm:ss fff') as StartDate;

Load * Inline

[

StartDate,EndDate

2017-01-20 00:01:25 235,2017-01-20 00:02:56 235

2017-01-20 00:01:25 235,2017-01-20 00:01:26 235

2017-01-20 00:01:25 235,2017-01-20 00:11:26 235

2017-01-20 01:21:25 235,2017-01-20 01:31:26 235

2017-01-20 01:21:25 235,2017-01-20 01:31:29 235

];

NoConcatenate

NewData:

LOAD *

Resident Data

Where Time(EndDate-StartDate,'hh:mm:ss fff')>Time#('00:00:30:','hh:mm:ss');

DROP Table Data;