Time diference between two observations (duration of atendee)

[Felipe Damasceno]

I'm using Stata 14 with Windows 10 OS

I need to calculate the duration that participants stay in a lecture. I have the following data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str36 id str10 date str7 time str12 action
"036d9c59-52cf-4c4a-a2e0-29609931129c" " 5/12/2020" "7:00:42" " Joined"
"036d9c59-52cf-4c4a-a2e0-29609931129c" " 5/12/2020" "7:01:37" " Left"  
"03b1ffbc-9100-4cbe-b5e1-420151c2227b" " 5/12/2020" "8:07:58" " Joined"
"03b1ffbc-9100-4cbe-b5e1-420151c2227b" " 5/12/2020" "9:28:38" " Left"  
"03b2c990-7ac6-4553-8a37-8bdc351d1d9a" " 5/12/2020" "8:48:09" " Joined"
"03b2c990-7ac6-4553-8a37-8bdc351d1d9a" " 5/12/2020" "8:48:21" " Left"  
"04304bac-3789-45a6-8b3c-b843177324be" " 5/12/2020" "8:48:27" " Joined"
"04304bac-3789-45a6-8b3c-b843177324be" " 5/12/2020" "9:34:07" " Left"  
"05291aa4-5719-4413-8558-5c385f4b5106" " 5/12/2020" "8:01:47" " Joined"
"05291aa4-5719-4413-8558-5c385f4b5106" " 5/12/2020" "8:02:35" " Left"  
"056f8ad8-a5c9-4af8-aeae-432486d23d0b" " 5/12/2020" "8:00:40" " Joined"
"056f8ad8-a5c9-4af8-aeae-432486d23d0b" " 5/12/2020" "8:12:53" " Left"  
end

[William Lisowski]

Perhaps this sample code will start you in a useful direction.

replace action = trim(action)
generate double dt = clock(date+" "+time,"MDYhms")
format dt %tc
drop date time
reshape wide dt, i(id) j(action) string
generate duration = dtLeft - dtJoined
format duration %tchH:MM:SS
generate minutes = duration/(1000*60)
format minutes %9.2f
list, clean noobs

. list, clean noobs

                                      id             dtJoined               dtLeft   duration   minutes  
    036d9c59-52cf-4c4a-a2e0-29609931129c   12may2020 07:00:42   12may2020 07:01:37    0:00:55      0.92  
    03b1ffbc-9100-4cbe-b5e1-420151c2227b   12may2020 08:07:58   12may2020 09:28:38    1:20:40     80.67  
    03b2c990-7ac6-4553-8a37-8bdc351d1d9a   12may2020 08:48:09   12may2020 08:48:21    0:00:12      0.20  
    04304bac-3789-45a6-8b3c-b843177324be   12may2020 08:48:27   12may2020 09:34:07    0:45:40     45.67  
    05291aa4-5719-4413-8558-5c385f4b5106   12may2020 08:01:47   12may2020 08:02:35    0:00:48      0.80  
    056f8ad8-a5c9-4af8-aeae-432486d23d0b   12may2020 08:00:40   12may2020 08:12:53    0:12:13     12.22

[Nick Cox]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str36 id str10 date str7 time str12 action
"036d9c59-52cf-4c4a-a2e0-29609931129c" " 5/12/2020" "7:00:42" " Joined"
"036d9c59-52cf-4c4a-a2e0-29609931129c" " 5/12/2020" "7:01:37" " Left"  
"03b1ffbc-9100-4cbe-b5e1-420151c2227b" " 5/12/2020" "8:07:58" " Joined"
"03b1ffbc-9100-4cbe-b5e1-420151c2227b" " 5/12/2020" "9:28:38" " Left"  
"03b2c990-7ac6-4553-8a37-8bdc351d1d9a" " 5/12/2020" "8:48:09" " Joined"
"03b2c990-7ac6-4553-8a37-8bdc351d1d9a" " 5/12/2020" "8:48:21" " Left"  
"04304bac-3789-45a6-8b3c-b843177324be" " 5/12/2020" "8:48:27" " Joined"
"04304bac-3789-45a6-8b3c-b843177324be" " 5/12/2020" "9:34:07" " Left"  
"05291aa4-5719-4413-8558-5c385f4b5106" " 5/12/2020" "8:01:47" " Joined"
"05291aa4-5719-4413-8558-5c385f4b5106" " 5/12/2020" "8:02:35" " Left"  
"056f8ad8-a5c9-4af8-aeae-432486d23d0b" " 5/12/2020" "8:00:40" " Joined"
"056f8ad8-a5c9-4af8-aeae-432486d23d0b" " 5/12/2020" "8:12:53" " Left"  
end

bysort id (action) : gen wanted = (clock(date[2] + time[2], "DMY hms") - clock(date[1] + time[1], "DMY hms")) / 1000 

list date time wanted , sepby(id)

     +-------------------------------+
     |       date      time   wanted |
     |-------------------------------|
  1. |  5/12/2020   7:00:42       55 |
  2. |  5/12/2020   7:01:37       55 |
     |-------------------------------|
  3. |  5/12/2020   8:07:58     4840 |
  4. |  5/12/2020   9:28:38     4840 |
     |-------------------------------|
  5. |  5/12/2020   8:48:09       12 |
  6. |  5/12/2020   8:48:21       12 |
     |-------------------------------|
  7. |  5/12/2020   8:48:27     2740 |
  8. |  5/12/2020   9:34:07     2740 |
     |-------------------------------|
  9. |  5/12/2020   8:01:47       48 |
 10. |  5/12/2020   8:02:35       48 |
     |-------------------------------|
 11. |  5/12/2020   8:00:40      733 |
 12. |  5/12/2020   8:12:53      733 |
     +-------------------------------+

[Felipe Damasceno]

Thanks Nick. Worked fine. I just have one problem. Some attendees got in and out several times. Thus, I have duplicates of id and action. I think the best setting would be to get the first join and the last left. Can you help me with this?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str36 id str10 date str7 time str12 action
"402442ce-ad31-4431-ad05-d26a8c1f408e" " 5/12/2020" "7:45:01" " Joined"     
"402442ce-ad31-4431-ad05-d26a8c1f408e" " 5/12/2020" "8:21:15" " Reconnected"
"402442ce-ad31-4431-ad05-d26a8c1f408e" " 5/12/2020" "8:21:37" " Left"       
"402442ce-ad31-4431-ad05-d26a8c1f408e" " 5/12/2020" "9:16:28" " Left"       
"402442ce-ad31-4431-ad05-d26a8c1f408e" " 5/12/2020" "9:16:33" " Joined"     
"402442ce-ad31-4431-ad05-d26a8c1f408e" " 5/12/2020" "9:17:04" " Joined"     
"402442ce-ad31-4431-ad05-d26a8c1f408e" " 5/12/2020" "9:21:19" " Reconnected"
"402442ce-ad31-4431-ad05-d26a8c1f408e" " 5/12/2020" "9:21:49" " Left"       
"402442ce-ad31-4431-ad05-d26a8c1f408e" " 5/12/2020" "9:28:37" " Left"       
end

[Nick Cox]

More general code would be just to look at the first and last observations, except that if the first date and time is not joined OR the last is not left, I don't know what you expect.

bysort id (date time) : gen wanted = (clock(date[_N] + time[_N], "DMY hms") - clock(date[1] + time[1], "DMY hms")) / 1000
by id : replace wanted = . if action[_N] == " Joined" | action[1] != " Joined"

list date time wanted , sepby(id)

Warning you don't have any pm times here, but if you do, string times like "10:00:00" will sort before "9:59:59". So if you do, you would need to do something more like this.

gen double datetime = clock(date + time, "DMY hms" )
bysort id (datetime) : gen wanted = (datetime[_N] - datetime[1]) / 1000
by id : replace wanted = . if action[_N] != " Left" | action[1] != " Joined"

[Nick Cox]

Duplicate post. Need coffee....

[Felipe Damasceno]

Thanks Nick. It seems working but what am I actually getting? Seconds?

[Nick Cox]

Indeed. Look at the code: datetimes are in milliseconds and we are dividing by 1000. Look at the example in #3. Mental arithmetic on the first identifier gives 55 seconds.

[Felipe Damasceno]

Thanks very much Nick