Calculating duration data with times crossing midnight

[Patrick Albright]

Hi all,

I am trying to calculate hours and minutes for the duration of an operation. The "Date" data is not attached to the time strings so everything is occurring to stata from the baseline date of 01 Jan 1960. As you can see in the attached image, this works fine for data occurring from 12:01 am to 11:59 pm. However, the issue arises when data crosses from say 11:00 pm to 1:00 am (2 hours). Stata thinks this is 31 Dec 1959, and not 02 Jan 1960 (a positive value). I have read through the "Help dates" guide, but I cannot seem to figure out how to alleviate this issue. Here is my code (simplistic, obviously) as well as an attached image of the data.

Thanks in advance for your help.

generate double oprepstartds = clock(oper_prep_start, "hm")

generate double oparriveds = clock(oper_arrive, "hm")

generate double opstartds = clock(oper_start, "hm")

generate double opendds = clock(oper_end, "hm")

generate double oppostopds = clock(oper_postop, "hm")

gen op_duration = opendds - opstartds

br participant_id redcap_event_name oper_prep_start oper_arrive oper_start oper_end oper_postop op_duration op_duration_min op_duration_hr

gen op_duration_min = op_duration/(60*1000)
gen op_duration_hr = op_duration_min/60

format oprepstartds %tc
format oparriveds %tc
format opstartds %tc
format opendds %tc
format oppostopds %tc
format op_duration %tc

[Carole J. Wilson]

Please use the -dataex- command to provide a sample of your data. See the FAQ 12.2 (https://www.statalist.org/forums/help#stata)

[Patrick Albright]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str18 redcap_event_name str5(oper_prep_start oper_arrive oper_start oper_end oper_postop) float(op_duration op_duration_min op_duration_hr)
"enroll_arm_1" "23:15" "23:35" "23:54" "00:43" "01:22" -83460000 -1391  -23.18333
"enroll_arm_1" "22:20" "22:36" "23:12" "00:12" "00:22" -8.28e+07 -1380        -23
"enroll_arm_1" "22:38" "22:46" "23:23" "00:35" "00:42" -82080000 -1368      -22.8
"enroll_arm_1" "22:57" "23:02" "23:35" "00:53" "00:59" -81720000 -1362      -22.7
"enroll_arm_1" "22:10" "22:44" "23:21" "00:44" "00:56" -81420000 -1357 -22.616667
"enroll_arm_1" "22:00" "22:10" "23:05" "00:43" "00:53" -80520000 -1342 -22.366667
"enroll_arm_1" "22:47" "22:52" "23:16" "01:05" "01:16" -79860000 -1331  -22.18333
"enroll_arm_1" "21:28" "21:41" "22:46" "00:39" "00:53" -79620000 -1327 -22.116667
"enroll_arm_1" "21:20" "21:41" "22:35" "00:30" "00:48" -7.95e+07 -1325 -22.083334
"enroll_arm_1" "22:31" "22:53" "23:31" "01:26" "01:34" -7.95e+07 -1325 -22.083334
"enroll_arm_1" "11:55" "12:10" "12:43" "01:00" "01:07" -42180000  -703 -11.716666
"enroll_arm_1" "11:00" "11:30" "12:00" "01:08" "01:21" -39120000  -652 -10.866667
"enroll_arm_1" "11:20" "11:40" "12:22" "01:35" "01:40" -38820000  -647 -10.783334
"enroll_arm_1" "10:37" "10:47" "11:44" "01:45" "01:50" -35940000  -599  -9.983334
"enroll_arm_1" "09:06" "09:35" "10:21" "00:53" "01:09" -34080000  -568  -9.466666
"enroll_arm_1" "10:40" "10:55" "11:44" "02:26" "02:37" -33480000  -558       -9.3
"enroll_arm_1" "01:40" "02:08" "08:40" "03:37" "03:51" -18180000  -303      -5.05
"enroll_arm_1" "15:02" "15:20" "16:02" "16:13" "16:30"    660000    11  .18333334
"enroll_arm_1" "08:04" "08:45" "09:38" "09:58" "10:30"   1200000    20   .3333333
"enroll_arm_1" "23:20" "23:30" "00:08" "00:37" "00:43"   1740000    29   .4833333
"enroll_arm_1" "02:33" "04:52" "05:30" "06:00" "06:10"   1800000    30         .5
"enroll_arm_1" "01:16" "01:39" "02:19" "02:50" "02:55"   1860000    31  .51666665
"enroll_arm_1" "12:39" "12:50" "01:22" "01:56" "02:10"   2040000    34  .56666666
"enroll_arm_1" "16:00" "16:24" "17:16" "17:51" "17:59"   2100000    35   .5833333
"enroll_arm_1" "14:23" "14:37" "15:20" "15:55" "16:03"   2100000    35   .5833333
"enroll_arm_1" "16:04" "16:13" "16:41" "17:16" "17:26"   2100000    35   .5833333
"enroll_arm_1" "09:31" "10:00" "10:10" "10:46" "10:58"   2160000    36         .6
"enroll_arm_1" "02:01" "02:10" "02:48" "03:25" "03:30"   2220000    37   .6166667
"enroll_arm_1" "10:03" "11:02" "11:33" "12:10" "12:23"   2220000    37   .6166667
"enroll_arm_1" "06:45" "07:00" "08:27" "09:05" "09:20"   2280000    38   .6333333
"enroll_arm_1" "01:00" "01:15" "01:36" "02:15" "02:20"   2340000    39        .65
"enroll_arm_1" "05:08" "05:32" "06:21" "07:00" "07:07"   2340000    39        .65
"enroll_arm_1" "18:54" "19:06" "19:27" "20:06" "20:20"   2340000    39        .65
"enroll_arm_1" "03:00" "03:00" "04:20" "05:00" "05:10"   2400000    40   .6666667
"enroll_arm_1" "16:05" "16:01" "16:32" "17:12" "17:27"   2400000    40   .6666667
"enroll_arm_1" "10:35" "10:45" "11:30" "12:10" "12:15"   2400000    40   .6666667
"enroll_arm_1" "01:02" "01:38" "02:05" "02:46" "02:59"   2460000    41   .6833333
"enroll_arm_1" "22:30" "22:56" "23:08" "23:49" "23:54"   2460000    41   .6833333
"enroll_arm_1" "06:32" "06:37" "07:54" "08:35" "08:49"   2460000    41   .6833333
"enroll_arm_1" "03:41" "03:47" "04:32" "05:13" "05:24"   2460000    41   .6833333
"enroll_arm_1" "06:40" "07:02" "07:38" "08:20" "08:30"   2520000    42         .7
"enroll_arm_1" "04:45" "04:30" "05:00" "05:42" "05:47"   2520000    42         .7
"enroll_arm_1" "08:50" "09:04" "09:40" "10:23" "10:39"   2580000    43   .7166666
"enroll_arm_1" "13:03" "13:29" "14:00" "14:43" "14:58"   2580000    43   .7166666
"enroll_arm_1" "10:16" "10:40" "11:45" "12:28" "12:34"   2580000    43   .7166666
"enroll_arm_1" "10:49" "11:22" "11:52" "12:36" "12:55"   2640000    44   .7333333
"enroll_arm_1" "03:01" "03:14" "03:30" "04:14" "04:25"   2640000    44   .7333333
"enroll_arm_1" "22:21" "22:26" "23:04" "23:48" "23:54"   2640000    44   .7333333
"enroll_arm_1" "05:00" "05:10" "05:40" "06:25" "06:30"   2700000    45        .75
"enroll_arm_1" "11:43" "12:08" "12:43" "13:28" "13:38"   2700000    45        .75
"enroll_arm_1" "09:44" "09:50" "10:20" "11:05" "11:10"   2700000    45        .75
"enroll_arm_1" "15:00" "15:10" "10:06" "10:52" "10:57"   2760000    46   .7666667
"enroll_arm_1" "20:02" "21:03" "21:52" "22:38" "22:48"   2760000    46   .7666667
"enroll_arm_1" "14:30" "14:44" "15:10" "15:56" "16:10"   2760000    46   .7666667
"enroll_arm_1" "15:23" "15:35" "16:47" "17:33" "17:39"   2760000    46   .7666667
"enroll_arm_1" "11:10" "11:22" "12:02" "12:48" "12:53"   2760000    46   .7666667
"enroll_arm_1" "13:05" "13:21" "14:05" "14:51" "15:03"   2760000    46   .7666667
"enroll_arm_1" "18:10" "18:21" "19:00" "19:46" "19:53"   2760000    46   .7666667
"enroll_arm_1" "07:13" "07:31" "08:23" "09:11" "09:23"   2880000    48         .8
"enroll_arm_1" "11:04" "11:32" "12:15" "13:04" "13:16"   2940000    49   .8166667
"enroll_arm_1" "06:00" "06:25" "07:25" "08:15" "08:26"   3000000    50   .8333333
"enroll_arm_1" "12:40" "13:45" "14:26" "15:16" "15:41"   3000000    50   .8333333
"enroll_arm_1" "22:04" "22:25" "22:51" "23:41" "00:10"   3000000    50   .8333333
"enroll_arm_1" "08:00" "10:30" "11:26" "12:16" "12:35"   3000000    50   .8333333
"enroll_arm_1" "08:00" "08:15" "09:00" "09:50" "10:00"   3000000    50   .8333333
"enroll_arm_1" "05:20" "06:00" "06:40" "07:31" "07:50"   3060000    51        .85
"enroll_arm_1" "07:02" "07:14" "07:38" "08:30" "08:40"   3120000    52   .8666667
"enroll_arm_1" "19:04" "19:31" "20:17" "21:09" "21:20"   3120000    52   .8666667
"enroll_arm_1" "19:32" "19:48" "20:27" "21:20" "21:30"   3180000    53   .8833333
"enroll_arm_1" "09:45" "10:05" "10:53" "11:46" "11:55"   3180000    53   .8833333
"enroll_arm_1" "17:00" "17:53" "18:42" "19:35" "20:24"   3180000    53   .8833333
"enroll_arm_1" "18:00" "18:12" "18:47" "19:41" "19:54"   3240000    54         .9
"enroll_arm_1" "04:31" "04:36" "05:15" "06:10" "06:16"   3300000    55   .9166667
"enroll_arm_1" "13:10" "13:20" "13:50" "14:45" "14:55"   3300000    55   .9166667
"enroll_arm_1" "19:00" "19:33" "20:31" "21:26" "21:34"   3300000    55   .9166667
"enroll_arm_1" "12:50" "13:05" "13:31" "14:26" "14:32"   3300000    55   .9166667
"enroll_arm_1" "18:59" "19:15" "19:35" "20:30" "20:40"   3300000    55   .9166667
"enroll_arm_1" "05:00" "05:11" "06:05" "07:00" "07:21"   3300000    55   .9166667
"enroll_arm_1" "04:06" "04:16" "05:00" "05:55" "06:05"   3300000    55   .9166667
"enroll_arm_1" "07:34" "08:26" "09:34" "10:29" "10:40"   3300000    55   .9166667
"enroll_arm_1" "13:03" "14:22" "14:53" "15:48" "15:54"   3300000    55   .9166667
"enroll_arm_1" "06:00" "06:12" "06:44" "07:40" "07:50"   3360000    56   .9333333
"enroll_arm_1" "19:40" "19:00" "20:13" "21:10" "21:20"   3420000    57        .95
"enroll_arm_1" "21:20" "21:35" "22:16" "23:13" "23:25"   3420000    57        .95
"enroll_arm_1" "11:52" "12:09" "01:08" "02:06" "02:16"   3480000    58   .9666666
"enroll_arm_1" "05:13" "05:44" "06:27" "07:25" "07:39"   3480000    58   .9666666
"enroll_arm_1" "14:00" "14:10" "14:44" "15:42" "15:54"   3480000    58   .9666666
"enroll_arm_1" "05:07" "05:21" "05:53" "06:51" "07:13"   3480000    58   .9666666
"enroll_arm_1" "02:20" "02:25" "02:55" "03:54" "04:08"   3540000    59   .9833333
"enroll_arm_1" "03:41" "04:00" "04:43" "05:42" "05:57"   3540000    59   .9833333
"enroll_arm_1" "09:20" "09:30" "10:00" "11:00" "11:10"   3600000    60          1
"enroll_arm_1" "12:00" "12:30" "13:30" "14:30" "14:35"   3600000    60          1
"enroll_arm_1" "13:23" "13:37" "14:06" "15:06" "15:14"   3600000    60          1
"enroll_arm_1" "12:00" "12:53" "13:40" "14:40" "14:50"   3600000    60          1
"enroll_arm_1" "05:07" "05:20" "06:07" "07:07" "07:22"   3600000    60          1
"enroll_arm_1" "11:00" "11:16" "11:40" "12:40" "12:45"   3600000    60          1
"enroll_arm_1" "12:20" "12:25" "01:01" "02:01" "02:20"   3600000    60          1
"enroll_arm_1" "09:26" "09:41" "10:00" "11:00" "11:10"   3600000    60          1
"enroll_arm_1" "07:42" "08:09" "08:57" "09:57" "10:05"   3600000    60          1
"enroll_arm_1" "11:49" "12:04" "12:42" "13:43" "13:55"   3660000    61  1.0166667
end
format %tc op_duration

[Carole J. Wilson]

Here's the start of one solution. The logic is to add an arbitrary date to your times (01/01/1999). Then check if the date-time is "less than" the prep date. If it is less than the prep date, then change the date to 01/02/1999. This works for all but one of your cases (obs 17) where the prep time is 1:40, the operation start is 8:40, and the operation end is 3:37. In this case, all are greater than 1:40, but clearly operation end is the next day. The better way to proceed would be to test oper_arrive<oper_prep_start, oper_start<oper_arrive, oper_end<oper_start, etc. I thought I was being cute and set it up in a loop to test against oper_prep_start, but to get your final code, you will need to break up the loop into steps.

Anyway, hopefully this can get you started.

gen d_oper_prep_start = "01/01/1999 " + oper_prep_start 
generate double tc_oper_prep_start = clock(d_oper_prep_start, "MDYhm")
format tc_oper_prep_start %tc


foreach v of varlist oper_arrive oper_start oper_end oper_postop {
    gen d_`v' = "01/01/1999 " + `v'
    gen double tc_`v'=clock(d_`v', "MDYhm")
    replace d_`v' = "01/02/1999 " + `v' if tc_`v'<tc_oper_prep_start
    replace tc_`v'= clock(d_`v', "MDYhm") 
    format tc_`v' %tc
    }

    gen op_duration=tc_oper_end-tc_oper_start
    gen op_duration_min = op_duration/(60*1000)
    gen op_duration_hr = op_duration_min/60

[Patrick Albright]

This is excellent! Thank you so much for your help! Exactly what I was looking for!!