Calculating Time Difference between multiple timestamps

[Anonymous]

I have a table similar to this.

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

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?

[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;

[giakoum]

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