1 2 Previous Next 18 Replies Latest reply on Feb 22, 2016 7:28 AM by disabled_morkus

    Timestamp difference?

      Trying to simply compute the difference between two timestamps using a calculation similar to the one below.

       

      This should be simple calculation since in most environments a Timestamp is just a "Time" value you can then do a simple calculation on. FileMaker is probably just fine in what it's doing, but I don't recall this calculation being difficult in the past.

       

      Yet, the approach below doesn't seem to work regardless of what I try.

       

      ( ExecuteSQL("select max(<TS_DATE>) from <TABLE>" ; ""; "") - ExecuteSQL("select min(<TS_DATE>) from <TABLE>" ; ""; "") )  / (tried different divisors)

       

      I've seen some CFs that have recursive solutions (???), but it's hard to believe you would need that to subtract two TimeStamps, it should only take a formula, right?

       

      I've tried dividing by 84600 and multiples of that, but the result is not even close.

       

      Clearly missing something here....

       

      Thanks in advance,

       

      - m

        • 1. Re: Timestamp difference?
          emory_brown

          What result are you getting? Not sure why there is a need to use SQL here, however you can simply subtract a timestamp from another timestamp. FileMaker will return the result as a time. You can then use GetAsNumber () to calculate the seconds.

           

          Could you post an example solution for what you are trying to do?

           

          -Emory Brown

          -www.dbservices.com

          • 2. Re: Timestamp difference?
            hbrendel

            Timestamp1 - Timestamp2 gives you the difference in seconds. Use other functions to convert it to dd/hh/mm/ss.

            • 3. Re: Timestamp difference?
              nicolai

              You expect ExecuteSQL to return you a date, but it is a string. You are subtracting one string from another. Try to add GetAsTimestamp() to both.

              • 4. Re: Timestamp difference?

                Sure, here you go...

                 

                I need to get the max and min Timestamps from a table so I can calculate the difference between the first and last Timestamps for an average calculation.

                 

                Although the Timestamp values appear in FileMaker table view in traditional FileMaker format: "mm/dd/yyyy ....", the ExecuteSQL returns two values like this:

                 

                2015-10-20 15:19:57

                 

                2016-02-20 13:50:17

                 

                ---

                 

                The value it returns, from original posting above, is: 9199983060

                 

                Thinking this is possibly  in milliseconds, and trying to get Days, I divided by 1,000 (ms -> seconds), then divided by 3600 (seconds per hour), and finally divided then by 24 (24 hours in a day). This gives me an answer of 106.48128541666667.  Not right.

                 

                Yet, the simpler calculation...

                 

                GetAsDate("2/20/2016") - GetAsDate("10/20/2015")

                 

                gives me 123, which looks right.

                 

                ----

                 

                Therefore, I have no idea what the units are in "9199983060" here since this is a huge number and how to convert it to "days".

                 

                My goal is to calculate the number of days between these two Timestamps.

                 

                Suggestions?

                 

                Thanks again.

                 

                -- m

                • 5. Re: Timestamp difference?
                  beverly

                  a couple of SQL functions:

                   

                  STRVAL(\"date\")

                  COALESCE(\"date\",'') // two single quotes on the end

                   

                  "returns mm/dd/yyyy h:mm:ss A instead of yyyy-mm-dd hh:mm:ss formatting"

                  - quoting myself. LOL

                   

                  However, there may be a problem with mixing the Aggregate within the other functions:

                   

                  STRVAL(MAX(\"date\"))

                   

                  may or may not work - I haven't tested.

                   

                  But I don't think that's what you are asking here.

                   

                  https://www.filemaker.com/help/14/fmp/en/html/create_db.8.19.html

                  A timestamp field can store date values from January 1, 0001 to December 31, 4000. Timestamps are measured in seconds.

                  &

                  FileMaker Pro stores timestamps as the number of seconds since midnight on January 1, 0001

                   

                  That's what makes it possible to perform addition and subtraction (on time and date and timestamp fields).

                   

                  HTH,

                  beverly

                  • 6. Re: Timestamp difference?
                    nicolai

                    Well, you did not read my message, You are subtracting two strings! For FileMaker it looks like:

                    "2015-10-20 15:19:57" - "2016-02-20 13:50:17"

                    Try to add this to data viewer and you will get the same result. - 9199983060

                     

                    I was wrong, you can not use GetAsTimeStamp() as the timestamp is in the SQL format. There are CFs which are dealing with this, or you can write your own calc. You will need to convert

                     

                    "2015-10-20 15:19:57" into "10-20-2015 15:19:57"


                    After this try


                    GetAsTimestamp ("10-20-2015 15:19:57")  - GetAsTimestamp("02-20-2015 13:50:17")


                    I am getting 5809:29:40, although I have to convert into UK format first.



                    • 7. Re: Timestamp difference?

                      Thanks. I found all the answers here helpful, but this answer seemed the most helpful over all.

                       

                      Thanks N.

                       

                      -- m

                      • 8. Re: Timestamp difference?

                        I wanted to follow up to say that, unless I'm missing something, it's strange that FM has three representations for a Timestamp:

                         

                        (1) In the DB Table View, it's 11/30/2015 3:09:25 PM

                         

                        (2) From a Select Statement, it's 2015-11-30 3:09:25, and finally...


                        (3) A converted TImestamp so you can use FM functions: 11-30-2015 3:09:25


                        And, to get (3), you have to write a CF? Huh?


                        -----


                        Again, assuming I'm not missing something, it seems that FM really, really needs a unified Timestamp (and date) format. Functions should work universally or there should be simple conversion functions, in FM, for example, to take (2) --> (3) above, if necessary.


                        Using MySQL again as a reference, it's as simple as:

                        select (UNIX_TIMESTAMP(max(<FieldName))) - UNIX_TIMESTAMP(min(<FieldName))/86400  from <tablename>

                         

                        ---

                         

                        Since you're a SQL Server guy, I'm sure it's simple in SQL Server also, or at least unified, right?

                         

                        It's no wonder Timestamps are so confusing in FM.

                         

                        I must have gotten the non-helpful "?" 100 times yesterday.

                         

                        Thanks to your reply posting, I think I now better see the problem I was having.


                        Thanks again, N.

                         

                        - m

                        • 9. Re: Timestamp difference?

                          The Min() and Max() appear to be problematic for FM since they appear to return a text string as Nicolai pointed out..

                           

                          And, it returns a formatted string that FileMaker functions don't seem to understand (GetAsTimestamp, for example) to do math on, and, without reformatting the SQL-returned Timestamp string, "GetAsTimeStamp()" just returns "?"

                           

                          (I love that. So helpful: "?". Yes, I know exactly what the problem is now!!!).

                           

                          Thanks Bev,

                           

                          - m

                          • 10. Re: Timestamp difference?
                            nicolai
                            it's simple in SQL Server

                             

                            In SQL Server timestamp does not mean timestamp (it is rowversion)! The actual timestamp as data type is datetime and it looks like this:

                             

                            "2007-04-30 13:10:02.0474381" , which is the same as the 1 in your list

                             

                            "11-30-2015 3:09:05" is the same as  "11/30/2015 3:09:05". I used Japanese (I think) format as it is easier to convert to.


                            GetAsTimestamp will work on both

                             

                            You have a point, FMP could be more consistent or might have some conversion function, but some good CFs are available so it did not bother me much.



                            • 11. Re: Timestamp difference?
                              beverly

                              Yes! this is a long-time request to allow, manage, convert between these kinds of dates (yyyy-mm-dd) and FMP dates (mm/dd/yyyy or dd/mm/yyyy).

                               

                              In the meantime, we convert with Custom Function (or create our own):

                               

                              I don't know if this method would allow you to use the MAX() or MIN() in eSQL:

                               

                              SELECT ''||MAX(\"date\") // concatenate the two single quotes before the aggregate

                               

                              This discussion on this forum also mentions this method, but does not test with aggregates:

                               

                              my "guess" is that this won't work either. and you'll get this error:

                              Expressions involving aggregations are not supported.

                               

                              beverly

                              • 12. Re: Timestamp difference?

                                I think the issue is the Max and Min functions keep that from working.

                                 

                                First:

                                SQL: ExecuteSQL("select max(<FIELD_NAME>) from <TABLE_NAME>" ; ""; "") returns:

                                 

                                2016-02-20 13:50:17

                                 

                                in the data viewer, but ...

                                 

                                either...

                                 

                                ExecuteSQL("select GetAsTimeStamp(max(<FIELD_NAME>) ) from <TABLE_NAME>" ; ""; "")

                                 

                                OR

                                 

                                ExecuteSQL("select max(GetAsTimeStamp)<FIELD_NAME>) ) from <TABLE_NAME>" ; ""; "")

                                 

                                return, wait for it ,....

                                 

                                "?"

                                 

                                Ahhhhh 

                                 

                                -- m

                                • 13. Re: Timestamp difference?
                                  beverly

                                  nicolai, the

                                  GetAsTimeStamp("11-30-2015 3:09:05") will work, but

                                  GetAsTimeStamp("2015-11-30 3:09:05") does not.

                                   

                                  <lamenting />

                                  beverly

                                  • 14. Re: Timestamp difference?

                                    Thanks for your reply. Good to know.

                                     

                                    My Workaround (workaround++) was to just do this the FileMaker way (pre-SQL) and create a Cartesian self-join. Then use the min, max, and count functions so I could compute the "$###^!!!#" number.  Works fine now, but the calculated field's formula is overly complex looking (three lines) since FM doesn't seem to return simple millisecond difference (that I know of) and you have to thus parse the colon-delimited string. (2093:22:11, or whatever)

                                     

                                    Thanks Bev.

                                     

                                    - m

                                    1 2 Previous Next