Timestamp ISO 8601 conversion with fraction of Seconds.

[Devin]

I need to covert an ISO 8601 timestamp to FM timestamp. but the supplied time stamp they give  the have added fraction of sec.   "2014-10-20T16:17:27.300-05:00"

What would be the best method of tackeling the date issue?

Her is a calculation that I've used in the past for ISO 8601.

Let ([
                        yyyy = Left ( Order_DATE ; 4 ) ; 
                        mm = Middle (Order_DATE ; 6 ; 2 ) ; 
                        dd = Middle ( Order_DATE ; 9 ;2 ) ; 
                        hh = Middle ( Order_DATE ; 12 ; 2 );
                        nn = Middle ( Order_DATE ; 15 ; 2 ) ; 
                        ss = Middle ( Order_DATE ; 18 ; 2 );
                        fs1 =  GetAsTime ( Right ( Order_DATE ; Length ( Order_DATE) - 19 )) 
                        ];GetAsTimestamp (  
                                (Substitute ( "mm/dd/yyyy hh:nn:ss" ;
                                ["yyyy" ; yyyy ];
                                ["mm" ; mm];
                                ["dd" ; dd];
                                ["hh";hh];
                                ["nn";nn];
                                ["ss";ss]
                                )  ) )- fs1)

[comment]

First, you should use the Timestamp() and Date() functions here, instead of GetAsTimestamp(), so that the calculation does not depend on the date format in use.

Other than that, you could do simply:

Let ( [
y = Left ( Order_DATE ; 4 ) ; 
m = Middle ( Order_DATE ; 6 ; 2 ) ; 
d = Middle ( Order_DATE ; 9 ; 2 ) ; 
t = Middle ( Order_DATE ; 12 ; 12 )
] ;
Timestamp ( Date ( m ; d ; y ) ; t )
)

There is no problem with the fractions of second here - Filemaker knows how to handle them. However, you should ask yourself what to do with the timezone offset. Are you sure you want to ignore it?

[Devin]

However, you should ask yourself what to do with the timezone offset. Are you sure you want to ignore it?

I do not want to ignore it, this is why I was using fs1 in my example.

Let ( [
y = Left ( Order_DATE ; 4 ) ; 
m = Middle ( Order_DATE ; 6 ; 2 ) ; 
d = Middle ( Order_DATE ; 9 ; 2 ) ; 
t = Middle ( Order_DATE ; 12 ; 12 )
] ;
Timestamp ( Date ( m ; d ; y ) ; t )
)

This worked.. just need the offset now.

Edited September 1, 2015 by Devin

[comment]

I do not want to ignore it

Ah, okay then. If you want to convert it to UTC, you can do:

Let ( [
y = Left ( Order_DATE ; 4 ) ; 
m = Middle (Order_DATE ; 6 ; 2 ) ; 
d = Middle ( Order_DATE ; 9 ;2 ) ; 
t = Middle ( Order_DATE ; 12 ; 12 ) ;
offset = Middle ( Order_DATE ; 24 ; 6 ) ; 
UTC = GetAsTime ( t )- GetAsTime ( Substitute ( offset ; "+" ; "" ) ) 
] ;
Timestamp ( Date ( m ; d ; y ) ; UTC )
)

This is yet another reason to use the Timestamp() function, since it will automatically adjust the date for you when the time is negative or exceeds 24 hours.

Although, if this is in the context of importing XML, my preference would be to do it all in the XSLT stylesheet, rather than burdening the solution with logic that serves no internal purpose.

 

Edited September 1, 2015 by comment

[Devin]

Ah, okay then. If you want to convert it to UTC, you can do:

Let ( [
y = Left ( Order_DATE ; 4 ) ; 
m = Middle (Order_DATE ; 6 ; 2 ) ; 
d = Middle ( Order_DATE ; 9 ;2 ) ; 
t = Middle ( Order_DATE ; 12 ; 12 ) ;
offset = Middle ( Order_DATE ; 24 ; 6 ) ; 
UTC = GetAsTime ( t )- GetAsTime ( Substitute ( offset ; "+" ; "" ) ) 
] ;
Timestamp ( Date ( m ; d ; y ) ; UTC )
)

Something I did not notice earlier is Using the formula for "2014-10-20T12:30:49.300-05:00" is returning "10/20/2014 5:30:49.3 PM" 

It's still showing .3 for fraction of second. 

Although, if this is in the context of importing XML, my preference would be to do it all in the XSLT stylesheet, rather than burdening the solution with logic that serves no internal purpose.

 Is this an interesting concept and makes perfect since as this is for the importing of the XML data. Will need to investigate that option also.

Thanks.

[comment]

It's still showing .3 for fraction of second. 

Well, yes. As I said earlier, Filemaker can handle fractions of a seconds. Your input says the actual time was 0.3 seconds after 12:30:49 and that's what ends up being stored in Filemaker. Why is this a problem and how else would you want it to behave?

[Devin]

Well, yes. As I said earlier, Filemaker can handle fractions of a seconds. Your input says the actual time was 0.3 seconds after 12:30:49 and that's what ends up being stored in Filemaker. Why is this a problem and how else would you want it to behave?

The users is not going to care about seeing a fraction of a second. I would rather it not be displayed.

 

[comment]

The users is not going to care about seeing a fraction of a second. I would rather it not be displayed.

If you don't want it to be displayed, then format the field to display the time as hh:mm:ss (or any other format). Note that when formatting a timestamp field, you must apply some formatting to the date portion too in order for the time format to take effect.

If you don't want it to be stored, change the calculation above so that:

t = Middle ( Order_DATE ; 12 ; 8 ) ;

 

[Devin]

If you don't want it to be displayed, then format the field to display the time as hh:mm:ss (or any other format). Note that when formatting a timestamp field, you must apply some formatting to the date portion too in order for the time format to take effect.

Duh!!! I was so caught up in the Calculation doing the work I totally for got about applying the formatting.

Thanks for your help!