Take Difference of time variable (hh:mm:ss)

[Malik Ali]

Hi I have the following data:

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 id long date double time
"000336AE7" 15526          40227000
"000336AE7" 15530          49511000
"000336AE7" 15545          57641000
"000336AE7" 15546          58839000
"000336AE7" 15547          44251000
"000336AE7" 15547          57238000
"000336AE7" 15573          39211000
"000336AE7" 15573          42033000
"000336AE7" 15573          49890000
"000336AE7" 15573          52938000
"000336AE7" 15575          51965000
"000336AE7" 15580          47456000
"000336AE7" 15582          39471000
"000336AE7" 15586          44234000
"000336AE7" 15603          54900000
"000336AE7" 15603          54900000
"000336AE7" 15606          56005000
"000336AE7" 15613          57960000
"000336AE7" 15613          57960000
"000336AE7" 15613          57960000
"000336AE7" 15613          58020000
"000336AE7" 15613          58020000
"000336AE7" 15638          59065000
"000336AE7" 15644          50221000
"000336AE7" 15645          35717000
"000336AE7" 15659          58897000
"000336AE7" 15669          38068000
"000336AE7" 15669          38068000
"000336AE7" 15670          56848000
"000336AE7" 15683          46772000
"000336AE7" 15683          54720000
"000336AE7" 15687          41880000
"000336AE7" 15734          38702000
"000336AE7" 15775          56062000
"000336AE7" 15775          56078000
"000336AE7" 15777          48120000
"000336AE7" 15782          56672000
"000336AE7" 15792          45300000
"000336AE7" 15792          46521000
"000336AE7" 15795          47029000
"000336AE7" 15795          49910000
"000336AE7" 15795          54470000
"000336AE7" 15796          36110000
"000336AE7" 15796          56450000
"000336AE7" 15796          56450000
"000336AE7" 15796          56450000
"000336AE7" 15796          56450000
"000336AE7" 15796          56450000
"000336AE7" 15796          59160000
"000336AE7" 15796          59595000
"000336AE7" 15796          59595000
"000336AE7" 15796          59608000
"000336AE7" 15796          59609000
"000336AE7" 15797          38450000
"000336AE7" 15797          39855000
"000336AE7" 15797          50090000
"000336AE7" 15797          59390000
"000336AE7" 15797          59510000
"000336AE7" 15797          59510000
"000336AE7" 15798          40713000
"000336AE7" 15798          44570000
"000336AE7" 15798          44570000
"000336AE7" 15798          44570000
"000336AE7" 15810          58099000
"000336AE7" 15811          39832000
"000336AE7" 15811          39900000
"000336AE7" 15811          39900000
"000336AE7" 15820          52519000
"000336AE7" 15820          56906000
"000336AE7" 15820          60052000
"000336AE7" 15820          60074000
"000336AE7" 15823          52715000
"000336AE7" 15823          52800000
"000336AE7" 15823          53023000
"000336AE7" 15823          53023000
"000336AE7" 15823          53650000
"000336AE7" 15823          53710000
"000336AE7" 15823          54643000
"000336AE7" 15823          54643000
"000336AE7" 15823          54647000
"000336AE7" 15823          55692000
"000336AE7" 15823          55871000
"000336AE7" 15824          41068000
"000336AE7" 15824          44580000
"000336AE7" 15824          45768000
"000336AE7" 15824          49800000
"000336AE7" 15824 62125000.00000001
"000336AE7" 15824          62908000
"000336AE7" 15824          63197000
"000336AE7" 15824          63419000
"000336AE7" 15831          59503000
"000336AE7" 15831          59570000
"000336AE7" 15833          60983000
"000336AE7" 15837          56983000
"000336AE7" 15838          58178000
"000336AE7" 15846          40060000
"000336AE7" 15859          41949000
"000336AE7" 15859          43380000
"000336AE7" 15859          44476000
"000336AE7" 15859          51470000
end
format %td date
format %tcHH:MM:SS time

The variables are id, date and time (hh:mm:ss). By every id and date, I want to take the difference in the time variable and want the difference in minutes. Would appreciate if someone could help in getting the difference between the time variable. Thank you.

[Andrew Musau]

Stata datetime variables are stored in milliseconds, so you need to divide the differences by 1000 × 60 to convert them to minutes. I assume you’re asking for time differences between consecutive observations, in which case you can do the following:

gen double datetime= cofd(date)+time
format datetime %tc
bys id (datetime): gen wanted= (datetime- datetime[_n-1])/ (1000*60)

Res.:

. l, sepby(id)

     +------------------------------------------------------------------+
     |        id        date       time             datetime     wanted |
     |------------------------------------------------------------------|
  1. | 000336AE7   05jul2002   11:10:27   05jul2002 11:10:27          . |
  2. | 000336AE7   09jul2002   13:45:11   09jul2002 13:45:11   5914.733 |
  3. | 000336AE7   24jul2002   16:00:41   24jul2002 16:00:41    21735.5 |
  4. | 000336AE7   25jul2002   16:20:39   25jul2002 16:20:39   1459.967 |
  5. | 000336AE7   26jul2002   12:17:31   26jul2002 12:17:31   1196.867 |
  6. | 000336AE7   26jul2002   15:53:58   26jul2002 15:53:58     216.45 |
  7. | 000336AE7   21aug2002   10:53:31   21aug2002 10:53:31   37139.55 |
  8. | 000336AE7   21aug2002   11:40:33   21aug2002 11:40:33   47.03333 |
  9. | 000336AE7   21aug2002   13:51:30   21aug2002 13:51:30     130.95 |
 10. | 000336AE7   21aug2002   14:42:18   21aug2002 14:42:18       50.8 |
 11. | 000336AE7   23aug2002   14:26:05   23aug2002 14:26:05   2863.783 |
 12. | 000336AE7   28aug2002   13:10:56   28aug2002 13:10:56    7124.85 |
 13. | 000336AE7   30aug2002   10:57:51   30aug2002 10:57:51   2746.917 |
 14. | 000336AE7   03sep2002   12:17:14   03sep2002 12:17:14   5839.383 |
 15. | 000336AE7   20sep2002   15:15:00   20sep2002 15:15:00   24657.77 |
 16. | 000336AE7   20sep2002   15:15:00   20sep2002 15:15:00          0 |
 17. | 000336AE7   23sep2002   15:33:25   23sep2002 15:33:25   4338.417 |
 18. | 000336AE7   30sep2002   16:06:00   30sep2002 16:06:00   10112.58 |
 19. | 000336AE7   30sep2002   16:06:00   30sep2002 16:06:00          0 |
 20. | 000336AE7   30sep2002   16:06:00   30sep2002 16:06:00          0 |
 21. | 000336AE7   30sep2002   16:07:00   30sep2002 16:07:00          1 |
 22. | 000336AE7   30sep2002   16:07:00   30sep2002 16:07:00          0 |
 23. | 000336AE7   25oct2002   16:24:25   25oct2002 16:24:25   36017.42 |
 24. | 000336AE7   31oct2002   13:57:01   31oct2002 13:57:01     8492.6 |
 25. | 000336AE7   01nov2002   09:55:17   01nov2002 09:55:17   1198.267 |
 26. | 000336AE7   15nov2002   16:21:37   15nov2002 16:21:37   20546.33 |
 27. | 000336AE7   25nov2002   10:34:28   25nov2002 10:34:28   14052.85 |
 28. | 000336AE7   25nov2002   10:34:28   25nov2002 10:34:28          0 |
 29. | 000336AE7   26nov2002   15:47:28   26nov2002 15:47:28       1753 |
 30. | 000336AE7   09dec2002   12:59:32   09dec2002 12:59:32   18552.07 |
 31. | 000336AE7   09dec2002   15:12:00   09dec2002 15:12:00   132.4667 |
 32. | 000336AE7   13dec2002   11:38:00   13dec2002 11:38:00       5546 |
 33. | 000336AE7   29jan2003   10:45:02   29jan2003 10:45:02   67627.03 |
 34. | 000336AE7   11mar2003   15:34:22   11mar2003 15:34:22   59329.33 |
 35. | 000336AE7   11mar2003   15:34:38   11mar2003 15:34:38   .2666667 |
 36. | 000336AE7   13mar2003   13:22:00   13mar2003 13:22:00   2747.367 |
 37. | 000336AE7   18mar2003   15:44:32   18mar2003 15:44:32   7342.533 |
 38. | 000336AE7   28mar2003   12:35:00   28mar2003 12:35:00   14210.47 |
 39. | 000336AE7   28mar2003   12:55:21   28mar2003 12:55:21      20.35 |
 40. | 000336AE7   31mar2003   13:03:49   31mar2003 13:03:49   4328.467 |
 41. | 000336AE7   31mar2003   13:51:50   31mar2003 13:51:50   48.01667 |
 42. | 000336AE7   31mar2003   15:07:50   31mar2003 15:07:50         76 |
 43. | 000336AE7   01apr2003   10:01:50   01apr2003 10:01:50       1134 |
 44. | 000336AE7   01apr2003   15:40:50   01apr2003 15:40:50        339 |
 45. | 000336AE7   01apr2003   15:40:50   01apr2003 15:40:50          0 |
 46. | 000336AE7   01apr2003   15:40:50   01apr2003 15:40:50          0 |
 47. | 000336AE7   01apr2003   15:40:50   01apr2003 15:40:50          0 |
 48. | 000336AE7   01apr2003   15:40:50   01apr2003 15:40:50          0 |
 49. | 000336AE7   01apr2003   16:26:00   01apr2003 16:26:00   45.16667 |
 50. | 000336AE7   01apr2003   16:33:15   01apr2003 16:33:15       7.25 |
 51. | 000336AE7   01apr2003   16:33:15   01apr2003 16:33:15          0 |
 52. | 000336AE7   01apr2003   16:33:28   01apr2003 16:33:28   .2166667 |
 53. | 000336AE7   01apr2003   16:33:29   01apr2003 16:33:29   .0166667 |
 54. | 000336AE7   02apr2003   10:40:50   02apr2003 10:40:50    1087.35 |
 55. | 000336AE7   02apr2003   11:04:15   02apr2003 11:04:15   23.41667 |
 56. | 000336AE7   02apr2003   13:54:50   02apr2003 13:54:50   170.5833 |
 57. | 000336AE7   02apr2003   16:29:50   02apr2003 16:29:50        155 |
 58. | 000336AE7   02apr2003   16:31:50   02apr2003 16:31:50          2 |
 59. | 000336AE7   02apr2003   16:31:50   02apr2003 16:31:50          0 |
 60. | 000336AE7   03apr2003   11:18:33   03apr2003 11:18:33   1126.717 |
 61. | 000336AE7   03apr2003   12:22:50   03apr2003 12:22:50   64.28333 |
 62. | 000336AE7   03apr2003   12:22:50   03apr2003 12:22:50          0 |
 63. | 000336AE7   03apr2003   12:22:50   03apr2003 12:22:50          0 |
 64. | 000336AE7   15apr2003   16:08:19   15apr2003 16:08:19   17505.48 |
 65. | 000336AE7   16apr2003   11:03:52   16apr2003 11:03:52    1135.55 |
 66. | 000336AE7   16apr2003   11:05:00   16apr2003 11:05:00   1.133333 |
 67. | 000336AE7   16apr2003   11:05:00   16apr2003 11:05:00          0 |
 68. | 000336AE7   25apr2003   14:35:19   25apr2003 14:35:19   13170.32 |
 69. | 000336AE7   25apr2003   15:48:26   25apr2003 15:48:26   73.11667 |
 70. | 000336AE7   25apr2003   16:40:52   25apr2003 16:40:52   52.43333 |
 71. | 000336AE7   25apr2003   16:41:14   25apr2003 16:41:14   .3666667 |
 72. | 000336AE7   28apr2003   14:38:35   28apr2003 14:38:35    4197.35 |
 73. | 000336AE7   28apr2003   14:40:00   28apr2003 14:40:00   1.416667 |
 74. | 000336AE7   28apr2003   14:43:43   28apr2003 14:43:43   3.716667 |
 75. | 000336AE7   28apr2003   14:43:43   28apr2003 14:43:43          0 |
 76. | 000336AE7   28apr2003   14:54:10   28apr2003 14:54:10      10.45 |
 77. | 000336AE7   28apr2003   14:55:10   28apr2003 14:55:10          1 |
 78. | 000336AE7   28apr2003   15:10:43   28apr2003 15:10:43      15.55 |
 79. | 000336AE7   28apr2003   15:10:43   28apr2003 15:10:43          0 |
 80. | 000336AE7   28apr2003   15:10:47   28apr2003 15:10:47   .0666667 |
 81. | 000336AE7   28apr2003   15:28:12   28apr2003 15:28:12   17.41667 |
 82. | 000336AE7   28apr2003   15:31:11   28apr2003 15:31:11   2.983333 |
 83. | 000336AE7   29apr2003   11:24:28   29apr2003 11:24:28   1193.283 |
 84. | 000336AE7   29apr2003   12:23:00   29apr2003 12:23:00   58.53333 |
 85. | 000336AE7   29apr2003   12:42:48   29apr2003 12:42:48       19.8 |
 86. | 000336AE7   29apr2003   13:50:00   29apr2003 13:50:00       67.2 |
 87. | 000336AE7   29apr2003   17:15:25   29apr2003 17:15:25   205.4167 |
 88. | 000336AE7   29apr2003   17:28:28   29apr2003 17:28:28      13.05 |
 89. | 000336AE7   29apr2003   17:33:17   29apr2003 17:33:17   4.816667 |
 90. | 000336AE7   29apr2003   17:36:59   29apr2003 17:36:59        3.7 |
 91. | 000336AE7   06may2003   16:31:43   06may2003 16:31:43   10014.73 |
 92. | 000336AE7   06may2003   16:32:50   06may2003 16:32:50   1.116667 |
 93. | 000336AE7   08may2003   16:56:23   08may2003 16:56:23    2903.55 |
 94. | 000336AE7   12may2003   15:49:43   12may2003 15:49:43   5693.333 |
 95. | 000336AE7   13may2003   16:09:38   13may2003 16:09:38   1459.917 |
 96. | 000336AE7   21may2003   11:07:40   21may2003 11:07:40   11218.03 |
 97. | 000336AE7   03jun2003   11:39:09   03jun2003 11:39:09   18751.48 |
 98. | 000336AE7   03jun2003   12:03:00   03jun2003 12:03:00      23.85 |
 99. | 000336AE7   03jun2003   12:21:16   03jun2003 12:21:16   18.26667 |
100. | 000336AE7   03jun2003   14:17:50   03jun2003 14:17:50   116.5667 |
     +------------------------------------------------------------------+

[Clyde Schechter]

If you are using version 17 or later, you can slightly simplify the final line of Andrew Musau 's code:

gen double datetime= cofd(date)+time
format datetime %tc
by id (datetime), sort: gen wanted= clockdiff_frac(datetime[_n-1], datetime, "min")

I can't say that this is all that much simpler, but it has the virtue of not requiring you to know the arithmetic needed to convert milliseconds to minutes. It would also be more transparent to somebody reading the code who has minimal familiarity with Stata: it doesn't require you to know that Stata represents clock times in milliseconds.