Converting Unix time to human readable time format

[Vincent KH Lee]

Hi, I am having an issue converting what is assumably Unix time stamp to human readable time format.

* Example generated by -dataex-. For more info, type help dataex

clear
input long ORDER_TIME
205151
134137
145136
204606
211504
210942
212004
204921
205859
212712
202832
163404
212028
205544
142451
143439
155042
154539
180347
180922
180004
121746
142007
152555
162113
153127
171127
151331
161443
113412
131426
123955
123619
194452
150749
213547
131254
122751
125313
143205
171811
143902
140321
161717
140404
140210
140659
140311
213227
213112
212630
152042
153656
152006
152628
142653
140852
202313
201223
200808
144346
143442
145750
145629
151253
150323
153620
160810
154556
150338
152300
164043
191606
191427
174344
175629
180221
175809
135401
161011
160827
162033
163723
160521
 94127
165558
174701
193920
102503
182007
190233
165509
185454
184617
170928
140620
180016
180752
152800
164021
end

When I convert them using:

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

format statatime %tC

list ORDER_TIME statatime, noobs

the time window of the variable is in 1970s, where the actual time window is between 2019-2020.

I have searched through other threads regarding similar issues, and tried converting the ORDER_TIME variable to float,

recast float ORDER_TIME

but the results are way off from 2019-2020 time window.

So, at this point I am assuming that this might not be a Unix time stamp, or I have missed out something important converting them.

Summarized ORDER_TIME has minimum value of 2, which I assume is not a valid Unix timestamp.

I'm starting to think that this might be some sort of time indicator of a day, but not sure what exactly it is indicating.

Can anyone provide advice on the matter?

Thanks in advance.

[Nick Cox]

I can't make much sense of these. If 94127 can be thought of as 094127 then all your example values can be read as hh:mm:ss converted to integers hhmmss. But that does not match a reported maximum way above 235959 or even 240000.

[Vincent KH Lee]

I see what you mean, as 24:00:00 is the maximum number hh:mm:ss format can represent.

That is why I initially assumed that this particular time format is in Unix time format.

Moment Unix time converting using the above code failed, I tried ORDER_TIME / (60*60*1000), to see if it is in milliseconds by any chance, but as you can probably imagine from the maximum number above 240000, it would return maximum of 355 something, which is absolutely obsolete.

I might just going to have to ask the provider of this dataset to see what it actually means...

Thanks for the advice!

[Vincent KH Lee]

For possible statalists who would encounter similar issue,

Nick's initial apporach was right while converting the data through ODBC connection, numbers that were in string format (ORDER_TM) converted to numeric, resulting in loss of leading 0s for some of the numbers such as 094127 --> 94127.

Had to contact the data provider regarding the reported maximum above 235959, or 240000, which accounted for extremely small portion of the data, and found out that some of the orders that were cancelled afterwards were marked with awkward numbers. (Which I think is not the best way to keep your database in any sense...)

So I dropped the cancelled observations from the data set and added in those deleted leading 0s, and everything was pretty straight forward after all.

Just in case here is a sample code to deal with that leading 0s and converting it to time:

generate str TIME_TEST = substr("0" * 6 + ORDER_TM, -6, .)

order TIME_TEST, after(ORDER_TM)


*drop TIME_TEST


gen ORDER_TIME = clock(TIME_TEST, "hms")

order ORDER_TIME, after(ORDER_TM)
format ORDER_TIME %tcHH:MM

list ORDER_TM ORDER_TIME, noobs

Hope this helps!

[Nick Cox]

Thanks for the story!

Here is another way to do it.

clear
input long ORDER_TIME
 94127
165558
174701
193920
102503
182007
190233
165509
185454
184617
170928
140620
180016
180752
152800
164021
end

gen Time = clock(cond(ORDER_TIME < 1e5, "0" + strofreal(ORDER_TIME), strofreal(ORDER_TIME)), "hms") 

format Time %tcHH:MM:SS 

list