Calculate differences in time

[Arto Arman]

Hi Everybody!

I have a data set with the variable "startdate_ms", which describes the moment when participants started filling out an online survey. They start on March 27th, 8:41 AM and finish on the 5th of April 7:20 PM.
I would like to calculate the hours it took from inviting people to the survey to the time that they started it. We sent out survey invitations on March 27th, 8:00 AM, so the shortest amount of time recorded would be 41minutes.

I think it would be best to just create a variable that is set to March 27th, 8:00 AM for all observations and then calcuate the difference in time between the newly created variable and the "startdate_ms", but I don't know how.
Can somebody help me please? Thanks a lot!

Best,
Arto

* Example generated by -dataex-. For more info, type help dataex
clear
input double startdate_ms
1963989706000.0002
1963989709000.0002
1963989803999.9998
     1963989915000
1963989927999.9998
1963989983999.9998
     1963990012000
     1963990026000
     1963990066000
     1963990125000
     1963990125000
1963990260000.0002
1963990266000.0002
1963990279000.0002
     1963990327000
1963990365000.0002
     1963990370000
1963990387999.9998
     1963990402000
     1963990418000
1963990454999.9998
     1963990493000
1963990524000.0002
1963990575999.9998
     1963990585000
     1963990655000
1963990685000.0002
     1.9639907e+12
     1963990711000
     1963990722000
1963990739000.0002
     1963990749000
1963990750999.9998
1963990771000.0002
     1963990811000
     1963990862000
1963990879000.0002
1963990903999.9998
     1963990934000
     1963990967000
     1963991002000
1963991005999.9998
     1963991034000
1963991051000.0002
1963991175000.0002
     1963991185000
1963991212000.0002
     1963991349000
     1963991362000
     1.9639914e+12
     1963991405000
1963991446000.0002
1963991446000.0002
1963991449999.9998
1963991530000.0002
     1963991575000
1963991616000.0002
1963991726999.9998
     1963991773000
     1963991878000
1963991925000.0002
1963991933999.9998
1963991992000.0002
     1963992002000
1963992008000.0002
     1963992206000
1963992256999.9998
1963992285000.0002
     1963992287000
1963992312999.9998
1963992522999.9998
1963992560000.0002
     1963992715000
     1963992884000
     1963992887000
     1963992890000
1963992904999.9998
1963992939999.9998
     1963992984000
     1963993035000
1963993054999.9998
1963993135999.9998
1963993208000.0002
     1963993277000
     1963993304000
1963993342999.9998
     1963993382000
     1963993401000
1963993447000.0002
     1963993457000
1963993495999.9998
1963993630999.9998
1963993815999.9998
1963993872000.0002
1963993931999.9998
1963993966000.0002
1963994141000.0002
     1963994264000
     1963994379000
1963994477999.9998
end
format %tcnn/dd/ccYY_hh:MM startdate_ms

[Nick Cox]

There is no need to put a constant into a variable. What you seem to want is something like this. (I got crazy results until I realised that the dates and times were in 2022.

* Example generated by -dataex-. For more info, type help dataex
clear
input double startdate_ms
1963989706000.0002
1963989709000.0002
1963989803999.9998
     1963989915000
1963989927999.9998
1963989983999.9998
     1963990012000
     1963990026000
     1963990066000
     1963990125000
     1963990125000
1963990260000.0002
1963990266000.0002
1963990279000.0002
     1963990327000
1963990365000.0002
     1963990370000
1963990387999.9998
     1963990402000
     1963990418000
end 

format startdate_ms %tc 
gen double wanted = (startdate_ms - clock("2022 March 27 8:00", "YMD hm")) / (1000 * 60 * 60)     

sort startdate_ms 

list 
    

    +--------------------------------+
     |       startdate_ms      wanted |
     |--------------------------------|
  1. | 27mar2022 08:41:46   .69611111 |
  2. | 27mar2022 08:41:49   .69694444 |
  3. | 27mar2022 08:43:23   .72333333 |
  4. | 27mar2022 08:45:15   .75416667 |
  5. | 27mar2022 08:45:27   .75777778 |
     |--------------------------------|
  6. | 27mar2022 08:46:23   .77333333 |
  7. | 27mar2022 08:46:52   .78111111 |
  8. | 27mar2022 08:47:06        .785 |
  9. | 27mar2022 08:47:46   .79611111 |
 10. | 27mar2022 08:48:45       .8125 |
     |--------------------------------|
 11. | 27mar2022 08:48:45       .8125 |
 12. | 27mar2022 08:51:00         .85 |
 13. | 27mar2022 08:51:06   .85166667 |
 14. | 27mar2022 08:51:19   .85527778 |
 15. | 27mar2022 08:52:07   .86861111 |
     |--------------------------------|
 16. | 27mar2022 08:52:45   .87916667 |
 17. | 27mar2022 08:52:50   .88055556 |
 18. | 27mar2022 08:53:07   .88555556 |
 19. | 27mar2022 08:53:22   .88944444 |
 20. | 27mar2022 08:53:38   .89388889 |
     +--------------------------------+

[Andrew Musau]

di %tcnn/dd/ccYY_hh:MM  clock("3/27/2022 8:00", "MDYhm")

Res.:

. di %tcnn/dd/ccYY_hh:MM  clock("3/27/2022 8:00", "MDYhm")
  3/27/2022 8:00

suggests that in units of hours

gen wanted= (startdate_ms- clock("3/27/2022 8:00", "MDYhm"))/3.6e+6

Res.:

. l, sep(0)

     +----------------------------+
     |    startdate_ms     wanted |
     |----------------------------|
  1. |  3/27/2022 8:41   .6961111 |
  2. |  3/27/2022 8:41   .6969444 |
  3. |  3/27/2022 8:43   .7233334 |
  4. |  3/27/2022 8:45   .7541667 |
  5. |  3/27/2022 8:45   .7577778 |
  6. |  3/27/2022 8:46   .7733333 |
  7. |  3/27/2022 8:46   .7811111 |
  8. |  3/27/2022 8:47       .785 |
  9. |  3/27/2022 8:47   .7961111 |
 10. |  3/27/2022 8:48      .8125 |
 11. |  3/27/2022 8:48      .8125 |
 12. |  3/27/2022 8:51        .85 |
 13. |  3/27/2022 8:51   .8516667 |
 14. |  3/27/2022 8:51   .8552778 |
 15. |  3/27/2022 8:52   .8686111 |
 16. |  3/27/2022 8:52   .8791667 |
 17. |  3/27/2022 8:52   .8805556 |
 18. |  3/27/2022 8:53   .8855556 |
 19. |  3/27/2022 8:53   .8894445 |
 20. |  3/27/2022 8:53   .8938889 |
 21. |  3/27/2022 8:54   .9041666 |
 22. |  3/27/2022 8:54   .9147222 |
 23. |  3/27/2022 8:55   .9233333 |
 24. |  3/27/2022 8:56   .9377778 |
 25. |  3/27/2022 8:56   .9402778 |
 26. |  3/27/2022 8:57   .9597222 |
 27. |  3/27/2022 8:58   .9680555 |
 28. |  3/27/2022 8:58   .9722222 |
 29. |  3/27/2022 8:58   .9752778 |
 30. |  3/27/2022 8:58   .9783334 |
 31. |  3/27/2022 8:58   .9830555 |
 32. |  3/27/2022 8:59   .9858333 |
 33. |  3/27/2022 8:59   .9863889 |
 34. |  3/27/2022 8:59   .9919444 |
 35. |  3/27/2022 9:00   1.003056 |
 36. |  3/27/2022 9:01   1.017222 |
 37. |  3/27/2022 9:01   1.021944 |
 38. |  3/27/2022 9:01   1.028889 |
 39. |  3/27/2022 9:02   1.037222 |
 40. |  3/27/2022 9:02   1.046389 |
 41. |  3/27/2022 9:03   1.056111 |
 42. |  3/27/2022 9:03   1.057222 |
 43. |  3/27/2022 9:03      1.065 |
 44. |  3/27/2022 9:04   1.069722 |
 45. |  3/27/2022 9:06   1.104167 |
 46. |  3/27/2022 9:06   1.106944 |
 47. |  3/27/2022 9:06   1.114444 |
 48. |  3/27/2022 9:09     1.1525 |
 49. |  3/27/2022 9:09   1.156111 |
 50. |  3/27/2022 9:10   1.166667 |
 51. |  3/27/2022 9:10   1.168056 |
 52. |  3/27/2022 9:10   1.179444 |
 53. |  3/27/2022 9:10   1.179444 |
 54. |  3/27/2022 9:10   1.180556 |
 55. |  3/27/2022 9:12   1.202778 |
 56. |  3/27/2022 9:12   1.215278 |
 57. |  3/27/2022 9:13   1.226667 |
 58. |  3/27/2022 9:15     1.2575 |
 59. |  3/27/2022 9:16   1.270278 |
 60. |  3/27/2022 9:17   1.299444 |
 61. |  3/27/2022 9:18     1.3125 |
 62. |  3/27/2022 9:18      1.315 |
 63. |  3/27/2022 9:19   1.331111 |
 64. |  3/27/2022 9:20   1.333889 |
 65. |  3/27/2022 9:20   1.335556 |
 66. |  3/27/2022 9:23   1.390556 |
 67. |  3/27/2022 9:24   1.404722 |
 68. |  3/27/2022 9:24     1.4125 |
 69. |  3/27/2022 9:24   1.413056 |
 70. |  3/27/2022 9:25   1.420278 |
 71. |  3/27/2022 9:28   1.478611 |
 72. |  3/27/2022 9:29   1.488889 |
 73. |  3/27/2022 9:31   1.531944 |
 74. |  3/27/2022 9:34   1.578889 |
 75. |  3/27/2022 9:34   1.579722 |
 76. |  3/27/2022 9:34   1.580556 |
 77. |  3/27/2022 9:35   1.584722 |
 78. |  3/27/2022 9:35   1.594444 |
 79. |  3/27/2022 9:36   1.606667 |
 80. |  3/27/2022 9:37   1.620833 |
 81. |  3/27/2022 9:37   1.626389 |
 82. |  3/27/2022 9:38   1.648889 |
 83. |  3/27/2022 9:40   1.668889 |
 84. |  3/27/2022 9:41   1.688056 |
 85. |  3/27/2022 9:41   1.695556 |
 86. |  3/27/2022 9:42   1.706389 |
 87. |  3/27/2022 9:43   1.717222 |
 88. |  3/27/2022 9:43     1.7225 |
 89. |  3/27/2022 9:44   1.735278 |
 90. |  3/27/2022 9:44   1.738056 |
 91. |  3/27/2022 9:44   1.748889 |
 92. |  3/27/2022 9:47   1.786389 |
 93. |  3/27/2022 9:50   1.837778 |
 94. |  3/27/2022 9:51   1.853333 |
 95. |  3/27/2022 9:52       1.87 |
 96. |  3/27/2022 9:52   1.879444 |
 97. |  3/27/2022 9:55   1.928056 |
 98. |  3/27/2022 9:57   1.962222 |
 99. |  3/27/2022 9:59   1.994167 |
100. | 3/27/2022 10:01   2.021667 |
     +----------------------------+

.

[Arto Arman]

sorry, I should have specified the year. thanks a lot for the help, that's exactly what I needed!