Choose Language Hide Translation Bar
Highlighted
MikeDou
Level I

Time format 2018-01-01 00:00:00+08:00 not recognized in JMP during importing

per ISO_8601, 2018-01-01 00:00:00+08:00 is a standard time format.

 

Can I make JMP to accept this format so I don't have to modify the format at each of the import tasks?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Time format 2018-01-01 00:00:00+08:00 not recognized in JMP during importing

I don't see a format that works but you can break it down into two pieces: date and time. Add the pieces together. Here is a formula that could be used in a second column to store the numeric data that represents the date and time:

 

time in = "2018-01-01 00:00:00+08:00";  // represents a row in the data table

In Format( Word( 1, time in ), "yyy-mm-dd" ) + In Format( Word( 2, time in, "+" ), "h:m" );
Learn it once, use it forever!

View solution in original post

4 REPLIES 4
Highlighted

Re: Time format 2018-01-01 00:00:00+08:00 not recognized in JMP during importing

I don't see a format that works but you can break it down into two pieces: date and time. Add the pieces together. Here is a formula that could be used in a second column to store the numeric data that represents the date and time:

 

time in = "2018-01-01 00:00:00+08:00";  // represents a row in the data table

In Format( Word( 1, time in ), "yyy-mm-dd" ) + In Format( Word( 2, time in, "+" ), "h:m" );
Learn it once, use it forever!

View solution in original post

Highlighted
Jeff_Perkinson
Community Manager Community Manager

Re: Time format 2018-01-01 00:00:00+08:00 not recognized in JMP during importing

Message has been edited to correct the error that @Craige_Hales, pointed out below. I originally had the signs backwards on the adjustments to GMT. -Jeff 

 

@markbailey is close, but I think the value after the plus (+) isn't the time, it's the offset from GMT.

 

So, if you want to convert everything back to GMT you'll need to take the offset into account:

 

Informat( Word( 1, :ISO 8601 Datetime ), "yyyy-mm-dd" )
+Informat( Word( 1, Word( 2, :ISO 8601 Datetime ), "-+" ), "hh:mm:ss" )
+Match( Contains( :ISO 8601 Datetime, "+" ) > 0,
	0, Informat( Word( 2, Word( 2, :ISO 8601 Datetime ), "-" ), "h:m" ),
	-Informat( Word( 2, :ISO 8601 Datetime, "+" ), "h:m" )
)

It may be easier to read in the Formula Editor where you'll want to use this anyway:

 

2020-02-05_12-35-22.290.png

I'm attaching a data table with an example with the formula column.

2020-02-05_12-36-41.181.png

 

 

-Jeff
Highlighted
Craige_Hales
Staff (Retired)

Re: Time format 2018-01-01 00:00:00+08:00 not recognized in JMP during importing

Hello from -5 land.

I like Jeff's answer (probably needs a sign change), depending...

Those numbers are the time zone. JMP doesn't have much time zone support. Mostly times are

  1. always in the same time zone and the zone can be ignored,
  2. or local time is what is needed: 11:00AM is just before lunch, and the zone can be ignored.

But, time zones make a big difference if you need to know the ordering of events in different zones. In that case, converting back to UTC is the right answer. Then you can add a table variable with a note like "All times in UTC" to reduce confusion.

Also: test any code that manipulates times carefully. I had to study Jeff's formula to understand the match...contains...0... stuff, and it might be correct (or the sign might be reversed).

https://en.wikipedia.org/wiki/ISO_8601 

I'm in zone -5 ("five hours behind UTC"). I'm pretty sure I have to add 5 (or subtract -5) to my time to get UTC. I think the "+match(" should be "-match(".

 

 

Making that last video was how I finally grasped the International Date Line concept; in the map the date line appears on the left and right edges and the +00:00 zone is in the center. Watch how, at noon in London, it is briefly the same day everywhere. The annotation at the bottom switches days when it jumps back to the right side. When it shows Monday/Tuesday at the right side, Tuesday has just begun at midnight at the date line, and it is Monday everywhere else. As midnight sweeps from right-to-left, Monday ends all across the map until it is Tuesday, everywhere.

 

Craige
Highlighted
Jeff_Perkinson
Community Manager Community Manager

Re: Time format 2018-01-01 00:00:00+08:00 not recognized in JMP during importing

Yes, @Craige_Hales, you're right. I did mess up the sign. I've corrected in my post above.

-Jeff
Article Labels

    There are no labels assigned to this post.