How to convert HRF dates into SIF without loosing milliseconds information

[Grettel Romero]

Hi,
I have dates in a string format as follows:
2012-04-01 21:06:55.8170
I have tried using the following commands to convert the strings into a SIF format

1.- gen double opent=clock(opentime, "YMDhms"), with this command I just generate a new variable with "missing values"

2.- The next thing I tried was adding a # to the mask as follows: gen double opent=clock(opentime, "YMDhms#"). The new variable transforms the original string into a SIF, but as I converted the variable back into a HRF (%tC), I noticed that the milliseconds information was lost.

Any suggestionss as to what i am doing wrong?

Thanx

[Nick Cox]

You have times to 0.1 ms resolution. Stata's date-time functions will go ms resolution but no further.

[Grettel Romero]

Hi Nick,
Thank you for your quick answer!
Do you maby know another way to solve the problem? In one of the Stata Manuals following example with milliseconds is given to convert the HRF format info SIF Typ
datetime 20jan2010 09:15:22.120

generate double eventtime = clock(mystr, "YMDhms")

http://www.timberlake.co.uk/media/wy.../manuals/d.pdf
on page 106

Thank you

[Nick Cox]

That's the same question once again, so far as I can see. I can't see an example with 0.1 ms resolution on that page, or indeed any use of clock() either.

[William Lisowski]

Perhaps the problem concerns the display format. The example below shows the truncation of the times to milliseconds and the subsequent display at the millisecond precision using an appropriate display format.

clear
input str50 opentime
"2012-04-01 21:06:55.8170"
"2012-04-01 21:06:55.8171"
"2012-04-01 21:06:55.8172"
"2012-04-01 21:06:55.8173"
"2012-04-01 21:06:55.8174"
"2012-04-01 21:06:55.8175"
"2012-04-01 21:06:55.8176"
"2012-04-01 21:06:55.8177"
"2012-04-01 21:06:55.8178"
"2012-04-01 21:06:55.8179"
end
generate double dtms = clock(opentime,"YMDhms#")
format dtms %tcCCYY-NN-DD_HH:MM:SS.sss
list, clean noobs

                    opentime                      dtms  
    2012-04-01 21:06:55.8170   2012-04-01 21:06:55.817  
    2012-04-01 21:06:55.8171   2012-04-01 21:06:55.817  
    2012-04-01 21:06:55.8172   2012-04-01 21:06:55.817  
    2012-04-01 21:06:55.8173   2012-04-01 21:06:55.817  
    2012-04-01 21:06:55.8174   2012-04-01 21:06:55.817  
    2012-04-01 21:06:55.8175   2012-04-01 21:06:55.817  
    2012-04-01 21:06:55.8176   2012-04-01 21:06:55.817  
    2012-04-01 21:06:55.8177   2012-04-01 21:06:55.817  
    2012-04-01 21:06:55.8178   2012-04-01 21:06:55.817  
    2012-04-01 21:06:55.8179   2012-04-01 21:06:55.817

[Nick Cox]

If you follow William's helpful example with

. format dtms %23.0f 

. list dtms 

     +---------------+
     |          dtms |
     |---------------|
  1. | 1648933615817 |
  2. | 1648933615817 |
  3. | 1648933615817 |
  4. | 1648933615817 |
  5. | 1648933615817 |
     |---------------|
  6. | 1648933615817 |
  7. | 1648933615817 |
  8. | 1648933615817 |
  9. | 1648933615817 |
 10. | 1648933615817 |
     +---------------+

you will see that the results are consistent with my statement that resolutions finer than ms are not supported. At best, the extra decimals are ignored, meaning that times are truncated downwards.

[Grettel Romero]

Hi William,

thank you too for your help. But I think Nick is right.
With regard to my example, (2012-04-01 21:06:55.8170) I tried your suggested command:

generate double dtms = clock(opentime,"YMDhms#")

The Time and Date were convertet into a SIF format. However when I converted it back to a HRF to check for the result in the new cell was correct (2012-04-01 21:06:55.8170 ) though, if I click on the cell the value showed is just 2012-04-01 21:06:55.
Thus the results are consistent with Nick´s statement that the miliseconds are truncated downwards.......

@ Nick:
Nick I´m a little confused, just for my understanding, Stata converts dates and times into milliseconds, but it is not able to add milliseconds within a Time and Date when converting them?

[Nick Cox]

I don't understand your new question. Please give an example (data, code, results) of what you mean. In general

date-time + another date-time = yet another date-time

[Clyde Schechter]

Grettl: I think you missed Nick's point. In "2012-04-01 21:06:55.8179", that final digit 9 is not milliseconds, it's tenths of milliseconds and Stata cannot represent that level of precision within a double. As William has shown you, you can get Stata to represent the time internally in milliseconds, and display it in milliseconds. But that is the limit.

[Grettel Romero]

Hi Nick:

I would like to know the time difference between this two timestamps:

opentime closetime
2012-04-01 21:07:06.5800 2012-04-01 21:07:31.8600

I used following code to convert them into a SIF format:
gen double o=clock(opentime, "YMDhms#")
gen double c=clock(closetime, "YMDhms#")

(Without the # the code just generates missing values.....)

Results:
o c
1.649e+12 1.649e+12

Prove, converting the SIF values into HRF´s:
format o %tcDDmonCCYY_HH:MM:SS.sss Result: 01apr2012 21:07:06
format c %tcDDmonCCYY_HH:MM:SS.sss Result: 01apr2012 21:07:31

The results are not 100% right, your statement that stata truncates the results is showed at this point.....


t= c-o

t
25280

If I understand the result of t correct, it is given in milliseconds and if i divide it by 1000 i´ll obtain the resuls in seconds right?


Thank you!

[Clyde Schechter]

Prove, converting the SIF values into HRF´s:
format o %tcDDmonCCYY_HH:MM:SS.sss Result: 01apr2012 21:07:06
format c %tcDDmonCCYY_HH:MM:SS.sss Result: 01apr2012 21:07:31

The results are not 100% right, your statement that stata truncates the results is showed at this point.....

I can't reproduce those results. See below:

. clear* 

. set obs 1
number of observations (_N) was 0, now 1

. gen double opentime = clock("2012-04-01 21:07:06.5800", "YMDhms#")

. gen double closetime = clock("2012-04-01 21:07:31.8600", "YMDhms#")

. format _all %tcDDmonCCYY_HH:MM:SS.sss

. list

     +-------------------------------------------------+
     |               opentime                closetime |
     |-------------------------------------------------|
  1. | 01apr2012 21:07:06.580   01apr2012 21:07:31.860 |
     +-------------------------------------------------+

You must be doing something slightly different. Please copy and paste commands and Stata's response directly from the Results window or your log file into a code block here, without editing so we can see what's going on.

[William Lisowski]

I think you do not understand the difference between how numbers are stored internally and how they are represented when displayed.

When you displayed o and c, Stata chose to display them using a format (%9.0g) that does not render them in the full precision in which they are stored. Had you specified a format of, say, %23.0f as Nick did, you would see them in their full precision.

With that said, much of what has transpired here has been the result of difficulties understanding your statement of the problem. Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. See especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using CODE delimiters, as described in section 12 of the FAQ.

Also, some fussy advice, but well meant, and also explained in the Statalist FAQ: We ask for full real names here. Please take the time to click "Contact Us" below and request your registration name be changed to include your personal and family names.

And as Clyde demonstrated, crossing my adding the below to my post, I too do not obtain the results you describe.

. input str50 (opentime closetime)

                                               opentime                                        closetime
  1. "2012-04-01 21:07:06.5800" "2012-04-01 21:07:31.8600"
  2. end

. gen double o=clock(opentime, "YMDhms#")

. gen double c=clock(closetime, "YMDhms#")

. format o %tcDDmonCCYY_HH:MM:SS.sss

. format c %tcDDmonCCYY_HH:MM:SS.sss

. list, clean noobs

                    opentime                  closetime                        o                        c  
    2012-04-01 21:07:06.5800   2012-04-01 21:07:31.8600   01apr2012 21:07:06.580   01apr2012 21:07:31.860

[Nick Cox]

I don't know exactly what you are doing because you don't show reproducible code throughout the question. Please do study http://www.statalist.org/forums/help#stata as you are prompted to do at every post.

But your example is not inherently problematic as the times can be held exactly as ms. The difference is, indeed, in ms.

Assigning date-time display formats should not be thought of as any kind of conversion. The date-time values remain exactly as they were; the difference is only that Stata interprets them for you as requested on display. Ironically, or otherwise, to understand what is going on in calculations, it is best to assign a format that shows large integers exactly,

clear
input str42 (opentime closetime)
"2012-04-01 21:07:06.5800" "2012-04-01 21:07:31.8600"
end

gen double o = clock(opentime, "YMDhms#")
gen double c = clock(closetime, "YMDhms#")
gen double d = o - c
format ? %23.0f
list ?


     +----------------------------------------+
     |             o               c        d |
     |----------------------------------------|
  1. | 1648933626580   1648933651860   -25280 |
     +----------------------------------------+

.

[Grettel Romero]

Hello Clyde,
your example helped me to understand Nick´s point.
in my data set all the timestamps have a zero at the end (as you explained it to me tenths of milliseconds).

2012-04-01 21:07:06.5800
2012-04-01 21:07:57.3700
2012-04-01 21:10:35.8330

So would it be Ok to drop the last zero of my Data so that stata does not have difficulties in converting it? Would i get the miliseconds precision?

[Nick Cox]

Whether you remove the last digit or Stata ignores it, it's the same result.