miliseconds datetime convertion

[Luis Pecht]

Hi ,

my dataset has a datetime string like this:

clear
input str28 activityDateTime
"2022-11-29T18:42:07.5027244Z"
"2022-11-29T18:42:06.8798184Z"
"2022-11-29T18:42:05.4312594Z"
"2022-11-29T18:32:22.4548754Z"
"2022-11-29T18:30:08.0438916Z"
"2022-11-29T18:29:27.8196684Z"
"2022-11-29T17:21:02.8832722Z"
"2022-11-29T17:21:02.266528Z" 
"2022-11-29T17:21:01.0909388Z"
"2022-11-29T14:42:34.7783454Z"
end

I am curious about how do I convert them to numerically encoded Stata format (clock, maybe) retaining as much precision as possible, elegantly, I mean, not splitting the string.

thks.

[Clyde Schechter]

gen double wanted = clock(activityDateTime, "YMD#hms#")
format wanted %tcCCYY-NN-DD_hh:mm:ss.sss

will give you the most precision possible in Stata, both in terms of what is actually stored and in terms of what is shown in Stata outputs. However, that is not the full precision of your original data. The maximum precision of a clock variable in Stata is the millisecond, so you cannot go beyond the third decimal place in the time.

That said, unless you are analyzing data from particle physics experiments or something like that, it is hard to imagine that your time measurements actually have more fine-grained precision than the millisecond level, or that you need that level of precision for your analyses.

[Luis Pecht]

thanks Prof. Schechter, for the quick replay and I am completely OK with the millisecond precision.

. gen double wanted = clock(activityDateTime, "YMD#hms#")

. format wanted %tcCCYY-NN-DD_hh:mm:ss.sss

. list activityDateTime wanted in 1/10

     +-------------------------------------------------------+
     |             activityDateTime                   wanted |
     |-------------------------------------------------------|
  1. | 2022-11-29T18:42:07.5027244Z    2022-11-29 6:42:7.502 |
  2. | 2022-11-29T18:42:06.8798184Z    2022-11-29 6:42:6.879 |
  3. | 2022-11-29T18:42:05.4312594Z    2022-11-29 6:42:5.431 |
  4. | 2022-11-29T18:32:22.4548754Z   2022-11-29 6:32:22.454 |
  5. | 2022-11-29T18:30:08.0438916Z    2022-11-29 6:30:8.043 |
     |-------------------------------------------------------|
  6. | 2022-11-29T18:29:27.8196684Z   2022-11-29 6:29:27.819 |
  7. | 2022-11-29T17:21:02.8832722Z    2022-11-29 5:21:2.883 |
  8. |  2022-11-29T17:21:02.266528Z    2022-11-29 5:21:2.266 |
  9. | 2022-11-29T17:21:01.0909388Z    2022-11-29 5:21:1.090 |
 10. | 2022-11-29T14:42:34.7783454Z   2022-11-29 2:42:34.778 |
     +-------------------------------------------------------+

it seem 12hs late. Am I reading it right?

[Clyde Schechter]

Oh, my mistake, sorry. The numerical info Stata has is right, but it is displaying it incorrectly. Change the format command to:

format wanted %tcCCYY-NN-DD_HH:MM:SS.sss

With the upper case format, the hours, minutes, and seconds will display correctly. The lower case format I used is meant to be used with an AM/PM indicator, which I did not include.