Unix Timestamp to Real Date & time

Oct 21, 2015 4:35 AM(7354 views)

I extract data from a database and part of this is a "start time" for a process. The main problem with this is that it is in UNIX format so therefore returns a 10 digit number, I know that the formula for converting this to actual date and time is as follows

( taken from Excel)

=CELL/(60*60*24)+"1/1/1970"

so changing a column in JMP should be straightforward but I can't seem to do it. The (60*60*24) is easy but what format do I have to set the +"1/1/1970" to to make this work?

I have attached a data table that contains some dates from the past 6ish years as an example. My feeling is that this might have been asked before but I can't see anything that relates to it.

The first edition Unix Programmer's Manual dated 3 November 1971 defines the Unix time as "the time since 00:00:00, 1 January 1971, measured in sixtieths of a second".[5]

The User Manual also commented that "the chronologically-minded user will note that 232 sixtieths of a second is only about 2.5 years". Because of this limited range, the epoch was redefined more than once,[citation needed] before the rate was changed to 1 Hz and the epoch was set to its present value of 1 January 1970 00:00:00 UTC. This yielded a range of about 136 years, though with more than half the range in the past (see discussion of signedness above).

Re: Unix Timestamp to Real Date & time

I had noticed January 4, 1904 popping out a number of times and I thought that it was some formatting or calculation error that I had made, this only added to my confusion why I couldn't get this date conversion to work.

Re: Unix Timestamp to Real Date & time

Another add - talked with Jeff offline and he made a very good point. Don't use the character string "MDYHMS" if you want to do some math with your time stamps. Use Column Info to format your new times as continuous data.