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

# Calculating Time Difference between multiple timestamps

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

This should do the trick:

```Data:
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:
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:
Date,
Interval(Avg(Diff), 'mm') As Average
RESIDENT Difference
GROUP BY ID, Date;

DROP TABLES Data, Difference;
```
• ###### Re: Calculating Time Difference between multiple timestamps

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

something like: