6 Replies Latest reply on Nov 14, 2013 12:53 PM by coh998

    Extracting DATE from pseudo TIMESTAMP field?

    coh998

      Title

      Extracting DATE from pseudo TIMESTAMP field?

      Post

           I have a recurring import of a .csv file that contains a TIMESTAMP in the format 2012-12-27 01:23:33. The Field Type is set to TIMESTAMP, but it does not sort ascending as expected unless I change it to TEXT. I suspect that the "-" characters are preventing Filemaker from recognizing and correctly sorting on the DATE info, since there is no option in either System Formats or DATE Formats that allow the use of that "-" character in a DATE format. I'm getting lots of "?" results or really weird Sort results depending on which Field Type is specified.

           Also, not all records contain timestamp data; many contain the text "NULL".

           At this time, I cannot Script or Calculate my way around the simplest problems, although I am trying to learn. But I'm hoping that someone here will provide a calculation or point me to docs on how to write one that returns "NULL" for those records where it is found and extracts a valid DATE of the format MM/DD/YYYY from the timestamp in such a way that the DATEs can be used in subsequent DATE RANGE calculations. I do not need the TIME data in this calculated field.

           Gratefully,

           --Charles

        • 1. Re: Extracting DATE from pseudo TIMESTAMP field?
          philmodjunk

          2012-12-27 01:23:33

               is almost the correct format for a time stamp. The - shouldn't be a problem but the text "null" and having the year entered before the month/day data won't work for a timestamp field.

               You'll need to use a calculation to convert the text into a true timestamp.

               I used FileMaker Advanced data viewer to test the following expression with the sample text from your post:

               Let ( [tValues = Substitute ( YourTextFieldHere ; " " ; ¶ ) ;
                         T = GetAsTime ( GetValue ( tValues ; 2 ) ) ;
                         dValues = substitute ( GetValue ( tValues ; 1 ) ; "-" ; ¶ ) ;
                         y = GetValue ( dValues ; 1 ) ;
                         m = GetValue ( dValues ; 2 ) ;
                         d = GetValue ( dValues ; 3 ) ;
                         Dt = Date ( m ; d ; y )
                       ] ;

                       TimeStamp ( Dt ; T )
                      ) // Let

               The functions that I used can all be looked up in FileMaker help if they are unfamiliar. You can use the calculation to define a separate calculation field that returns TimeStamp as the result type. You can also define a field of type TimeStamp with this expression as an auto-enter calculation provided that you enable auto-enter options when you import your csv data. You can also use this calculation with Replace Field Contents to update a field of type TimeStamp with the data from your text field after the data has been imported.

               In all of these cases, you can copy and paste the above description directly into the specify calculation dialog box and then replace "YourTextFieldHere" with a reference to the text field into which you are importing this data.

               And a tech note about timestamp fields. The data you see on the screen is not how the value of the field is actually stored. TimeStamp fields store an integer that represents the number of seconds from midnight, 12/31/0000 to the date and time shown in the field. The particular format shown--such as whether you delimit the date with / or - is simply a display setting you select in the inspector for that field. Data entry and import of time stamp data, on the other hand will be based on the file options set for your database file and the locality settings specified for your computer.

          • 2. Re: Extracting DATE from pseudo TIMESTAMP field?
            coh998

                 Thank you, PhilModJunk. I clearly have much to learn! The calculation is mostly beyond my comprehension. ;-)

            Can you modify the formula so that it will strip out the "NULL" text, leaving those records blank while extracting DATE data *only* from those that do contain TIMESTAMP info? And to have all this happen on Import?
                 
                 I need to derive a field with DATE only to use for DATE RANGE queries, without any unresolved "?"'s.

            Thanks again,

            --Charles

            • 3. Re: Extracting DATE from pseudo TIMESTAMP field?
              philmodjunk

                   If ( YourTextFieldHere ≠ "Null" ; Put Calc from my first post here )

                   

              And to have all this happen on Import?

              Define a field of type TimeStamp and keep your text field. Set up this calculation as an auto-entered calculation. (Double click the field definition for the timestamp field in Manage | Database | Fields to open the field options dialog.) When you import records there's a small dialog box that pops up just before the actual import starts up that has a check box that enables "auto-enter options". make sure to select this option when importing your data.

              • 4. Re: Extracting DATE from pseudo TIMESTAMP field?
                coh998

                Thank you once again, PhilModJunk! This works very nicely, though the result still contains the extraneous TIME data when all I really need is the DATE. But I can live with that! I really appreciate your help and guidance and I shall spend some time trying to understand how and *why* it works. ;-)
                     
                     Many thanks,

                --Charles 

                • 5. Re: Extracting DATE from pseudo TIMESTAMP field?
                  philmodjunk

                       If all you need is the date, use a date field instead of a timestamp field and leave out the parts of the calculation that extract the time.

                       Let ( [tValues = Substitute ( YourTextFieldHere ; " " ; ¶ ) ;
                                 dValues = substitute ( GetValue ( tValues ; 1 ) ; "-" ; ¶ ) ;
                                 y = GetValue ( dValues ; 1 ) ;
                                 m = GetValue ( dValues ; 2 ) ;
                                 d = GetValue ( dValues ; 3 ) ;
                                 Dt = Date ( m ; d ; y )
                               ] ;

                               Dt
                              ) // Let

                  • 6. Re: Extracting DATE from pseudo TIMESTAMP field?
                    coh998

                         Wow, you make it look easy! ;-)