Change Numeric Variable to Time

[Mone Jang]

I have a data set with a date and time variable as below. My ultimate goal is to calculate the different in hours between date1 at time1 and date2 at time2. However, I am having significant problems converting the time1 and time2 variable into a mm:hh format which I can then use to combine with my date variable. Any suggestions?

date1	time1	date2	time2
11/2/2016	11:00	11/3/2016	14:30
11/15/2016	21:45	11/16/2016	11:45
12/21/2016	9:30	12/22/2016	9:55
11/16/2016	10:00	11/17/2016	10:00
11/28/2016	8:26	11/29/2016	13:46
12/1/2016	15:30	12/2/2016	13:49
12/4/2016	18:25	12/6/2016	8:45
12/14/2016	22:00	12/16/2016	8:45
12/27/2016	14:50	12/28/2016	16:30
12/29/2016	15:30	12/30/2016	10:00
1/1/2017	18:40	1/2/2017	14:20
1/4/2017	22:35	1/5/2017	11:40
1/5/2017	1010	1/6/2017	15:50
1/9/2017	1902	1/10/2017	15:10
12/6/2016	1502	12/7/2016	9:29

[Clyde Schechter]

Your data display, though well intended, is almost for the purpose of developing code. It provides none of the critically important metadata about the representation of these dates and times internal to Stata. It only tells us what they look like to your eyes-and that is of almost no value at all for this purpose. The useful way to show example data is to use the -dataex- command. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

If you post back with -datex- output to illustrate the problem, I'm confident you will get a timely and helpful response.

[Mone Jang]

Thanks Clyde. Please see the -datex- output below.


----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input int(dod tod doaut toaut_s)
20832 1300 20835 1545
20850  800 20852 1400
20940 1540 20943 1142
20950 1345 20951 1355
20961  630 20961 1645
20963 1400 20965 1355
20979  440 20979 1450
21001 1845 21003 1610
21034  500 21035 1440
21039 2337 21040 1345
21040 2204 21041 1557
21045 2130 21046 1530
21046 1130 21047 1340
21111 1740 21113 1247
21128  735 21130 1515
21131  730 21131 1400
21139  620 21139 1556
21144  600 21144 1530
21209 2030 21210 1245
21212 2030 21213 1533
21217  745 21217 1501
21263  730 21264 1420
21378  130 21378 1219
21384 1318 21385 1358
21424  630 21425 1218
21488 2350 21489 1302
21510  210 21510 1325
21513 2030 21515 1318
21516  640 21516 1603
21557  700 21557 1303
21559  923 21560 1314
21678 1900 21679 1303
21690 1100 21692 1336
20762  630 20762 1645
20787 1945 20788 1200
20796    5 20796 1522
20800 1700 20801 1400
20807 1520 20809  935
20831 2345 20832 1535
20835  415 20836 1632
20838 1815 20839 1640
20841 1320 20842 1525
20849  900 20849 1642
20849 1320 20850 1715
20877 1230 20878 1548
20880  430 20880 1600
20883 1330 20884 1816
20887  330 20888 1558
20901  645 20902 1507
20919  250 20919 1530
20920  635 20920 1310
20926 2125 20927 1715
20948  615 20948 1530
20957  330 20957 1220
20958  500 20958 1555
20970 1930 20971 1620
20972  200 20972 1620
20982  200 20984 1600
20990  610 20991 1555
20998 2020 21000 1516
21003 2130 21004 1740
21005 2300 21006 1712
21011 2230 21012 1744
21023 1240 21025 1548
21031  130 21031 1618
21033  720 21034 1301
21041   57 21042 1533
21044 1729 21045 1640
21054 1900 21055 1618
21055 1330 21056 1710
21061   30 21061 1715
21061 1430 21062 1820
21065 1640 21067 1628
21080  835 21082 1430
21088 2105 21088 1631
21100 1847 21102 1518
21102 1920 21103 1535
21103 1445 21104 1530
21108 1615 21109 1433
21110 1517 21111 1612
21119  210 21119 1320
21125  320 21125 1645
21138  445 21138 1620
21145  450 21145 1627
21149 1615 21151 1453
21158  215 21158 1358
21159  330 21159 1638
21160 1200 21161 1535
21163 2325 21164 1730
21163 1820 21165 1713
21165 1233 21167 1404
21166 2220 21168 1223
21170 2201 21172 1342
21174 1428 21175 1506
21179 1355 21180 1652
21181   17 21182 1622
21185 2045 21186 1723
21188 1030 21188 1605
21192 1000 21192 1723
21192 1635 21195 1137
end
format %tdnn/dd/CCYY dod
format %tdnn/dd/CCYY doaut

------------------ copy up to and including the previous line ------------------

Listed 100 out of 202 observations
Use the count() option to list more

[William Lisowski]

Perhaps this example code will start you in a useful direction.

generate double dt_od  = dhms(dod,   floor(tod    /100), mod(tod,    100),0), after(tod)
generate double dt_aut = dhms(doaut, floor(toaut_s/100), mod(toaut_s,100),0), after(toaut_s)
format %tc dt_od dt_aut
list in 1/10, clean noobs

. list in 1/10, clean noobs

          dod    tod                dt_od       doaut   toaut_s               dt_aut  
    1/13/2017   1300   13jan2017 13:00:00   1/16/2017      1545   16jan2017 15:45:00  
    1/31/2017    800   31jan2017 08:00:00    2/2/2017      1400   02feb2017 14:00:00  
     5/1/2017   1540   01may2017 15:40:00    5/4/2017      1142   04may2017 11:42:00  
    5/11/2017   1345   11may2017 13:45:00   5/12/2017      1355   12may2017 13:55:00  
    5/22/2017    630   22may2017 06:30:00   5/22/2017      1645   22may2017 16:45:00  
    5/24/2017   1400   24may2017 14:00:00   5/26/2017      1355   26may2017 13:55:00  
     6/9/2017    440   09jun2017 04:40:00    6/9/2017      1450   09jun2017 14:50:00  
     7/1/2017   1845   01jul2017 18:45:00    7/3/2017      1610   03jul2017 16:10:00  
     8/3/2017    500   03aug2017 05:00:00    8/4/2017      1440   04aug2017 14:40:00  
     8/8/2017   2337   08aug2017 23:37:00    8/9/2017      1345   09aug2017 13:45:00

Added in edit:

Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

[Clyde Schechter]

//  EXTRACT HOURS AND MINUTES FROM THE t* VARIABLES
foreach v of varlist tod toaut_s {
    gen byte `v'_hrs = floor(`v'/100)
    gen byte `v'_min = mod(`v', 100)
}

//  CALCULATE A DATE TIME VARIABLE FOR EACH
gen double dod_datetime = cofC(Cdhms(dod, tod_hrs, tod_min, 0))
gen double doaut_datetime = cofC(Cdhms(doaut, toaut_s_hrs, toaut_s_min, 0))
format *_datetime %tc

//  GET THE DIFFERENCE
gen double time_difference_in_hours = clockdiff_frac(dod_datetime, doaut_datetime, "h")

Added: Crossed with #4. The two solutions are largely the same.

[Mone Jang]

Thanks Clyde and William for these solutions. I struggled so much the day prior thinking about how to generate these new variables. I'll spend some more time with the datetime documentation.