Difference between time variables stored as string variable

[Al Bothwell]

I need help to calculate the time difference between two times. However, both variables are in string format and contain "NA". I need to find a way to exclude the NA from the calculation of the time difference. Your help will be appreciated.

A sample of the data is displayed below.

* Example generated by -dataex-. For more info, type help dataex
clear
input str8(time_of_lea_placement time_of_delivery)
"09:30:00" "15:35:00"
"17:15:00" "19:37:00"
"08:22:00" "17:18:00"
"NA"       "19:57:00"
"19:50:00" "07:34:00"
"14:10:00" "18:18:00"
"NA"       "14:52:00"
"08:00:00" "13:08:00"
"10:00:00" "14:37:00"
"22:45:00" "15:36:00"
"07:40:00" "13:38:00"
"10:31:00" "16:21:00"
"09:50:00" "16:51:00"
"NA"       "18:10:00"
"17:10:00" "21:28:00"
"19:45:00" "21:01:00"
"NA"       "13:25:00"
"09:00:00" "17:06:00"
"08:20:00" "15:30:00"
"15:07:00" "19:00:00"
end

[Andrew Musau]

With date-time functions, you need not bother excluding strings that do not match the required pattern. -clock()- is one such function.

help datetime

Differences are in units of hours below. Negative values in the variable "wanted" indicate different days, so you need to have information on days to get accurate results if you are dealing with 24 hour+ periods.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8(time_of_lea_placement time_of_delivery)
"09:30:00" "15:35:00"
"17:15:00" "19:37:00"
"08:22:00" "17:18:00"
"NA"       "19:57:00"
"19:50:00" "07:34:00"
"14:10:00" "18:18:00"
"NA"       "14:52:00"
"08:00:00" "13:08:00"
"10:00:00" "14:37:00"
"22:45:00" "15:36:00"
"07:40:00" "13:38:00"
"10:31:00" "16:21:00"
"09:50:00" "16:51:00"
"NA"       "18:10:00"
"17:10:00" "21:28:00"
"19:45:00" "21:01:00"
"NA"       "13:25:00"
"09:00:00" "17:06:00"
"08:20:00" "15:30:00"
"15:07:00" "19:00:00"
end

gen wanted= (clock(time_of_delivery , "hms") - clock(time_of_lea_placement , "hms"))/3.6e+6
gen wanted2= cond(clock(time_of_delivery , "hms")< clock(time_of_lea_placement , "hms"), ///
    (clock("23:59:59", "hms")-clock(time_of_lea_placement , "hms") +clock(time_of_delivery , "hms"))/3.6e+6, ///
         (clock(time_of_delivery , "hms") - clock(time_of_lea_placement , "hms"))/3.6e+6)

Res.:

. l, sep(0)

     +--------------------------------------------+
     | time_o~t   time_o~y      wanted    wanted2 |
     |--------------------------------------------|
  1. | 09:30:00   15:35:00    6.083333   6.083333 |
  2. | 17:15:00   19:37:00    2.366667   2.366667 |
  3. | 08:22:00   17:18:00    8.933333   8.933333 |
  4. |       NA   19:57:00           .          . |
  5. | 19:50:00   07:34:00   -12.26667   11.73306 |
  6. | 14:10:00   18:18:00    4.133333   4.133333 |
  7. |       NA   14:52:00           .          . |
  8. | 08:00:00   13:08:00    5.133333   5.133333 |
  9. | 10:00:00   14:37:00    4.616667   4.616667 |
 10. | 22:45:00   15:36:00       -7.15   16.84972 |
 11. | 07:40:00   13:38:00    5.966667   5.966667 |
 12. | 10:31:00   16:21:00    5.833333   5.833333 |
 13. | 09:50:00   16:51:00    7.016667   7.016667 |
 14. |       NA   18:10:00           .          . |
 15. | 17:10:00   21:28:00         4.3        4.3 |
 16. | 19:45:00   21:01:00    1.266667   1.266667 |
 17. |       NA   13:25:00           .          . |
 18. | 09:00:00   17:06:00         8.1        8.1 |
 19. | 08:20:00   15:30:00    7.166667   7.166667 |
 20. | 15:07:00   19:00:00    3.883333   3.883333 |
     +--------------------------------------------+

[Al Bothwell]

Thank you, Andrew for the the solution. As you rightly noted, some of the times are 24 hours+ so I need date & time. Here is the date and time data. I would like to have the time difference (in hours) from datetime_lea_placement to datetime_birth.

I am reading the tutorials at "help datetime" but I haven't found a solution yet.

* Example generated by -dataex-. For more info, type help dataex
clear
input str16(datetime_lea_placement datetime_birth)
"   1/2/2002 9:30" "   1/2/2002 3:35"
"   1/5/2002 5:15" "   1/5/2002 7:37"
"   1/7/2009 8:22" "   1/7/2009 5:18"
"NA"               "   1/9/1999 7:57"
"   2/5/2006 7:50" "   2/6/2006 7:34"
"   2/7/2002 2:10" "   2/7/2002 6:17"
"NA"               "   3/3/2003 2:51"
"   3/3/2006 8:00" "   3/3/2006 1:08"
"   3/6/2006 9:59" "   3/6/2006 2:37"
"  3/7/2006 10:44" "   3/8/2006 3:36"
"   3/9/1999 7:40" "   3/9/1999 1:38"
"  4/1/1999 10:31" "   4/1/1999 4:21"
"   4/3/2005 9:49" "   4/3/2005 4:50"
"NA"               "   4/4/2003 6:10"
"   4/6/2009 5:10" "   4/6/2009 9:27"
"   5/1/2001 7:44" "   5/1/2001 9:01"
"NA"               "   5/2/2003 1:25"
"   5/2/2005 9:00" "   5/2/2005 5:06"
"   5/5/2009 8:19" "   5/5/2009 3:30"
"   5/7/1998 3:07" "   5/7/1998 6:59"
"   5/7/2004 3:00" "   5/7/2004 4:38"
"  5/8/1998 11:41" "   5/8/1998 6:49"
"   5/9/1999 9:59" "   5/9/1999 5:00"
"NA"               "   6/1/2007 5:51"
"   6/5/1999 3:00" "   6/5/1999 5:28"
"   6/9/2000 1:40" "   6/9/2000 8:54"
"NA"               "   6/9/2001 3:56"
"   7/1/2005 5:15" "   7/1/2005 7:15"
"  7/7/2003 11:05" "   7/7/2003 8:44"
"   8/2/2009 3:36" "   8/2/2009 6:26"
end

[Andrew Musau]

For some of your observations, it appears that placement is after birth whereas for others, birth is after placement. Use a 24-hour clock to indicate times or indicate whether a particular time is am or pm. Assuming the date format is MDY:

replace datetime_lea_placement= trim(datetime_lea_placement)
replace datetime_birth = trim(datetime_birth)
gen wanted= (clock(datetime_birth, "MDYhm") - clock(datetime_lea_placement, "MDYhm"))/3.6e+6