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

[#1]

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?

[#2 · Accepted solution]

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" );

[#3]

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 

@Mark_Bailey 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:

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

 

[#4]

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(".

• a wikipedia map of time zones
• blog on UTC
• video for another blog, watch the Monday/Tuesday annotation at the bottom edge.

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.

[#5]

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