Milliseconds inconsistent when trying to find the earliest date/time across variables

[Jill Jak]

Despite reviewing help datetime, searching Statalist, and reaching out to colleagues, I cannot solve this issue and kindly request any insights you may have. Thank you in advance.

Goal: Find the earliest date and time from three datetime variables for an observation.
Issue: Despite using double precision and clock function, the earliest time is milliseconds inconsistent with the earliest time.
Version: Stata 17.0 on Windows 10

Code excerpt

* format operative date
gen date_op_date = date(op_date,"DM20Y")
format %-tdNN/DD/CCYY date_op_date

* format operative time
gen double time_op_time = clock(op_inc_time,"hm")
format %tcHH:MM time_op_time

* combine operative date and time
gen double date_time_op = dhms(date_op_date,hh(time_op_time),mm(time_op_time ),ss(time_op_time))
format date_time_op %tcNN/DD/CCYY_HH:MM:SS

* keep earliest date/time
gen date_time_anchor = min(date_time_ed_arr, date_time_admit, date_time_op)
format date_time_anchor %tcNN/DD/CCYY_HH:MM:SS


Output excerpt w/example data (anchor should be the lowest/earliest)

date_time_ed_arr---------date_time_admit-----------date_time_op---------------date_time_anchor
03/01/2019 01:51:00-----03/01/2019 06:59:00-----03/01/2019 10:51:00------03/01/2019 01:51:42
03/01/2019 11:35:00-----03/01/2019 16:55:00-----03/01/2019 17:15:00------03/01/2019 11:34:58
03/01/2019 00:45:00-----03/01/2019 09:06:00-----03/01/2019 18:45:00------03/01/2019 00:43:58
03/01/2018 02:03:00-----03/01/2018 02:50:00------03/01/2018 12:38:00-----03/01/2018 02:03:54

Example generated by -dataex-
clear
input double(date_time_ed_arr date_time_admit date_time_op) float date_time_anchor
1867024260000 1867042740000 1867056660000 1.8670243e+12
1.8670593e+12 1.8670785e+12 1.8670797e+12 1.8670593e+12
1.8670203e+12 1867050360000 1.8670851e+12 1.8670202e+12
1.8354495e+12 . 1835538180000 1.8354495e+12
1835488980000 1.8354918e+12 1835527080000 1.835489e+12
end
format %tcNN/DD/CCYY_HH:MM:SS date_time_ed_arr
format %tcNN/DD/CCYY_HH:MM:SS date_time_admit
format %tcNN/DD/CCYY_HH:MM:SS date_time_op
format %tcNN/DD/CCYY_HH:MM:SS date_time_anchor

[Andrew Musau]

Are you storing the result using double precision? This works for me.

clear
input double(date_time_ed_arr date_time_admit date_time_op) float date_time_anchor
1867024260000 1867042740000 1867056660000 1.8670243e+12
1.8670593e+12 1.8670785e+12 1.8670797e+12 1.8670593e+12
1.8670203e+12 1867050360000 1.8670851e+12 1.8670202e+12
1.8354495e+12 . 1835538180000 1.8354495e+12
1835488980000 1.8354918e+12 1835527080000 1.835489e+12
end
format %tcNN/DD/CCYY_HH:MM:SS date_time_ed_arr
format %tcNN/DD/CCYY_HH:MM:SS date_time_admit
format %tcNN/DD/CCYY_HH:MM:SS date_time_op
format %tcNN/DD/CCYY_HH:MM:SS date_time_anchor

egen double wanted= rowmin(date_time_ed_arr date_time_admit date_time_op)
format wanted %tc

Res.:

. l, sep(0)

     +------------------------------------------------------------------------------------------------------------+
     |    date_time_ed_arr       date_time_admit          date_time_op      date_time_anchor               wanted |
     |------------------------------------------------------------------------------------------------------------|
  1. | 03/01/2019 01:51:00   03/01/2019 06:59:00   03/01/2019 10:51:00   03/01/2019 01:51:42   01mar2019 01:51:00 |
  2. | 03/01/2019 11:35:00   03/01/2019 16:55:00   03/01/2019 17:15:00   03/01/2019 11:34:58   01mar2019 11:35:00 |
  3. | 03/01/2019 00:45:00   03/01/2019 09:06:00   03/01/2019 18:45:00   03/01/2019 00:43:58   01mar2019 00:45:00 |
  4. | 02/28/2018 15:05:00                     .   03/01/2018 15:43:00   02/28/2018 15:04:10   28feb2018 15:05:00 |
  5. | 03/01/2018 02:03:00   03/01/2018 02:50:00   03/01/2018 12:38:00   03/01/2018 02:03:54   01mar2018 02:03:00 |
     +------------------------------------------------------------------------------------------------------------+

.

[Jill Jak]

Andrew,

Brilliant!

You're exactly correct. I needed "double" precision on the rowmin

Thank you so much. I can't tell you how much I appreciate you helping me learn.

Jill