topic Re: Calculate the difference in seconds between two time stamps. in QlikView

Calculate the difference in seconds between two time stamps.

ryan_evans1984 — Tue, 07 Aug 2018 10:49:23 GMT

I have used a resident load group by with in my code and I cannot calculate the correct duration in seconds between the start timestamp and end time stamp

Current_Data:

Load

datStart,

datEnd,

""Looking for a line to calculate the difference between datStart & datEnd ******

MachineName as L_Machine_Name

RESIDENT Data

Group by MachineName;

Re: Calculate the difference in seconds between two time stamps.

Anil_Babu_Samineni — Wed, 08 Aug 2018 00:25:00 GMT

There is no aggregate here, Not sure why you required Group By? Perhaps this.

Current_Data:

Load

datStart,

datEnd,

Interval(datStart-dayEnd,'hh:mm:ss') as NewField,

MachineName as L_Machine_Name

RESIDENT Data

Re: Calculate the difference in seconds between two time stamps.

vishsaggi — Wed, 08 Aug 2018 02:19:18 GMT

Can you tell us the date format your fields has.

Re: Calculate the difference in seconds between two time stamps.

rwunderlich — Wed, 08 Aug 2018 04:25:55 GMT

Assuming datEnd and datStart are timestamps, the differences in seconds would be:

num(interval(datEnd-datStart, 's'))

You may perhaps not want the actual seconds, but rather keep the standard duration (1 = 1 day) and instead just display the value as seconds.

interval(datEnd-datStart, 's')

-Rob

Re: Calculate the difference in seconds between two time stamps.

ryan_evans1984 — Wed, 08 Aug 2018 08:23:20 GMT

Thanks for the comments.

I created a group by because I wanted a small table by machine... the main table 'data' has 800k rows.

I just wanted to show the last datstart and datend by machine in a small table.

Re: Calculate the difference in seconds between two time stamps.

ryan_evans1984 — Wed, 08 Aug 2018 08:23:36 GMT

Thanks for the comments.

I created a group by because I wanted a small table by machine... the main table 'data' has 800k rows.

I just wanted to show the last datstart and datend by machine in a small table.

Re: Calculate the difference in seconds between two time stamps.

ryan_evans1984 — Wed, 08 Aug 2018 08:25:23 GMT

Thanks for the comments.

The num(interval(datEnd-datStart, 's')) works and displays a number but I wanted seconds.

And the interval(datEnd-datStart, 's') doesnt work.

tried multiple things and still cannot get this into seconds.

Re: Calculate the difference in seconds between two time stamps.

rwunderlich — Tue, 14 Aug 2018 05:32:57 GMT

I typed too fast.

interval(datEnd-datStart, 's')

should display the value in seconds. What does it display for you?

-Rob

Re: Calculate the difference in seconds between two time stamps.

BalaBhaskar_Qlik — Tue, 14 Aug 2018 10:40:50 GMT

May be:

=sum(datEnd-datStart) * 24 * 60 * 60