2 Replies Latest reply: Oct 31, 2017 12:36 PM by ioannis giakoumakis RSS

    Calculating Time Difference between multiple timestamps

    Harjit Nar

      I have a table similar to this.

       

      IDDateTime

      1

      1/10/1709:00
      11/10/1710:00
      11/10/1711:00
      11/10/1712:00
      21/10/1705:00
      21/10/17

      05:15

      21/10/1705:30
      21/10/1705:45
      21/10/1706:00


      I want to calculate the average time difference per per ID, Date e.g. 60mins or 15 mins.


      Can anyone help me do this within the script?

        • Re: Calculating Time Difference between multiple timestamps
          Nicole Smith

          This should do the trick:

           

          Data:
          LOAD * INLINE [
              ID, Date, Time
              1, 1/10/17, 09:00
              1, 1/10/17, 10:00
              1, 1/10/17, 11:00
              1, 1/10/17, 12:00
              2, 1/10/17, 05:00
              2, 1/10/17, 05:15
              2, 1/10/17, 05:30
              2, 1/10/17, 05:45
              2, 1/10/17, 06:00
          ];
          
          Difference:
          LOAD ID, 
          Date, 
          Time, 
          IF(ID = Previous(ID) AND Date = Previous(Date), Interval(Time - Previous(Time), 'mm')) as Diff
          RESIDENT Data
          ORDER BY ID, Date, Time;
          
          Average:
          LOAD ID, 
          Date, 
          Interval(Avg(Diff), 'mm') As Average
          RESIDENT Difference
          GROUP BY ID, Date;
          
          DROP TABLES Data, Difference;
          
          • Re: Calculating Time Difference between multiple timestamps
            ioannis giakoumakis

            combine firstsortedvalue with -Date for max and Date for min and then just subtract the 2

             

            something like:

             

            load

            Timestamp(firstsortedvalue, -Date) - Timestamp(firstsortedvalue, Date) as diff

            Resident table

            Group by

            ID, Date