How can I create variables condition on time stamps?

[Arto Arman]

Hi Everybody!

I have two variables that measure when a person checked into a facility and when they left again (see data example). As far as I know, the timestamps are already in a format, in which Stata can recognize it as a date and time. For example in the data editor it just says "25mar2022 22:00:00" and so forth. All timestamps are between 24th of March 2022, 12:00 and 27th of March 2022, 9:00.

I would now like to calculate a variable that indicates, whether the people's stay entailed at least one night. In other words, did they stay at the facility between 10PM and 6 AM at least once?
I find working with time variables in Stata very confusing. Can anybody help me with that?

Thanks a lot!

* Example generated by -dataex-. For more info, type help dataex
clear
input double(trial_start trial_end)
1.9638648e+12      1963915948000
1.9637532e+12 1963836405999.9998
1.9638576e+12 1963906080000.0002
 1.963926e+12      1963980095000
 1.963926e+12 1963983385000.0002
1.9639368e+12 1963996583999.9998
 1.963854e+12      1963939665000
 1.963782e+12      1963841815000
 1.963926e+12      1963996749000
1.9638576e+12 1963914894000.0002
 1.963818e+12 1963901958999.9998
1.9638468e+12 1963927559000.0002
1.9638936e+12      1963997112000
1.9638144e+12      1963981392000
1.9637784e+12      1963830336000
1.9637784e+12      1963838773000
1.9637676e+12      1963820090000
1.9638432e+12      1963933670000
1.9637712e+12 1963821229000.0002
1.9639224e+12 1963986526000.0002
1.9638072e+12 1963914968000.0002
1.9638432e+12      1963928894000
1.9637784e+12      1963822947000
   1.9638e+12 1963863196000.0002
1.9638432e+12      1963933447000
1.9638936e+12 1963927282000.0002
1.9637748e+12      1963826766000
1.9637676e+12      1963812671000
1.9638612e+12      1963919220000
 1.963854e+12 1963918831000.0002
1.9637568e+12 1963777575999.9998
1.9638216e+12      1963950069000
1.9639044e+12 1963987150000.0002
1.9637676e+12      1963818615000
1.9639044e+12      1963987609000
1.9638648e+12      1963914942000
1.9637496e+12      1963823218000
1.9638504e+12      1963889824000
1.9639044e+12 1963928166999.9998
1.9638468e+12 1963912961999.9998
1.9638072e+12 1963919009999.9998
1.9638252e+12      1963998072000
1.9637748e+12      1963823719000
1.9639368e+12      1963990991000
 1.963872e+12      1963937086000
1.9638684e+12      1963934661000
1.9637676e+12      1963820528000
1.9638072e+12      1963830564000
1.9638684e+12      1963933904000
1.9638036e+12      1963950656000
1.9638108e+12      1963895296000
1.9637604e+12      1963828394000
1.9638612e+12 1963926777999.9998
 1.963836e+12      1963947946000
1.9637676e+12 1963814405000.0002
1.9637496e+12      1963831805000
1.9637496e+12      1963824369000
 1.963746e+12      1963826694000
1.9639044e+12      1963997061000
1.9637568e+12      1963834618000
1.9637676e+12 1963808707999.9998
1.9638576e+12      1963912725000
1.9637676e+12      1963819920000
1.9638396e+12      1963857401000
1.9638288e+12      1963907089000
1.9637604e+12      1963827073000
1.9638288e+12      1963902836000
1.9637748e+12      1963831453000
1.9639224e+12      1963997546000
1.9639188e+12      1963991358000
1.9637568e+12 1963908154999.9998
1.9638396e+12      1963913054000
1.9638432e+12      1963949329000
1.9638972e+12      1963980346000
1.9637748e+12      1963824277000
1.9638396e+12      1963953405000
1.9638252e+12      1963911541000
1.9638216e+12 1963906301999.9998
1.9637496e+12      1963905843000
 1.963764e+12      1963822229000
1.9637676e+12 1963816929000.0002
 1.963746e+12      1963850093000
1.9638396e+12      1963850896000
1.9638036e+12      1963923184000
1.9637676e+12 1963818196999.9998
1.9639224e+12      1963997726000
1.9637568e+12      1963850452000
1.9637712e+12 1963850925000.0002
1.9637676e+12      1963819804000
 1.963746e+12 1963825150000.0002
  1.96389e+12      1963928469000
1.9638432e+12      1963928252000
1.9638468e+12      1963894875000
1.9637712e+12      1963863554000
1.9638324e+12      1963902990000
  1.96389e+12      1963994106000
1.9638684e+12      1963938808000
1.9639224e+12 1963998491999.9998
1.9639224e+12 1963986056999.9998
1.9637784e+12 1963825314000.0002
end
format %tc trial_start
format %tc trial_end

[Andrew Musau]

If these intervals are continuous, then you were in the time range if the start date differs from the end date or you checked in on the same day at or before 5.59 a.m. (You could also add checking in at 10 p.m. and leaving at or before midnight).

gen wanted= (dofc(trial_end)> dofc(trial_start)) |  ///
                          (dofc(trial_start)== dofc(trial_end))& inrange(hh(trial_start), 0, 5) | ///
                              (dofc(trial_start)== dofc(trial_end))& hh(trial_start)>=22

Res.:

. l, sep(0)

     +--------------------------------------------------+
     |        trial_start            trial_end   wanted |
     |--------------------------------------------------|
  1. | 25mar2022 22:00:00   26mar2022 12:12:28        1 |
  2. | 24mar2022 15:00:00   25mar2022 14:06:45        1 |
  3. | 25mar2022 20:00:00   26mar2022 09:28:00        1 |
  4. | 26mar2022 15:00:00   27mar2022 06:01:35        1 |
  5. | 26mar2022 15:00:00   27mar2022 06:56:25        1 |
  6. | 26mar2022 18:00:00   27mar2022 10:36:23        1 |
  7. | 25mar2022 19:00:00   26mar2022 18:47:45        1 |
  8. | 24mar2022 23:00:00   25mar2022 15:36:55        1 |
  9. | 26mar2022 15:00:00   27mar2022 10:39:09        1 |
 10. | 25mar2022 20:00:00   26mar2022 11:54:54        1 |
 11. | 25mar2022 09:00:00   26mar2022 08:19:18        1 |
 12. | 25mar2022 17:00:00   26mar2022 15:25:59        1 |
 13. | 26mar2022 06:00:00   27mar2022 10:45:12        1 |
 14. | 25mar2022 08:00:00   27mar2022 06:23:12        1 |
 15. | 24mar2022 22:00:00   25mar2022 12:25:36        1 |
 16. | 24mar2022 22:00:00   25mar2022 14:46:13        1 |
 17. | 24mar2022 19:00:00   25mar2022 09:34:50        1 |
 18. | 25mar2022 16:00:00   26mar2022 17:07:50        1 |
 19. | 24mar2022 20:00:00   25mar2022 09:53:49        1 |
 20. | 26mar2022 14:00:00   27mar2022 07:48:46        1 |
 21. | 25mar2022 06:00:00   26mar2022 11:56:08        1 |
 22. | 25mar2022 16:00:00   26mar2022 15:48:14        1 |
 23. | 24mar2022 22:00:00   25mar2022 10:22:27        1 |
 24. | 25mar2022 04:00:00   25mar2022 21:33:16        1 |
 25. | 25mar2022 16:00:00   26mar2022 17:04:07        1 |
 26. | 26mar2022 06:00:00   26mar2022 15:21:22        0 |
 27. | 24mar2022 21:00:00   25mar2022 11:26:06        1 |
 28. | 24mar2022 19:00:00   25mar2022 07:31:11        1 |
 29. | 25mar2022 21:00:00   26mar2022 13:07:00        1 |
 30. | 25mar2022 19:00:00   26mar2022 13:00:31        1 |
 31. | 24mar2022 16:00:00   24mar2022 21:46:15        0 |
 32. | 25mar2022 10:00:00   26mar2022 21:41:09        1 |
 33. | 26mar2022 09:00:00   27mar2022 07:59:10        1 |
 34. | 24mar2022 19:00:00   25mar2022 09:10:15        1 |
 35. | 26mar2022 09:00:00   27mar2022 08:06:49        1 |
 36. | 25mar2022 22:00:00   26mar2022 11:55:42        1 |
 37. | 24mar2022 14:00:00   25mar2022 10:26:58        1 |
 38. | 25mar2022 18:00:00   26mar2022 04:57:04        1 |
 39. | 26mar2022 09:00:00   26mar2022 15:36:06        0 |
 40. | 25mar2022 17:00:00   26mar2022 11:22:41        1 |
 41. | 25mar2022 06:00:00   26mar2022 13:03:29        1 |
 42. | 25mar2022 11:00:00   27mar2022 11:01:12        1 |
 43. | 24mar2022 21:00:00   25mar2022 10:35:19        1 |
 44. | 26mar2022 18:00:00   27mar2022 09:03:11        1 |
 45. | 26mar2022 00:00:00   26mar2022 18:04:46        1 |
 46. | 25mar2022 23:00:00   26mar2022 17:24:21        1 |
 47. | 24mar2022 19:00:00   25mar2022 09:42:08        1 |
 48. | 25mar2022 06:00:00   25mar2022 12:29:24        0 |
 49. | 25mar2022 23:00:00   26mar2022 17:11:44        1 |
 50. | 25mar2022 05:00:00   26mar2022 21:50:56        1 |
 51. | 25mar2022 07:00:00   26mar2022 06:28:16        1 |
 52. | 24mar2022 17:00:00   25mar2022 11:53:14        1 |
 53. | 25mar2022 21:00:00   26mar2022 15:12:57        1 |
 54. | 25mar2022 14:00:00   26mar2022 21:05:46        1 |
 55. | 24mar2022 19:00:00   25mar2022 08:00:05        1 |
 56. | 24mar2022 14:00:00   25mar2022 12:50:05        1 |
 57. | 24mar2022 14:00:00   25mar2022 10:46:09        1 |
 58. | 24mar2022 13:00:00   25mar2022 11:24:54        1 |
 59. | 26mar2022 09:00:00   27mar2022 10:44:21        1 |
 60. | 24mar2022 16:00:00   25mar2022 13:36:58        1 |
 61. | 24mar2022 19:00:00   25mar2022 06:25:07        1 |
 62. | 25mar2022 20:00:00   26mar2022 11:18:45        1 |
 63. | 24mar2022 19:00:00   25mar2022 09:32:00        1 |
 64. | 25mar2022 15:00:00   25mar2022 19:56:41        0 |
 65. | 25mar2022 12:00:00   26mar2022 09:44:49        1 |
 66. | 24mar2022 17:00:00   25mar2022 11:31:13        1 |
 67. | 25mar2022 12:00:00   26mar2022 08:33:56        1 |
 68. | 24mar2022 21:00:00   25mar2022 12:44:13        1 |
 69. | 26mar2022 14:00:00   27mar2022 10:52:26        1 |
 70. | 26mar2022 13:00:00   27mar2022 09:09:18        1 |
 71. | 24mar2022 16:00:00   26mar2022 10:02:34        1 |
 72. | 25mar2022 15:00:00   26mar2022 11:24:14        1 |
 73. | 25mar2022 16:00:00   26mar2022 21:28:49        1 |
 74. | 26mar2022 07:00:00   27mar2022 06:05:46        1 |
 75. | 24mar2022 21:00:00   25mar2022 10:44:37        1 |
 76. | 25mar2022 15:00:00   26mar2022 22:36:45        1 |
 77. | 25mar2022 11:00:00   26mar2022 10:59:01        1 |
 78. | 25mar2022 10:00:00   26mar2022 09:31:41        1 |
 79. | 24mar2022 14:00:00   26mar2022 09:24:03        1 |
 80. | 24mar2022 18:00:00   25mar2022 10:10:29        1 |
 81. | 24mar2022 19:00:00   25mar2022 08:42:09        1 |
 82. | 24mar2022 13:00:00   25mar2022 17:54:53        1 |
 83. | 25mar2022 15:00:00   25mar2022 18:08:16        0 |
 84. | 25mar2022 05:00:00   26mar2022 14:13:04        1 |
 85. | 24mar2022 19:00:00   25mar2022 09:03:16        1 |
 86. | 26mar2022 14:00:00   27mar2022 10:55:26        1 |
 87. | 24mar2022 16:00:00   25mar2022 18:00:52        1 |
 88. | 24mar2022 20:00:00   25mar2022 18:08:45        1 |
 89. | 24mar2022 19:00:00   25mar2022 09:30:04        1 |
 90. | 24mar2022 13:00:00   25mar2022 10:59:10        1 |
 91. | 26mar2022 05:00:00   26mar2022 15:41:09        1 |
 92. | 25mar2022 16:00:00   26mar2022 15:37:32        1 |
 93. | 25mar2022 17:00:00   26mar2022 06:21:15        1 |
 94. | 24mar2022 20:00:00   25mar2022 21:39:14        1 |
 95. | 25mar2022 13:00:00   26mar2022 08:36:30        1 |
 96. | 26mar2022 05:00:00   27mar2022 09:55:06        1 |
 97. | 25mar2022 23:00:00   26mar2022 18:33:28        1 |
 98. | 26mar2022 14:00:00   27mar2022 11:08:11        1 |
 99. | 26mar2022 14:00:00   27mar2022 07:40:56        1 |
100. | 24mar2022 22:00:00   25mar2022 11:01:54        1 |
     +--------------------------------------------------+

[Benno Schoenberger]

Hi Arto,

try the following:

gen hour_start = hh(trial_start)
gen date_start = dofc(trial_start)
gen hour_end = hh(trial_end)
gen date_end = dofc(trial_end)
gen datediff = date_end - date_start
format date_* %td
local tresh_start 22
local tresh_end    6

gen overnight = (datediff==1 & hour_start<=`tresh_start' & hour_end>=`tresh_end') | datediff >1

All the best,
Benno

[Benno Schoenberger]

Uups,didn't refresh the page before posting, otherwise I would have seen Andrew's post beforehand.

[Arto Arman]

Great, thanks a lot for the quick help!