cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
d_barnett
Level IV

Unix Timestamp to Real Date & time

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.

Regards


David

15 REPLIES 15

Re: Unix Timestamp to Real Date & time

Just an add to Jeff's post.  When I used his formula I got back the number of seconds.  I added the MDYHMS format to the formula to get date and time

10241_pastedImage_0.png

Bill

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.

10242_pastedImage_0.png

d_barnett
Level IV

Re: Unix Timestamp to Real Date & time

Jeff/ Bill,

I noticed that it was in seconds as well but I used the formatting available to turn it into 'proper' time.

Thanks again.

David

dpeyer
Level II

Re: Unix Timestamp to Real Date & time

Hi Bill,

 

I know this post is from forever ago, but I am trying to do the same thing and failing. I have the formula and all that input into the new column, but I cannot find the option to select m/d/y h:m:s for the date convention. I am only given the options to select the many variants of m/d/y. Is there something that I am missing to get the time included in the date output? Thanks.

 

dpeyer_0-1612981121924.png

dpeyer_1-1612981139375.png

 

 


 

@Bill_Worley wrote:

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.

 

 

10242_pastedImage_0.png


 

txnelson
Super User

Re: Unix Timestamp to Real Date & time

You have clicked on "Date" for the selection of the format to choose from. Select "Time" and it will give you all of the Date/Time formats
Jim
dpeyer
Level II

Re: Unix Timestamp to Real Date & time

Yup....knew I was missing something lol. Thanks!