Convert string to time including milliseconds

[John Smithers]

I have a variable containing strings in the following format

StringVar
"2018-12-27 14:28:41.4861930"

I would like to convert it into a variable with a time format STATA will recognize, keeping the precision to the millisecond level. E.g. the # of milliseconds since 1960 would be perfect.

I tried among other things the following, but it delivered only missing values.
gen time2=clock(StringVar,"DMYhms")

Any ideas?
Thank you for the help...

[Clyde Schechter]

The reason -gen time2=clock(StringVar,"DMYhms")- gave you missing values is that 2018 is not the Day of the month, and 27 is not a Year. You need the second argument of -clock()- to correctly specify the order in which the components appear in the string.

gen time2=clock(StringVar,"YMDhms")

Added: Also, you cannot specify more than three decimal places in the seconds position. Milliseconds is the maximum precision, and confronted with a string that goes to tenths of a microsecond, Stata balks because it cannot store that information without loss of precision.

[Nick Cox]

Clyde gave excellent advice as always. I add further: specify

gen double time2 =

[Sergiy Radyakin]

See also here

[Joshua Benjamin Miller]

Just to echo Nick Cox's observation, "gen double" is absolutely necessary if you want to preserve milleseconds using a string in this format. Learned this the hard way.

Example:

generate datetime = clock(datetime_str,"YMDhms")
generate double datetime_double = clock(datetime_str,"YMDhms")
format datetime datetime_double %13.0f

generates:

datetime_str	datetime	datetime_double
2010-12-29 15:45:30.82	1609256796160	1609256730820
2010-12-29 15:45:33.90	1609256796160	1609256733900

ps. there doesn't seem to be a way to use %tc format to display the milleseconds, and what you see displayed is a rounded down to the nearest second.

. display %tc clock("2010-12-29 15:45:30.82","YMDhms")
29dec2010 15:45:30

[Clyde Schechter]

Yes, my error in #2. -double- is absolutely necessary there.

Joshua Benjamin Miller

there doesn't seem to be a way to use %tc format to display the milleseconds, and what you see displayed is a rounded down to the nearest second.

That's not true. The unelaborated %tc format shows only the century, year, month (as 3 characters lower case), day, hour, minutes, and rounded seconds. But you can specify exactly what you want:

. display %tcCCYY-NN-DD_HH:MM:SS.ss clock("2010-12-29 15:45:30.82","YMDhms")
2010-12-29 15:45:30.82