Date difference in hours keeping the date format

[alessio lombini]

Hello everybody,

I am trying to get the difference in hours from two date variables (start and end). The tricky part is that I would like to keep the HH:MM:SS format (as string) for the outcome variable (duration_hms). Here is an example of my data. Note that some duration_hms' values are already available to demonstrate what I am looking for, and that the variable q_totalduration shows the difference already computed in minutes (in the case it might be of any help).

Thank you in advance to whoever is willing to help!

* Example generated by -dataex-. For more info, type help dataex
clear
input double q_totalduration str9 duration_hms double(start end)
          10147.15 ""          1874780044000 1875388874000
22643.316666666666 ""          1874077736000 1875436336000
19854.283333333333 ""          1874253947000 1875445205000
           19462.8 ""          1874279876000 1875447645000
           24518.6 ""          1873978675000 1875449792000
            5901.1 ""          1875107642000 1875461709000
           20221.8 ""          1874248895000 1875462204000
           25537.4 ""          1873983237000 1875515482000
          11147.35 ""          1874950878000 1875619720000
23.183333333333334 "00:23:12"  1906135445000 1906136837000
             64.75 "01:04:46"  1905955413000 1905959299000
61.983333333333334 "01:01:59"  1906105480000 1906109199000
              49.9 "00:49:54"  1905756875000 1905759869000
 32.13333333333333 "00:32:09"  1906011427000 1906013356000
             125.2 "02:05:13"  1908081442000 1908088955000
 90.61666666666666 "01:30:38"  1905777318000 1905782756000
34.983333333333334 "00:35:00"  1906132538000 1906134638000
39.733333333333334 "00:39:45"  1906388359000 1906390744000
37891.316666666666 "631:31:20" 1905608040000 1907881520000
18.683333333333334 "00:18:42"  1907076430000 1907077552000
              46.5 "00:46:30"  1905860022000 1905862812000
end
format %tc start
format %tc end

EDIT: start and end appear in the following format: 27may2021 19:11:55

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input double q_totalduration str9 duration_hms double(start end)
          10147.15 ""          1874780044000 1875388874000
22643.316666666666 ""          1874077736000 1875436336000
19854.283333333333 ""          1874253947000 1875445205000
           19462.8 ""          1874279876000 1875447645000
           24518.6 ""          1873978675000 1875449792000
            5901.1 ""          1875107642000 1875461709000
           20221.8 ""          1874248895000 1875462204000
           25537.4 ""          1873983237000 1875515482000
          11147.35 ""          1874950878000 1875619720000
23.183333333333334 "00:23:12"  1906135445000 1906136837000
             64.75 "01:04:46"  1905955413000 1905959299000
61.983333333333334 "01:01:59"  1906105480000 1906109199000
              49.9 "00:49:54"  1905756875000 1905759869000
 32.13333333333333 "00:32:09"  1906011427000 1906013356000
             125.2 "02:05:13"  1908081442000 1908088955000
 90.61666666666666 "01:30:38"  1905777318000 1905782756000
34.983333333333334 "00:35:00"  1906132538000 1906134638000
39.733333333333334 "00:39:45"  1906388359000 1906390744000
37891.316666666666 "631:31:20" 1905608040000 1907881520000
18.683333333333334 "00:18:42"  1907076430000 1907077552000
              46.5 "00:46:30"  1905860022000 1905862812000
end
format %tc start
format %tc end


gen double diff= (end-start)/ (1000*60*60)
gen hrs= int(diff)
gen min= int((diff-hrs)*60)
gen sec= ((diff*60)-((hrs*60)+min))*60

gen wanted= cond(length(string(hrs))==1,"0"+string(hrs), string(hrs)) ///
+":"+  cond(length(string(min))==1,"0"+string(min), string(min))+":" ///
+ cond(length(string(sec))==1,"0"+string(sec), string(sec))

Res.:

. l, sep(0)

     +-----------------------------------------------------------------------------------------------------------+
     | q_total~n   duratio~s                start                  end        diff   hrs   min   sec      wanted |
     |-----------------------------------------------------------------------------------------------------------|
  1. |  10147.15               29may2019 20:14:04   05jun2019 21:21:14   169.11944   169     7    10   169:07:10 |
  2. | 22643.317               21may2019 17:08:56   06jun2019 10:32:16   377.38889   377    23    20   377:23:20 |
  3. | 19854.283               23may2019 18:05:47   06jun2019 13:00:05     330.905   330    54    18   330:54:18 |
  4. |   19462.8               24may2019 01:17:56   06jun2019 13:40:45   324.38028   324    22    49   324:22:49 |
  5. |   24518.6               20may2019 13:37:55   06jun2019 14:16:32   408.64361   408    38    37   408:38:37 |
  6. |    5901.1               02jun2019 15:14:02   06jun2019 17:35:09   98.351944    98    21     7    98:21:07 |
  7. |   20221.8               23may2019 16:41:35   06jun2019 17:43:24   337.03028   337     1    49   337:01:49 |
  8. |   25537.4               20may2019 14:53:57   07jun2019 08:31:22   425.62361   425    37    25   425:37:25 |
  9. |  11147.35               31may2019 19:41:18   08jun2019 13:28:40   185.78944   185    47    22   185:47:22 |
 10. | 23.183333    00:23:12   26may2020 18:04:05   26may2020 18:27:17   .38666667     0    23    12    00:23:12 |
 11. |     64.75    01:04:46   24may2020 16:03:33   24may2020 17:08:19   1.0794444     1     4    46    01:04:46 |
 12. | 61.983333    01:01:59   26may2020 09:44:40   26may2020 10:46:39   1.0330556     1     1    59    01:01:59 |
 13. |      49.9    00:49:54   22may2020 08:54:35   22may2020 09:44:29   .83166667     0    49    54    00:49:54 |
 14. | 32.133333    00:32:09   25may2020 07:37:07   25may2020 08:09:16   .53583333     0    32     9    00:32:09 |
 15. |     125.2    02:05:13   18jun2020 06:37:22   18jun2020 08:42:35   2.0869444     2     5    13    02:05:13 |
 16. | 90.616667    01:30:38   22may2020 14:35:18   22may2020 16:05:56   1.5105556     1    30    38    01:30:38 |
 17. | 34.983333    00:35:00   26may2020 17:15:38   26may2020 17:50:38   .58333333     0    35     0    00:35:00 |
 18. | 39.733333    00:39:45   29may2020 16:19:19   29may2020 16:59:04       .6625     0    39    45    00:39:45 |
 19. | 37891.317   631:31:20   20may2020 15:34:00   15jun2020 23:05:20   631.52222   631    31    20   631:31:20 |
 20. | 18.683333    00:18:42   06jun2020 15:27:10   06jun2020 15:45:52   .31166667     0    18    42    00:18:42 |
 21. |      46.5    00:46:30   23may2020 13:33:42   23may2020 14:20:12        .775     0    46    30    00:46:30 |
     +-----------------------------------------------------------------------------------------------------------+

[alessio lombini]

That is exactly what I was looking for! Thank you a lot, Andrew!