12 Replies Latest reply on Jun 3, 2017 11:12 PM by mz5005

    Splitting TimeStamp field

    mz5005

      I have imported external data with a time-stamp field.

      The data is really a time-stamp and not text (sorting works according to calendar).

      Now I need to split the date and time parts of the time-stamp to a DATE and a TIME field.

      Been trying with converting to text, then with memvars etc, but I do something wrong.

      Timestamps are in European date- and military time format, with milliseconds, like this:

       

      25.02.1992 00:00:00.000

       

      How should I do this?

       

      Feedback highly appreciated!

        • 1. Re: Splitting TimeStamp field
          TomHays

          Make two of these calcs.  One returns theDate.  The other returns theTime.

           

           

          Let([

          theTS = YourExternalTimeStampField;

          theTSList = Substitute(theTS; " "; "¶");

          theDateList = Substitute(GetValue(theTSList; 1); "."; "¶");

          theTimeList = Substitute(GetValue(theTSList; 2); ":"; "¶");

          theDate = Date(GetValue(theDateList;2); GetValue(theDateList; 1); GetValue(theDateList; 3) );

          theTime = Time(GetValue(theTimeList;1); GetValue(theTimeList; 2); GetValue(theTimeList; 3) )

          ];

          theDate

          /* theTime */

          )

           

           

          -Tom

          1 of 1 people found this helpful
          • 2. Re: Splitting TimeStamp field
            Johan Hedman

            Left( YourFIeldTimeStampe ; 10 ) will give you the Date

             

            Middle ( YourFIeldTimeStampe ; 12 ; 8 ) will give you the Time

            1 of 1 people found this helpful
            • 3. Re: Splitting TimeStamp field
              DavidZakary

              Many are offering suggestions. This one just does the date but could be modified to do the time too. Replace the "ts" with your field.

               

              Here's mine...

               

              Let ( [

                ts = "25.02.1992 00:00:00.000";

                gap = Position ( ts; " "; 1; 1 ) ;

                extract = Substitute (Left ( ts; gap - 1 ); "."; "¶");

                the_date = Date (MiddleValues (extract; 2; 1); LeftValues ( extract; 1); RightValues ( extract; 1 )

                )

              ] ;

               

              the_date

               

              )

              • 4. Re: Splitting TimeStamp field
                mz5005

                Hi Tom, Johan,

                 

                big thanks for the suggestions!  I will try them both tomorrow (late night here already).

                • 5. Re: Splitting TimeStamp field
                  philmodjunk

                  GetasTime ( Timestamp )  will return the time.

                  GetAsDate ( Timestamp ) will return the date.

                  • 6. Re: Splitting TimeStamp field
                    mz5005

                    philmodjunk - thanks for your assistance (again  :-)

                     

                    i tried what you said, but somehow it doesn't work.

                    tried to change the format of the DateTime from timestamp to text - didn't help.

                    checked the fmp help and found that dates imported should be in system format - so changed my system

                    date settings (can't change data files) - to no avail.

                     

                     

                    tried also 2 script options.

                    still stuck. can you see what's wrong? (there is only one table for now in the solution).

                    screenshots attached.

                     

                    would be a great help!

                     

                    SS_New_DateTime_format.PNG

                     

                    SS_DateTime1.PNG

                    • 7. Re: Splitting TimeStamp field
                      erolst

                      The string you have is not recognized as a timestamp, so coercing it to a date or time using the built-in functions won't work. Why not use one of the formulas that were suggested?

                       

                      btw, I'd use Replace Field Contents. You can still use the script step, rather than the command, so the calculations are stored and don't have to be re-typed if you need to make a correction.

                       

                      And of course, test this with a small sample set ...

                      • 8. Re: Splitting TimeStamp field
                        philmodjunk

                        Timestamp is a specific Filemaker data type. My suggested functions work as described if the parameters are of that type.

                        • 9. Re: Splitting TimeStamp field
                          mz5005

                          thanks erolst. i tried earlier the formula suggested by johan, but at that time it didn't work.

                          however, when i changed the time-stamp field to text it worked. so thanks johan. (and that's why philmod's formula

                          did't work i think).

                           

                          you are right about REPLACE FIELD CONTENTS - will change it.

                          another probem solved. thank you all.

                          • 10. Re: Splitting TimeStamp field
                            keywords

                            Hmm! The problem with Johan's suggested formula is that it breaks because FM doesn't use leading zeros in the day part of dates, so sometimes the date is 9 characters (3/06/2017) and sometimes 10 (13/06/2017). With this hard-coded method Johan's date calc will include a trailing zero if the date is only 9 characters, and the time calc will miss the first character in the time string, since the starting point should be 11, not 12.

                            Using FM's built in functions, as suggested by Phil, resolves all this. Try the following calcs with a few scenarios and you'll soon see—

                            Let (

                            [

                               theTS = "4/06/2017 8:39:47 AM"

                              ; theDate = GetAsDate ( theTS )

                              ; theTime = GetAsTime ( theTS )

                            ] ;

                            List ( theDate ; theTime )

                            )

                            Will give the result—

                            4/06/2017

                            8:39:47 AM

                             

                            This version, however—

                            Let (

                            [

                               theTS = "4/06/2017 8:39:47 AM"

                              ; theDate = Left ( theTS ; 10 )

                              ; theTime = Middle ( theTS ; 12 ; 8 )

                            ] ;

                            List ( theDate ; theTime )

                            )

                            Will give the result—

                            4/06/2017

                            :39:47 A

                            (Note the space at the end of the date)

                            1 of 1 people found this helpful
                            • 11. Re: Splitting TimeStamp field
                              mz5005

                              Keywords: Again learned something new - will try it out. Thanks!

                              • 12. Re: Splitting TimeStamp field
                                mz5005

                                thanks david too for your feedback - missed it earlier.