Monday, May 14, 2012

Converting UNIX <-> Human Readable Timestamps in R and Excel.

For the last two days, I have been trapped in a tricky problem (for a beginner like me :p); converting UNIX timestamp to human readable timestamp.

To solve the problem, firstly I went to this site .  I entered a sample UNIX timestamp record which is “1333456742556”.  The site successfully converted the data into “GMT: Tue, 03 Apr 2012 12:39:02 GMT”.  Hmm quite reasonable..

Then to convert the whole dataset I tried the code recommended by the site.
for (R) <- as.POSIXct(theUNIXtimestamp, origin="1970-01-01")
for (Excel) <- (theUNIXtimestamp / 86400) + 25569
*note you have to adjust the time with your local timezone, (see the URLs at the end of this post).

When I applied the code, one funny thing happened.  The code convert “1333456742556” into "4225-07-25 03:42:36 CEST".  How could it be?  What’s wrong?

After sometime, I find the answer.  The key is to use the first 10 digits of the UNIX code and remove the remaining digits.  Note that the original UNIX timestamp, “1333456742556” consists of 13 digits.  Thus, I extracted the first 10 digits (“1333456742”), … re-used the code, and … abracadabra.. “"2012-04-03 13:39:02 CEST".. the correct timestamp appear..  HURRAY..  (beginner’s excitement hehehe..)

Credits for these URLs ( those lovely people who spend a bit of their time to share their knowledge.. :) )...
(for General Issue)
(for Excel) 
(for R)