Today I was working on a one-time HIVE report which due to the amount of data that is in each table, took a significant amount of time to run. After running though, I had the data that I wanted except for the fact that I neglected to convert the UNIX timestamp into a text version of the date. Instead of re-running the query again, I decided to use a formula in order to convert the UNIX time into a meaningful string.
- First I used
=DATEVALUE("01/01/1970")
in order to get the number representation of the first day in UNIX time (January 1, 1970). - Next I determined that UNIX time must be divided by the amount of seconds in a day (86400) in order to be in accordance with the way Excel converts dates to numbers.
- Finally I ended up with the following formula in order to convert the UNIX time stored in cell A2 into a text representation:
=TEXT(A2/86400+25569,"YYYY-MM-DD HH:MM:SS")
It is important that there is no built-in way to determine the timezone offset of the current users computer with a function. On the other hand, if you already know the timezone offset, you could include this in your calculations. For instance, if the data was captured in a timezone of -0400, you could use the following formula: =TEXT([UNIX_TIME_CELL]/86400+25569-4/24,"YYYY-MM-DD HH:MM:SS")
.