Duration/interval time between date/time values in same field

[Anonymous]

Hi All,

Please assist me to crack this one: a logic to calculate the duration/interval time between first and the previous time in one field. The below is how my data looks like for each session.

LOAD *  INLINE [

    ID,Date and Time

    1,03/05/2017 16:07:02

    1,03/05/2017 16:05:02

    1,03/05/2017 16:12:09

    2,04/05/2017 17:00:33

    2,04/05/2017 17:05:45

    3,04/05/2017 17:09:00

];

[sunny_talwar · Accepted solution]

May be try this

SET TimestampFormat='MM/DD/YYYY h:mm:ss';

Table:

LOAD *  INLINE [

    ID,Date and Time

    1,03/05/2017 16:07:02

    1,03/05/2017 16:05:02

    1,03/05/2017 16:12:09

    2,04/05/2017 17:00:33

    2,04/05/2017 17:05:45

    3,04/05/2017 17:09:00

];

FinalTable:

LOAD *,

  Interval(If(ID = Previous(ID), [Date and Time] - Previous([Date and Time]), 0), 'D h:mm:ss') as Difference

Resident Table

Order By ID, [Date and Time];

DROP Table Table;

[Anonymous]

Thank you Sunny

That work perfectly!! I re did with the peek() and I still got the same results . See the below:

SET TimestampFormat='DD/MM/YYYY h:mm:ss';

table1:

LOAD *  INLINE [

    ID,DT

    1,03/05/2017 16:07:02

    1,03/05/2017 16:05:02

    1,03/05/2017 16:12:09

    2,04/05/2017 17:00:33

    2,04/05/2017 17:05:45

    3,04/05/2017 17:09:00

];

LOAD *,

Interval(DT - if(Peek(ID)=ID,Peek(DT),DT)) as Interval

Resident table1

Order By ID,DT;

DROP Table table1;