topic Re: Time difference in QlikView

Time difference

ashis — Wed, 25 Nov 2020 16:16:04 GMT

In our data base we have date field as below , Year-month-day hours:min:sec:nano sec

2018-03-23 22:32:24.043

When I am pulling the date field in the load script from the DB , it is reading as "DD/MM/YYYY hh:mm:ss:TT"

1) My first question is why it is discarding the nano second

2) I want to calculate end date and start date in terms of second so I am using the following formula:

=Interval((EndTime) - (StartTime),'ss')

But for some cases it is not showing right value.

for example difference between end and start time is showing 00 , however it should be 1

Kindly give your input.

Re: Time difference

tresB — Wed, 09 May 2018 06:27:09 GMT

Could you share your sample qvw?

Re: Time difference

Anonymous — Wed, 09 May 2018 06:58:29 GMT

Hi,

SET TimestampFormat='m/dd/yyyy h:mm:ss TT';

LOAD * ,

interval(endTime-startTime) as Diff

INLINE [

startTime, endTime

4/24/2018 5:02:53 AM, 4/24/2018 5:02:54 AM

];

Re: Time difference

jonathandienst — Wed, 09 May 2018 07:06:24 GMT

>>DD/MM/YYYY hh:mm:ss:TT

This is incorrect. TT is the AM/PM indicator so it has converted the time to a 12 hour clock and you are rounding the display to the nearest second. You need format string 'DD/MM/YYYY hh:mm:ss.fff'.

>> difference between end and start time is showing 00 , however it should be 1

Not necessarily. Your format has hidden but not removed the second fractions, so the difference might be less than 0.5 seconds, which will round down to 0.

If the field EndTime and StartTime are loaded as strings, then you need to convert with

TimeStamp#(EndTime, 'yyyy-MM-dd hh:mm:ss.fff')

Re: Time difference

ashis — Wed, 09 May 2018 07:34:29 GMT

Hi Jonathan,

It seems my starttime and endtime is loading as text , do I need to change it in the load script:

TimeStamp#(Start_Time, 'YYYY-MM-DD hh:mm:ss.fff') as Start_Time,

Doing this in the load script I see my chart is disappearing .

Your input is very helpful though.

Re: Time difference

ashis — Wed, 09 May 2018 09:13:14 GMT

Hi Aruna,

Thank you for your quick response . It did not work. I believe in our db we have Nano seconds so while calculating the difference I need to consider second and Nano second.

I wanted to skip the Nano second part though.