3 Replies Latest reply: Sep 15, 2015 6:09 PM by Marco Wedel RSS

    Duration between time stamps

    Yvonne Han

      Hi QV expert!

       

      I'd like to calculate the duration in hour between two time stamps shown below in the load script.  Can someone help me with that?

       

      Thanks

       

         

      Job IDTime StartedTime CompletedDuration(hr) (example)
      1August 16, 2015 at 9:54 AMAugust 19, 2015 at 9:21 PM60.2
      2August 20, 2015 at 5:55 PMAugust 21, 2015 at 3:44 AM10
      3September 8, 2015 at 4:49 PMSeptember 8, 2015 at 5:02 PM0.1
        • Re: Duration between time stamps
          Sunny Talwar

          Not sure if this is what you want, but I got different value for Job 1:

           

          Capture.PNG

           

          If the values look good. I used the following script:

           

          SET TimestampFormat='MMM DD YYYY h:mmTT';

           

          Table:

          LOAD *,

            Interval(Timestamp#([Time Completed]) - Timestamp#([Time Started]), 'hh:m')  as Difference;

          LOAD * Inline [

          Job ID, Time Started, Time Completed, Test

          1, Aug 16 2015 9:54AM, Aug 19 2015 9:21PM, 60.2

          2, Aug 20 2015 5:55PM, Aug 21 2015 3:44AM, 10

          3, Sep 8 2015 4:49PM, Sep 8 2015 5:02PM, 0.1

          ];

          • Re: Duration between time stamps
            Massimo Grossi

            see attachment

             

            expression is

             

            =

            Interval(

              (

              Date#(replace([Time Completed], 'at ', ''), 'MMMM DD, YYYY h:mm tt')

              -

              Date#(replace([Time Started], 'at ', ''), 'MMMM DD, YYYY h:mm tt')

              )

              ,'hh')

            1.png

            • Re: Duration between time stamps
              Marco Wedel

              Hi,

               

              another solution could be.

               

              QlikCommunity_Thread_180887_Pic1.JPG

               

              LOAD *,
                   Num(([Time Completed]-[Time Started])*24,'0.0') as [Duration(hr)];
              LOAD [Job ID], 
                   Timestamp(Timestamp#(SubField([Time Started],'at',1)&SubField([Time Started],'at',2),'MMMM DD, YYYY  h:mm TT')) as [Time Started], 
                   Timestamp(Timestamp#(SubField([Time Completed],'at',1)&SubField([Time Completed],'at',2),'MMMM DD, YYYY  h:mm TT')) as [Time Completed]
              FROM [https://community.qlik.com/thread/180887] (html, codepage is 1252, embedded labels, table is @1);
              

               

              hope this helps

               

              regards

               

              Marco