9 Replies Latest reply on May 30, 2017 5:58 PM by user19752

    Timestamp Calculations




      Please see these calculations and their results:


      GetAsTimestamp ( "5/29/2017 5:30:13 PM" ) - 63631675814 = -1

      GetAsTimestamp ( "5/29/2017 5:30:13 PM" ) - 63631675813 = 0

      GetAsTimestamp ( "5/29/2017 5:30:13 PM" ) - 63631675812 = "1/1/0001 12:00:01 AM"


      If the result is not positive, this returns the difference in seconds, otherwise it returns the Timestamp of that difference

      Is this a bug?



      By the way, anybody using FileMaker Custom Function:UTCOffset will not get the desired result if they are located East of UTC.

      Change the custom function formula to:

      GetAsNumber ( Get ( CurrentTimestamp ) ) - Floor ( Get ( CurrentTimeUTCMilliseconds ) / 1000 )



        • 1. Re: Timestamp Calculations

          The difference of two timestamps is always the difference in seconds. With a positive result and a timestamp result type, that will produce a date and time since timestamps record their value as the number of seconds from something like midnight 12/31/0000 to the date and time displayed in the field. Thus a difference in seconds IS a timestamp.


          If you change your result type to number, you'll see those seconds. Divide by 3600, the number of seconds in an hour and you'll get the difference as a decimal representing elapsed hours.

          • 2. Re: Timestamp Calculations

            Your calculation is timestamp - number, then it results timestamp (number would be considered as time interval). But there is no 0 nor minus timestamp, so it resulted as number. I'm not sure it should be ? (invalid value).


            If you want difference in seconds, the calculation should be timestamp - timestamp.

            GetAsTimestamp ( "5/29/2017 5:30:13 PM" ) - GetAsTimestamp ( 63631675814 ) = -0:00:01

            1 of 1 people found this helpful
            • 3. Re: Timestamp Calculations

              Thanks for your help

              • 4. Re: Timestamp Calculations

                The desired result of the UTCOffset function is not to consistently be a number or timestamp type, only that adding or subtracting it to a timestamp results in a timestamp. I don't care if UTCOffset is a number or timestamp as long as Timestamp ( ... ) ± UTCOffset is a timestamp.

                • 5. Re: Timestamp Calculations

                  I still miss what you want to achieve.


                  (inputs) - (mystery) - (outputs)


                  usually the inputs and outputs are on you, the mystery is on us.

                  • 6. Re: Timestamp Calculations

                    Hello jbante,


                    To test your comment, I replaced the Get ( CurrentTimestamp ) with UTC + 2 Timestamp in the UTCOffset calculation


                    Let ( [

                    UTCOffset = ( GetAsTimestamp ( Get ( CurrentTimeUTCMilliseconds ) / 1000 ) + 7200 ) - Floor ( Get ( CurrentTimeUTCMilliseconds ) / 1000 )


                    GetAsTimestamp ( "5/30/2017 1:33:25 PM" ) + UTCOffset



                    The result is similar to 17675487:33:25.089 which is Time

                    You will get a Timestamp if you change " + 7200" to " - 7200" in the UTCOffset calculation


                    I believe that UTCOffset must be a number, unless I misunderstood something or the test is flawed.



                    • 7. Re: Timestamp Calculations

                      What I'm not seeing is an example where $someTimestampFromARealApp ± OriginalUTCOffsetCustomFunction results in something that is not a timestamp. When I insert the calculation for the function you linked to in place of the UTCOffset variable in your test, I get a timestamp result.


                      I looked up the canonical version of the function, instead of what's on Brian Dunning's site. I see that I changed it to cast the result as time-type data for some reason. I don't exactly remember why. I must've been in a mood. I'd resist that change today, but it isn't worth the 60 seconds to change it back now. Using that function in your test calculation instead also gets a timestamp result.


                      All the same, it is very odd on FileMaker's behalf that changing "+ 7200" to "- 7200" in your test should change the data type of the result. I don't know that it's a bug, per se. The nuances of FileMaker's type-casting behavior are not thoroughly documented, so there isn't always a standard against which to compare observed behavior. So we just design against the observed behavior. It might be nice to see that wrinkle ironed out, but I'm not losing any sleep over it.


                      I see now. I had to try something different to understand what you were trying to say. I wasn't seeing the connection between the result of UTCOffset being a different type at different times and the result of the final timestamp arithmetic also being a different type. When UTCOffset is time- or number-type data, the result of $timestamp ± UTCOffset is a timestamp; but when the UTCOffset result is a timestamp, then $timestamp ± UTCOffset is a time. Weird, indeed. In any case, the current version of the UTCOffset function does not appear to have this problem.

                      1 of 1 people found this helpful
                      • 8. Re: Timestamp Calculations

                        Right, given your the canonical version of the function I can see you probably run into this before (or it might have been something else).

                        You changed the result to Time and I changed it to Number, but it has to be changed to something standard, otherwise you get something weird, indeed, as you said.

                        • 9. Re: Timestamp Calculations

                          Here is a good document on PostgreSQL that how data types should be calculated.

                          PostgreSQL: Documentation: 9.6: Date/Time Functions and Operators


                          Timestamp is a moment in time flow, so

                          timestamp - timestamp

                          is a time, or number of seconds, or "interval", but

                          timestamp + timestamp

                          is not a valid calculation.


                          time-type in FM have 2 means, a moment in a day (like 12:34AM) and "interval" (12 hours and 34 minutes, difference between 2 timestamps or 2 times)