Calculating the duration between dates in the same field

[evie]

I need to calculate the duration between two dates in the same field by each user. I basically just need to know how much downtime each user has in between each action (Time) and return a null if it is the first time stamp for that user.

Say this is my data:

User	Time
Damien	6/1/2023 12:01:06
Adam	6/1/2023 12:01:47
Damien	6/1/2023 12:02:36
Sorel	6/1/2023 12:03:05
Sorel	6/1/2023 12:07:06
Adrian	6/1/2023 12:07:20
Damien	6/1/2023 12:07:59
Damien	6/1/2023 12:08:06
Adrian	6/1/2023 12:10:04
Adrian	6/1/2023 12:11:07
Damien	6/1/2023 12:12:01
Addison	6/1/2023 12:13:08
Sorel	6/1/2023 12:14:37
Adrian	6/1/2023 12:15:16
Damien	6/1/2023 12:16:01
Damien	6/1/2023 12:16:46
Adrian	6/1/2023 12:17:31
Adrian	6/1/2023 12:18:16
Damien	6/1/2023 12:19:01
Addison	6/1/2023 12:19:46

This is what I need the new field to look like:

User	Time	Duration
Damien	6/1/2023 12:01:06	-
Adam	6/1/2023 12:01:47	-
Damien	6/1/2023 12:02:36	0h 1m 30s
Sorel	6/1/2023 12:03:05	-
Sorel	6/1/2023 12:07:06	0h 4m 1s
Adrian	6/1/2023 12:07:20	-
Damien	6/1/2023 12:07:59	0h 5m 23s
Damien	6/1/2023 12:08:06	0h 0m 7s
Adrian	6/1/2023 12:10:04	0h 2m 44s
Adrian	6/1/2023 12:11:07	0h 1m 3s
Damien	6/1/2023 12:12:01	0h 3m 55s
Addison	6/1/2023 12:13:08	-
Sorel	6/1/2023 12:14:37	0h 7m 31s
Adrian	6/1/2023 12:15:16	0h 4m 9s
Damien	6/1/2023 12:16:01	0h 12m 0s
Damien	6/1/2023 12:16:46	0h 0m 45s
Adrian	6/1/2023 12:17:31	0h 2m 15s
Adrian	6/1/2023 12:18:16	0h 0m 45s
Damien	6/1/2023 12:19:01	0h 2m 15s
Addison	6/1/2023 12:19:46	0h 6m 38s

This is what is currently in my script and what it is returning:

LOAD

UserID,

If(User = Previous(User), Time(Interval(Timestamp#(Time, 'dd/mm/yyyy hh:mm:ss') - (Timestamp#(Previous(Time), 'dd/mm/yyyy hh:mm:ss')), 'hh mm ss'), null()) AS Duration

FROM DataSource

ORDER BY UserID;

User	Time	Duration
Damien	6/1/2023 12:01:06	-
Adam	6/1/2023 12:01:47	-
Damien	6/1/2023 12:02:36	-
Sorel	6/1/2023 12:03:05	-
Sorel	6/1/2023 12:07:06	-
Adrian	6/1/2023 12:07:20	-
Damien	6/1/2023 12:07:59	-
Damien	6/1/2023 12:08:06	-
Adrian	6/1/2023 12:10:04	-
Adrian	6/1/2023 12:11:07	-
Damien	6/1/2023 12:12:01	-
Addison	6/1/2023 12:13:08	-
Sorel	6/1/2023 12:14:37	-
Adrian	6/1/2023 12:15:16	-
Damien	6/1/2023 12:16:01	-
Damien	6/1/2023 12:16:46	-
Adrian	6/1/2023 12:17:31	-
Adrian	6/1/2023 12:18:16	-
Damien	6/1/2023 12:19:01	-
Addison	6/1/2023 12:19:46	-

I have also tried something more simple like:

LOAD

UserID,

If(User = Previous(User),

Interval(Time - Previous(Time), 'hh mm ss'), null()) AS Duration

FROM DataSource

and it has not worked.

I know 'hh mm ss' will not return the correct format of 'Xh Xm Xs', but I am also unsure of how to do that.

Can you help with this?

[joaopaulo_delco]

Hi @evie !

                  I think the script below will return exactly what you need:

[DataSource]:
Load * Inline [
User,	Time
Damien,	6/1/2023 12:01:06
Adam,	6/1/2023 12:01:47
Damien,	6/1/2023 12:02:36
Sorel,	6/1/2023 12:03:05
Sorel,	6/1/2023 12:07:06
Adrian,	6/1/2023 12:07:20
Damien,	6/1/2023 12:07:59
Damien,	6/1/2023 12:08:06
Adrian,	6/1/2023 12:10:04
Adrian,	6/1/2023 12:11:07
Damien,	6/1/2023 12:12:01
Addison, 6/1/2023 12:13:08
Sorel,	6/1/2023 12:14:37
Adrian,	6/1/2023 12:15:16
Damien,	6/1/2023 12:16:01
Damien,	6/1/2023 12:16:46
Adrian,	6/1/2023 12:17:31
Adrian,	6/1/2023 12:18:16
Damien,	6/1/2023 12:19:01
Addison, 6/1/2023 12:19:46
];

[TAB]:
LOAD
User,
Time,
If(User = Previous(User), Time(Interval(Timestamp#(Time, 'dd/mm/yyyy hh:mm:ss') - (Timestamp#(Previous(Time), 'dd/mm/yyyy hh:mm:ss')), 'hh mm ss'), null())) AS Duration
Resident DataSource
ORDER BY User, Time;

Drop Table DataSource;