Help interpreting/converting odd date format

Question

I have data pulled from a database and stored in Stata .dta files. But when I read it into R using the foreign package, I get a date format unlike any I've seen. All of the other dates are "%m/%d/%Y" and import correctly.

I have searched the database's documentation, but there's no explanation for the odd date format for "DealActiveDate". The "facilitystartdate" date should be close to the "DealActiveDate", but not necessarily the same. Here are a few rows of these two columns.

facilitystartdate DealActiveDate
1         09/12/1987   874022400000
2         09/12/1987   874022400000
3         09/12/1987   874022400000
4         09/01/1987   873072000000
5         09/08/1987   873676800000
6         10/01/1987   875664000000
7         08/01/1987   870393600000
8         08/01/1987   870393600000
9         10/01/1987   875664000000
10        09/01/1987   873072000000

Please let me know if you have any idea how to convert "DealActiveDate" to a more conventional date. Thanks! (I'm not sure SO is the best venue, but I couln't think of any other options!)

Plumb

Perhaps it would be easier to find out why the database / Stata file treats/stores the two dates differently rather than trying to reverse engineer from the R side? – Mauk

@Gavin -- I don't think it's a foreign problem; it's likely just an odd date format. I got the data from a Stata user who thinks there's something wrong with the "DealActiveDate" column, but never chased down the problem (he just never uses it). I searched the online version of the database, but had no luck. I am hopeful that some wizard here would know the odd date or Stata format. – Plumb

I think you misunderstood - I meant to try to solve the problem with the way it is stored in the original files, not why foreign doesn't recognise it. Seems like it might be milliseconds since an epoch (from @Joshua's answer) so we can ignore my suggestion now :-) – Mauk

@Gavin -- I did misunderstand. Sorry. I was 99.9% sure that the numbers were coming through correctly and this was an x intervals from date y problem. But I was fixated on dropping the first two and last five digits and counting days. I just knew that R tag is where the smart kids hang out. :) – Plumb

Answer 1

Looks like milliseconds since 1960-01-01:

as.POSIXct(874022400000/1000, origin="1960-01-01")
# [1] "1987-09-12 01:00:00 CDT"

Dressy

Thanks! I was fixated on dropping the 87 and counting days, but got nowhere. – Plumb

@richardh: You're welcome. I noticed the time difference between the dates was 1000x the number of days ((874022400000-873072000000)/(60*60*24) = 11000), which implied milliseconds. .POSIXct(874022400) was off by 10 years, which implied an earlier epoch. – Dressy