Time Interval

[#1]

Hi

I Have 2 Tables

Received:

TRN_Received	Received_DateTime
20150309-00029814	01/04/2015 16:19:02
20150309-00029814	10/03/2015 02:28:16
20150312-00004425	01/04/2015 14:20:03
20150312-00004425	12/03/2015 03:47:29
20150316-00001394	08/04/2015 02:51:44
20150316-00001394	17/03/2015 02:21:09

Released:

TRN_Released	Released_DateTime
20150309-00029814	01/04/2015 15:07:25
20150309-00029814	01/04/2015 15:28:30
20150312-00004425	01/04/2015 10:52:52
20150312-00004425	01/04/2015 13:50:40
20150316-00001394	07/04/2015 16:33:07
20150316-00001394	08/04/2015 7:21:39

How can i find the difference between Released_DateTime and Received_DateTime

Thanks

[buzzy996]

try tis way,

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

use ur fields on the above expression

[sunny_talwar]

There are multiple TRN_Released in both the tables. Which ones do you need to calculate the difference?

[avinashelite]

Hi Senthil,

In order to find the interval , the compare  based  on the unique key other wise this yield in wrong result

[#5]

Hi

Key	Received_DateTime	Released_DateTime
20150309-00029814_1	10/03/2015 02:28:16	01/04/2015 15:07:25
20150309-00029814_2	01/04/2015 16:19:02	01/04/2015 17:28:30
20150312-00004425_1	12/03/2015 03:47:29	01/04/2015 10:52:52
20150312-00004425_2	01/04/2015 14:20:03	01/04/2015 14:50:40
20150316-00001394_1	17/03/2015 02:21:09	07/04/2015 16:33:07
20150316-00001394_2	08/04/2015 02:51:44	08/04/2015 7:21:39

In the above table , Received_DateTime is from table1 and the other from table2

Key is the Concatenated Column .

[sunny_talwar]

Is this the output you are expecting?

If yes then you can use the following script:

Table1:

LOAD *,

  Interval(Released_DateTime - Received_DateTime, 'D hh:mm:ss') as Difference;

LOAD Key,

  Timestamp#(Received_DateTime, 'DD/MM/YYYY hh:mm:ss') as Received_DateTime,

  Timestamp#(Released_DateTime, 'DD/MM/YYYY hh:mm:ss') as Released_DateTime;

LOAD * Inline [

Key, Received_DateTime, Released_DateTime

20150309-00029814_1, 10/03/2015 02:28:16, 01/04/2015 15:07:25

20150309-00029814_2, 01/04/2015 16:19:02, 01/04/2015 17:28:30

20150312-00004425_1, 12/03/2015 03:47:29, 01/04/2015 10:52:52

20150312-00004425_2, 01/04/2015 14:20:03, 01/04/2015 14:50:40

20150316-00001394_1, 17/03/2015 02:21:09, 07/04/2015 16:33:07

20150316-00001394_2, 08/04/2015 02:51:44, 08/04/2015 7:21:39

];

HTH

Best,

Sunny