5 Replies Latest reply: Jun 30, 2015 8:16 AM by Sunny Talwar RSS

    Time Interval

      Hi

      I Have 2 Tables

       

      Received:

       

      TRN_ReceivedReceived_DateTime
      20150309-0002981401/04/2015 16:19:02
      20150309-0002981410/03/2015 02:28:16
      20150312-0000442501/04/2015 14:20:03
      20150312-0000442512/03/2015 03:47:29
      20150316-0000139408/04/2015 02:51:44
      20150316-0000139417/03/2015 02:21:09

       

      Released:

       

       

      TRN_ReleasedReleased_DateTime
      20150309-0002981401/04/2015 15:07:25
      20150309-0002981401/04/2015 15:28:30
      20150312-0000442501/04/2015 10:52:52
      20150312-0000442501/04/2015 13:50:40
      20150316-0000139407/04/2015 16:33:07
      20150316-0000139408/04/2015 7:21:39

       

       

      How can i find the difference between Released_DateTime and Received_DateTime

       

       

      Thanks

        • Re: Time Interval
          Shiva Reddy

          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

          • Re: Time Interval
            Sunny Talwar

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

              • Re: Time Interval

                Hi

                 

                  

                KeyReceived_DateTimeReleased_DateTime
                20150309-00029814_110/03/2015 02:28:1601/04/2015 15:07:25
                20150309-00029814_201/04/2015 16:19:0201/04/2015 17:28:30
                20150312-00004425_112/03/2015 03:47:2901/04/2015 10:52:52
                20150312-00004425_201/04/2015 14:20:0301/04/2015 14:50:40
                20150316-00001394_117/03/2015 02:21:0907/04/2015 16:33:07
                20150316-00001394_208/04/2015 02:51:4408/04/2015 7:21:39

                 

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

                Key is the Concatenated Column .

                  • Re: Time Interval
                    Sunny Talwar

                    Is this the output you are expecting?

                     

                    Capture.PNG

                     

                    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

                • Re: Time Interval
                  Avinash R

                  Hi Senthil,

                   

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