obtainning negative values when calculating elapsed minutes or substracting dates

[Paula Fortes]

hi, i am from chile so escuse my english please.
I am trying to obtain waiting times between dates/times from an er, i have the admission, triage, consult and discharge times, and when i generated the waiting times some appear as negative values or huge values, specially if the first date/time has a time at 23:00 something hours and the next is the next day at 00:02 hours as you can se in some lines, and also when the date 1 is 07:00 and next is 25:00 it counts as 50something hours, and is the same day,
can you please help me, i need to be able to give the median and range values of waiting times and cannot just mvdecode all the negatives,
please help me urgently

[Paula Fortes]

all date/times are double in %tc format

[Jean-Claude Arbaut]

I can't reproduce your result, and I don't see anything wrong with the following, using timestamps from your first row:

. sca tadm=tc(4feb2019 23:42:00)
. sca ttri=tc(4feb2019 23:51:00)
. sca tate=tc(5feb2019 00:07:00)
. sca tegr=tc(5feb2019 10:04:00)

. di minutes(ttri-tadm)
9

. di minutes(tate-ttri)
16

. di minutes(tegr-tate)
597

. di minutes(tegr-ttri)
613

. di minutes(tegr-tadm)
622

Note the durations are in minutes, hence the large values: there can be several hours or several days between admission and discharge. A day is 1440 minutes.

[Paula Fortes]

thanks for awnsering, i now abput the big values, yet i get -1000 on some just becase the start date is 01jan23:30 and teh end date is 02jan00:10, wich is 40 minutes and not 1000, is it am i making myself clear?

also there are

[Jean-Claude Arbaut]

Yet

. di minutes(tc(02jan2019 00:10:00)-tc(01jan2019 23:30:00))
40

So what's the problem? What did you type? It's neither negative, not 1000 minutes. It's 40 minutes, as expected.

You make yourself clear, and it's also quite clear you think that going from 23:59:59 to 00:00:00 causes problems, but it's wrong: %tc dates are double floating point numbers, which count milliseconds since 1960-01-01. Hours aren't even considered in computations, ony when converting back and forth to YYMMDD HHMMSS format, or when extracting components. For everything else, they are just integers.
And the behavior you describe don't seem to happen: the difference is not -1000 minutes.

So

1. What did you exactly type to compute the erroneous values?
2. What's your version of Stata, on which OS, and did you try the Installation Qualification Tool? It checks the MD5 sums of all official Stata files, in case they were damaged.
3. Do you have a legal copy of Stata? Otherwise it is said Stata can produce wrong results.

---

Additional note: in case you don't do it already, I advise you to store %tc values as doubles and not floats, as there is some precision loss. But nothing near what you have: with floats you would get the difference above as 39.3216 minutes instead of 40 minutes. To change the data type, the simplest is to type beforehand:

set type double

Put this in your profile.do so that you don't have to type it in every Stata session.
This won't convert automatically your files, it will just be the default type when you generate a new variable. This can be overriden, with for instance "gen byte var=...".

[Paula Fortes]

hi, i put this gen WaitTime=(triage-admission)/3600000*60, i use stata 12, windows 10 and should be original

[Paula Fortes]

is stata14, sorry

[Jean-Claude Arbaut]

Maybe I'm picky, but this is not exactly what you typed, as the variables have different names: HORA_ADMISION, HORATRIAGE and tpo_Adm_Tri (and other HORA* and tpo* variables). Can you confirm? Incidentally, tpo_Adm_Tri is the only one that looks good, so you have probably typed something else for the other ones, which is the reason why I asked what you typed.

So, what did you exactly type to generate all the tpo_* variables?

To be sure, can you post also the result of the following?

describe HORA*
di %20.4f HORA_ADMISION[1]
di %20.4f HORATRIAGE[1]
di %20.4f HORAATENCION[1]
di %20.4f FECHA_EGRESO[1]

[Paula Fortes]

it was the form, an example, i wrote this exactly:

gen double tpo_Adm_Tri= ( HORATRIAGE- HORA_ADMISION)/3600000*60
gen double tpo_Tri_Ate= ( HORAATENCION - HORATRIAGE )/3600000*60
410 . gen double tpo_Ate_Egre= ( FECHA_EGRESO - HORAATENCION )/3600000*60
411 . gen double tpo_Tri_Egre= ( FECHA_EGRESO - HORATRIAGE )/3600000*60
412 . gen double tpo_Adm_Egre= ( FECHA_EGRESO - HORA_ADMISION )/3600000*60

i have 5 different wait times, and all have some of those negative or very large results incongruent with the timing

[Jean-Claude Arbaut]

Ok, what about the code I show in the previous answer?
And while we are at it, what is the result of

di minutes(tc(05feb2019 00:07:00)-tc(04feb2019 23:51:00))
di minutes(tc(02jan2019 00:10:00)-tc(01jan2019 23:30:00))

And also the code in my first answer. Just to check. Your formula is correct (divide by 60000 to get the time in minutes), so it has to be something else.

It's probably not that, but it's mysterious, so let's check everything: if at one point you typed a wrong formula, you must either drop the variable before reusing gen, or you must use replace, as gen won't overwrite a variable. But you would have an error message to tell you what happens.

[Paula Fortes]

thanks, i'll try

[Paula Fortes]

. di minutes(tc(05feb2019 00:07:00)-tc(04feb2019 23:51:00))
16

. di minutes(tc(02jan2019 00:10:00)-tc(01jan2019 23:30:00))
40

[Jean-Claude Arbaut]

So, we can agree that this works?

What about:

describe HORA*
di %20.4f HORA_ADMISION[1]
di %20.4f HORATRIAGE[1]
di %20.4f HORAATENCION[1]
di %20.4f FECHA_EGRESO[1]

We have a problem here: tc() works, computing the difference and minutes() works, your variables are supposedly stored as %tc doubles, and the result is wrong.

What's left to see:
* retry to compute the variables (drop tpo_* and redo your formulas, in case you inadvertently kept something wrong)
* check the contents of the variables (without the format, hence the "di %20.4f..." I ask)
* check the Stata install (you'll have to install IQT and run it, which requires also downloading the siqe file on the page I point to above).

If this doesn't give an answer, I have nothing else to try. So please answer all of the questions above and in my preceding answers (you keep answering only partly, and it doesn't help), and try to drop/gen again, post the results I ask for, and let's see.

[William Lisowski]

Let me try a different perspective looking at post #4.

I notice that there are 1440 minutes in a day. The negative differences are exactly what you would get if the differences had been calculated ignoring the day, so that you would be subtracting a time in 23:00 hrs from a time in 00:00 hrs. But Stata doesn't "ignore the days" - the datetime value is just one big number.

But I notice that the variables start HORA which my very limited knowledge of Spanish tells me could mean "time" or could mean "hour".

It it at all possible that the HORA variables started off as just the hours-and-minutes, rather than the full date-and-time? And the subtraction was done then, which for observations that spanned midnight resulted in negative differences, but this wasn't noticed then? And later the HORA variable was replaced with date part added to the hours-and-minutes? And sometime later the negative differences were discovered?

I expect Jean-Claude's advice

retry to compute the variables (drop tpo_* and redo your formulas)

would give correct answers with the HORA values shown in post #1.

[Paula Fortes]

hi, thanks for awnsering, the datasets where given to me from teh statistics deparment and i don´t knnow if they manage teh variables in excel to add date and time, most alarming is that the las time or "hora" wich is the discharge time is the most altered and i am looding 7600 of 41000 data if i cannot correct this