1 Reply Latest reply on May 7, 2015 3:05 PM by TSGal

    Timestamp: seconds truncated when stored as variable

    andrewblackwell

      Summary

      Timestamp: seconds truncated when stored as variable

      Product

      FileMaker Pro

      Version

      13

      Operating system version

      OS 10.10

      Description of the issue

      When a timestamp value with 0 seconds is created as a full value, either in a field, or as a function (TIMESTAMP (DATE (MM;DD;YYYY); TIME (HH;MM;SS)), and this value is then stored as a variable, the seconds get truncated.

      So, for example, 07/05/2015 21:50:00 becomes 07/05/2015 21:50 when stored as a variable.

      This behaviour gets critical when one uses the variable to perform a find with a greater than symbol in a script step. The inserted find value then becomes "> 07/05/2015 21:50:*" - in other words, a star is inserted for the seconds, and all values between 21:50 and 21:51 are ignored, whereas the original intention was to find all results including timestamps between 21:50 and 21:51 (as the search should have said "> 07/05/2015 21:50:00").

      I noticed this issue in a synch system built to synch data between two databases, where around 5% of the data was not being synchronised because of this issue!

      Steps to reproduce the problem

      Create a global field in Table A to hold a timestamp value, and fill it with the following value:

      "07/05/2015 21:50:00"

      Create a script that stores this value in a variable (e.g. $$timestamp).

      Observe in the data viewer how the timestamp turns into "07/05/2015 21:50" (without the seconds).

      Use the variable in a search script step on another Table B, where the value sought is ">" & $$timestamp.

      Execute the script and observe how the find turns into "> 07/05/2015 21:50:*".

      Expected result

      see above

      Actual result

      see above

      Workaround

      Using a greater than or equal sign instead of a greater than sign circumvents the problem. Another workaround is to artificially expand the variable to include the seconds.

        • 1. Re: Timestamp: seconds truncated when stored as variable
          TSGal

          Andrew Blackwell:

          Thank you for your post.

          I am unable to replicate the issue.  This is what I have done:

          1. I created a new table "timestamp" with a Timestamp field named "TS", and set it for global storage.

          2. I created a script, "GetTimeStamp", with the following steps:
               Set Variable [ $notglobaltimestamp ; Value: TS ]
               Set Variable [ $$globaltimestamp ; Value: TS ]
               Show Custom Dialog [ $notglobaltimestamp ; $$globaltimestamp ]

          3. In Browse Mode, I added a record, and entered into TS:     5/7/2015 12:45:00

          4. With the Script Debugger and Data Viewer active, I ran the script "GetTimeStamp", and the seconds showed for both the local and global variables (See screen shot below).

          Let me know what I'm doing differently than you so I can replicate the issue.

          TSGal
          FileMaker, Inc.