topic Unix Timestamp to Real Date & time in Discussions

Unix Timestamp to Real Date & time

d_barnett — Wed, 21 Oct 2015 11:35:12 GMT

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

Re: Unix Timestamp to Real Date & time

Craige_Hales — Wed, 21 Oct 2015 13:08:26 GMT

try 1jan1970 without any quotation marks.

format( 1jan1970+1445432452, "mdy" )

"10/21/2015"

(That's today, for future reference. I got Epoch Converter - Unix Timestamp Converter to give me the Unix time for today: 1445432452 )

adding 1jan1970 works because the Unix time for 1jan1970 is zero. And because JMP and Unix are both counting in seconds.

Be careful, I've seen some discussion about when the Unix epoch really began: Unix time - Wikipedia, the free encyclopedia says

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 2³² 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

Jeff_Perkinson — Tue, 21 Jun 2022 14:57:50 GMT

The UNIX date is the number of seconds since midnight, January 1, 1970.

JMP stores dates as the number of seconds since midnight, January 1, 1904.

So, we just need to add the JMP date value for January 1, 1970 to the Unix date value. You can use the Date DMY() function to do this:

-Jeff

Re: Unix Timestamp to Real Date & time

Bill_Worley — Wed, 19 Oct 2016 00:59:55 GMT

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

Bill

Re: Unix Timestamp to Real Date & time

Bill_Worley — Wed, 19 Oct 2016 01:00:04 GMT

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.

Re: Unix Timestamp to Real Date & time

d_barnett — Thu, 22 Oct 2015 07:03:45 GMT

Jeff,

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.

thanks for your help

David

Re: Unix Timestamp to Real Date & time

d_barnett — Thu, 22 Oct 2015 07:32:13 GMT

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

Re: Unix Timestamp to Real Date & time

dpeyer — Wed, 10 Feb 2021 18:19:11 GMT

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.

@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.

Re: Unix Timestamp to Real Date & time

txnelson — Wed, 10 Feb 2021 18:23:46 GMT

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

Re: Unix Timestamp to Real Date & time

dpeyer — Tue, 16 Feb 2021 23:09:07 GMT

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

Re: Unix Timestamp to Real Date & time

NIE-MJ — Tue, 07 Jun 2022 17:19:42 GMT

My question is just the opposite, how do I convert real time to Unix timestamps?

Re: Unix Timestamp to Real Date & time

jthi — Tue, 07 Jun 2022 18:15:14 GMT

I think it should be enough if you remove unix zero time from JMP's datetime as both are in seconds. Hopefully you don't have to touch time zones (The Problem with Time & Timezones - Computerphile (youtube.com))

Names Default To Here(1);

unix_zero = Date MDY(1,1,1970);
date_now = Today();
unix_time = date_now - unix_zero; 
// In Hours() might be helpful if you need timezone conversion

// just to verify that time is correct I'm using Unix Timestamp converter REST API from https://unixtime.co.za/ 
aa = Associative Array();
aa["timestamp"] = unix_time;
request = New HTTP Request(
	URL("https://showcase.api.linx.twenty57.net/UnixTime/fromunix"),
	Method("GET"),
	Query String(aa)
);
data = request << send;
Show(As Date(date_now));
show(data);

Re: Unix Timestamp to Real Date & time

UersK — Wed, 08 Feb 2023 05:31:38 GMT

Thanks!

Re: Unix Timestamp to Real Date & time

hogi — Fri, 07 Jul 2023 17:12:23 GMT

@jthi wrote:
... Hopefully you don't have to touch time zones

and if I have to - can Jmp help?

Re: Unix Timestamp to Real Date & time

Jeff_Perkinson — Fri, 07 Jul 2023 17:20:32 GMT

There's nothing built-in but usually it's just a matter of adding/subtracting hours (in seconds) from a time.

What help do you need?

Re: Unix Timestamp to Real Date & time

hogi — Sun, 09 Jul 2023 11:50:43 GMT

Hi @Jeff_Perkinson,

something like.

The video in @jthi 's post shows higher orders of he issue, but even for a "simple" conversion from UTC to German summer/winter time a JSL function could save a lot of time.