calculation of time difference between 2 rows for the same date

[jayati_shrivast]

Hi All,

I have a date field in the format as 'YYYY-MM-DD hh:mm:ss'. I need to calculate the time difference for all the rows of the date field and store in separate column as status duration.

for this I applied the following formula in the expression editor in front end:

aggr(Date - Above(Date),Date).

But I am getting the time difference even when the date is different i.e from the table we can see, time difference is 196:17:27 when date is 18th oct and 26th oct, which should remain blank.

1) I need to get the time difference where the dates are same.

2) Also I want the time difference values to be displayed from the very first record but currently it is displaying from 2nd row and 1st row is blank

Kindly help as to what changes I need to implement in the formula to get the desired rslt.

Thanks

[sunny_talwar]

Try to do create a new field in the script like this

LOAD Date as TimeStamp,
  Date(Floor(Date)) as Date,
  ...
FROM ...;

And then try this

Aggr(TimeStamp - Above(TimeStamp), Date, TimeStamp)

[mrybalko]

Hello

Could you please provide sample data file and desired output example?

[jayati_shrivast]

here Timestamp is used as function or as field name?

[jayati_shrivast]

i converted the date into timestamp as shown above, but still I am getting the same rslt

[sunny_talwar]

Would you be able to share a sample to show what exactly you have?