Converting from Unix to Stata time

[robertekraut]

I am trying to convert from unix/epoch time to stata time. I've read a number of instructions, including the manual (http://www.stata.com/manuals13/ddatetime.pdf) but I must be doing something wrong when I follow them. I am using Stata IC 14.2 on a Windows 10 machine.

Here is what I do:

generate double statatime = unixtime + mdyhms(1,1,1970,0,0,0)
format statatime %tC

The input 1441422894 should generate 05Sep2015 03:14:54 but instead generates 17jan1970 16:23:42

Do you see my error?

Robert Kraut
Carnegie Mellon University

[William Lisowski]

Welcome to Statalist.

Your UNIX epoch is in seconds after 1/1/1970, Stata measures time in milliseconds.

. generate double statatime = unixtime*1000 + mdyhms(1,1,1970,0,0,0)

. format statatime %tC

. list

     +---------------------------------+
     |   unixtime            statatime |
     |---------------------------------|
  1. |          0   01jan1970 00:00:00 |
  2. | 1441422848   05sep2015 03:13:42 |
     +---------------------------------+

It is also possible you want %tc rather than %tC.

. generate double statatime = unixtime*1000 + mdyhms(1,1,1970,0,0,0)

. format statatime %tc

. list

     +-------------------------------+
     | unixtime            statatime |
     |-------------------------------|
  1. |        0   01jan1970 00:00:00 |
  2. | 1.44e+09   05sep2015 03:14:08 |
     +-------------------------------+

I cannot explain why neither gets the answer you presented.

[robertekraut]

Thank you very much. You've saved me hours of searching and lots of frustration.

[Robert Picard]

If you are wondering why the small time difference, William defined his variable unixtime as a float.

. dis %tC float(1441422894)*1000 + mdyhms(1,1,1970,0,0,0)
05sep2015 03:13:42

If it is stored as a double, the results would be

. dis %tC 1441422894*1000 + mdyhms(1,1,1970,0,0,0)
05sep2015 03:14:28

The above is what Stata calls leap second-adjusted values. To get what you expected, use the %tc format.

. dis %tc 1441422894*1000 + mdyhms(1,1,1970,0,0,0)
05sep2015 03:14:54

[Morgan DeBusk-Lane]

Along these same lines, I am attempting to merge a couple data sets by time. In doing so, I am converting the time stamps to a universal time. One of my time stamps is presented as a UTC time stamp. 1491575984

I have used:

. generate double test = v1*1000 + mdyhms(1,1,1970,0,0,0)

which generates: 1807195136000

reformatted using:

.format %tc

generates: 07apr2017 14:38:56

However, this does not align with what I know the time is, which is 07apr2017 14:39:44. I have also tried %tC to account for the leap seconds.

What am I missing? Thanks for any help!

[William Lisowski]

I just came across this thread, with post #5 new since the last time I saw it.

The problem described in post #5 occurs because the time stamp presented in the first paragraph is, apparently, what was stored in some external file, not what was stored in the Stata variable v1. When the data in the file was loaded into Stata (we don't know by what means) it appears that the variable v1 containing the time stamp was created as float, rather than double, resulting in a loss of precision in storing it. If the time is stored in a double, things work as expected. The code below demonstrates this.

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

. generate  float v1 = 1491575984

. generate double v2 = 1491575984

. format v1 v2 %12.0f

. generate double t1 = v1*1000 + mdyhms(1,1,1970,0,0,0)

. generate double t2 = v2*1000 + mdyhms(1,1,1970,0,0,0)

. format t1 t2 %tc

. list

     +-------------------------------------------------------------------+
     |         v1           v2                   t1                   t2 |
     |-------------------------------------------------------------------|
  1. | 1491575936   1491575984   07apr2017 14:38:56   07apr2017 14:39:44 |
     +-------------------------------------------------------------------+