Converting string to date/time variables fails somehow

[Martin Gunsenheimer-Welsch]

Hello Statalist community, I am struggeling with converting a string containing date and time information inti SIF/HRF. It seems that the seconds aren't converted corrctly. Maybe I am mistaken and use the wrong commands to perform my transformation or maybe the error lies in the wrong display format. As far as I know, I tried all i cound trying to get the right results but actually I failed.

Please can you help me to get the transformation right?

Original string information:

. describe treatmenttime

              storage   display    value
variable name   type    format     label      variable label
-----------------------------------------------------------------------------
treatmenttime   str19   %19s                  Treatment Time


. list treatmenttime in 1

     +---------------------+
     |       treatmenttime |
     |---------------------|
  1. | 2018-10-10 17:27:14 |
     +---------------------+

Transformation attempt string --> SIF:

gen treatmenttime2= Clock(treatmenttime, "YMDhms")

. describe treatmenttime2

              storage   display    value
variable name   type    format     label      variable label
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
treatmenttime2  float   %9.0g   

. list treatmenttime2 in 1

     +----------+
     | treatm~2 |
     |----------|
  1. | 1.85e+12 |
     +----------+


. format treatmenttime2 %tC

. list treatmenttime2 in 1

     +--------------------+
     |     treatmenttime2 |
     |--------------------|
  1. | 10oct2018 17:27:21 |
     +--------------------+

So you see: I started with:

2018-10-10 17:27:14

and got

10oct2018 17:27:21

after the transformation.

Furthermore, the actual difference between starting time and transformed time varies depending on the very content of the string between few seconds and minutes which is even more frustrating.

[Nick Cox]

You need clock() not Clock(). Different functions!

. di %tc clock("2018-10-10 17:27:14", "YMD hms")
10oct2018 17:27:14

[Martin Gunsenheimer-Welsch]

Hello Nick,

thank you very much for the quick answer.
That sounded promising at first, but:

My result after the following attempt:

. gen treatmenttime2= clock(treatmenttime, "YMDhms")
. format treatmenttime2 %tc

is:

. list treatmenttime treatmenttime2 in 1

     +------------------------------------------+
     |       treatmenttime       treatmenttime2 |
     |------------------------------------------|
  1. | 2018-10-10 17:27:14   10oct2018 17:27:48 |
     +------------------------------------------+

So the difference not only seems to remain, but getting bigger
I think the increase is due to the additional leap seconds, that I tried to avoid using "Clock" instead of "clock".

[Joseph Coveney]

Use double-precision floating point for the SIF.

set obs 1
generate str trt_dt = "2018-10-10 17:27:14"
generate double treatment_dt = clock(trt_dt, "YMDhms")
format treatment_dt %tcCCYY-NN-DD_HH:MM:SS
list, noobs

[Nick Cox]

You need to specify double as well. See help datetime, where this advice is repeated often.

I can't advise on whether your data are really with leap seconds or without them. But #2 shows that your string example is matched by clock() output in the single instance you reported as problematic.

Note that the calculation using display automatically uses double internally. For generating new variables, you have to spell that out, unless you've set the default storage type to double.

[Martin Gunsenheimer-Welsch]

Nick!
Thats awesome. I simply forgot to specify "double".
Thats the exact solution I was hoping for.

I'm sorry, that it has been so trivial. But sometimes it just is.

I feel very relieved now. Thank you Nick.
And thank you, too, Joseph!