Combine date and time - time is wrong after conversion

[Riccardo Valboni]

Dear all,

I am dealing with an apparently easy task. I am trying to combine in one column of date-time, two separate columns containing respectively a date and a time. The date is stored in date format (counting the number of days from January 1st, 1960); the time is stored as SIF datetime/c format. To combine them, I created the datetime column. But the result is quite surprising because the time is always wrong by a couple of seconds. Here is the code I used to obtain the results that are in the data set extract enclosed.

gen time=Clock(activation_time, "hms")
format time %tcHH:MM:SS
gen datetime=date*24*60*60*1000+time
format datetime %tCDD/NN/CCYY_HH:MM:SS

I tried to look at previous posts but no one seems to deal with the problem...

Many thanks in advance for your help.
Riccardo

[Clyde Schechter]

This is a precision problem. Your variables time and datetime need to be double.

[William Lisowski]

All datetime values need to be storage type double, as noted in the footnote under the table of HRF-to-SIF conversion functions in help datetime, and in all the subsequent examples of using clock() to create datetime values.

[Riccardo Valboni]

Oh I see, thanks a lot for pointing that out! I had read the datetime chapter but for some reason I didn't get that. Many thanks again to both.

[Nick Cox]

The need to use double here has often been flagged on Statalist.

There is another problem with your syntax. You used the Clock() function but then assigned %tc format. The difference between clock() and Clock() functions and %tc and %tC format is also documented at help dates (or the equivalent help in any version since Stata 11).

[Riccardo Valboni]

You are right Nick regarding the incoherent use of Clock()and then of %tc (instead of %tC) - I knew this but I didn't realize the mistake. Let me post here the right code for those who will read this post afterwards

gen double time=clock(activation_time, "hms")
format time %tcHH:MM:SS
gen double datetime=date*24*60*60*1000+time
format datetime %tcDD/NN/CCYY_HH:MM:SS

This code solves the precision problem explained above.