1 2 3 Previous Next 31 Replies Latest reply on Aug 19, 2014 7:39 AM by jormond

    Timestamp format 12/31/4000 11:59:59.999999 PM in FM13

    Boutsy

      Hello,

       

      In Filemaker 13 The minimum and maximum values of a timestamp are 1/1/0001 12:00 AM and 12/31/4000 11:59:59.999999 PM.

      This is usefulle if you want Timestamps to be different when using Get(CurrentTimestamp). Now, when to users use a script with Get(CurrentTimestamp), within the same second, the timestamp is the same and a Find action will give 2 results. I cannot find where to specify how many decimals have to be shownn(or used but not shown) in a TimeStamp Field. Using the format Decimals with a fixed number of decimals will not change the field.

      Furthermore I noticed that if the timestamp ends on 00 seconds, when using a find request it wil give all records with the timestamp ending with00 till 59!!!

      Example : If you make a search in a timestamp field with 12/31/2014 11:05:00, the result will give all the records from 12/31/2014 11:05:00 till 12/31/2014 11:15:59. So all the records within the same minute!!!! I don't find a way to resolve this problem (f.e.: ==12/31/2014 11:05:00 will also give the same result!!!).

       

      Can somebody please help me?

       

      Thank you,

       

      Marc

        • 1. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
          beverly

          Marc, if you enter just the date, you may notice that FM automatically enters "*" in the time values for this type of field. I don't know if that helps or not.

           

          Can you or can you not enter second with decimal while in find mode?

           

          Beverly

          • 2. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
            MacEasy

            Hi Marc,

             

            Thought it was just you at first, but I ran into the same problem, seems like it's a filemaker bug to me.

             

            Two solutions I found. 

             

            1) Convert your searching to timestampe to text for example

            '8/4/2014 12:01:23 PM'   versus  timestamp(date(8,4,2014),time(12,01,00))

             

            2) Create a 2nd field that = your timestamp, but stored as a number and then convert the timestamp you are searching for to number also. 

            • 4. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
              Boutsy

              Thanks Beverly, Peter and Wim,

              Your answers all bring solution I also thought about. Only... these are solutions allthought you can find the following in the help section :

               

              "To format the seconds and fractional seconds component, in the Data Formatting area, click Number .01 then select the foramtting options you want...."

               

              Filemaker Help Timestamp Format.JPG

              It seems to me that this function just doesn't work, allthough I agree the your solutions help to work arround the problem.

               

              Thank you all for your advice.

               

              Best regards,

               

              Marc

              • 5. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                jlamprecht

                I assume by the function, you are talking about get(currenttimestamp)? That works as stated on its FileMaker help page. It provides the current date and time rounded to the nearest second:

                 

                http://www.filemaker.com/help/html/func_ref2.32.19.html

                 

                If you are trying to get fractions of a second, you are better off using the function Wim suggested. You can then do some conversion to get to the timestamp once more with that fraction of a second.

                 

                I am also not able to replicate the issue of only finding records with an exact timestamp entered. I able to find records with exact timestamps, such as the one you posted, 12/31/2014 11:05:00, and not get other records within the same second.

                 

                I hope this helps.

                 

                -JohnAustin

                • 6. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                  keywords

                  Re "Now, when to users use a script with Get(CurrentTimestamp), within the same second, the timestamp is the same and a Find action will give 2 results"

                   

                  If you want to distinguish between two records in this circumstance, have you thought of also having a user stamp created when a record is created? Both could be incorporated into a single calc (Record created at: <timestamp>, on <device>, by <username>, etc).

                  • 7. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                    DavidJondreau

                    "I noticed that if the timestamp ends on 00 seconds, when using a find request it wil give all records with the timestamp ending with00 till 59!!!

                     

                    Example : If you make a search in a timestamp field with 12/31/2014 11:05:00, the result will give all the records from 12/31/2014 11:05:00 till 12/31/2014 11:15:59. So all the records within the same minute!!!! I don't find a way to resolve this problem (f.e.: ==12/31/2014 11:05:00 will also give the same result!!!)."

                     

                    I can't replicate this. Can you provide an example?

                     

                    Here's a link to a screencast of the search working: http://youtu.be/Vba2PndcMeo

                    • 8. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                      user19752

                      Did you read 1st notice on help ?

                      You need to change both date and time formattings to other than 'As entered'.

                      But if doing so, fractions are shown but all digits are always zero! Yes, not working...

                       

                      I also can't replicate finding issue, but if enter criteria as

                      yyyy/mm/dd hh:mm:ss

                      that is change to

                      yyyy/mm/dd hh:mm:ss.*

                      so entering just 00 second finds also 00.1 second.

                      • 9. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                        Boutsy

                        OK David,

                        I couldn't replicate as well at first. Then I found the problem. Lets say you create a new record and in the field "TimeStamp" you put the current TimeStamp with the scriptstep Get(CurrentTimeStamp). If at that point the time is exactly on the minute you will get a timestamp like : 02/02/2014 12:00. As you see, it doesn't include seconds !

                        If later on you would like to search for records with this timestamp and you would do this in a script like :

                         

                        Knipsel.JPG

                         

                        You will not only get 02/02/2014 12:00, but also 02/02/2014 12:00:00 till 02/02/2014 12:00:59

                         

                        I hope this will clear my problem.

                         

                        Thanks,

                         

                        Marc

                        • 10. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                          beverly

                          Marc, perhaps this gets back to my reply. IF you do not supply values the

                          find assumes the wildcard "*" for those values.

                           

                          Find 12:00 assumes 12:00:* and 10:15:23 assumes 10:15:23.*

                           

                          So if you need Exact use the "=" symbol before the value

                           

                          FIND =10:15:23

                          • 11. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                            Boutsy

                            OK Beverly,

                            That I know, however, the problem is that when you use the Get(CurrentTimeStamp)- function in a script to assign the CurrentTimestamp to a field it will not mention the seconds if the time was exactly on the minute. If, for example I would use this funtion at exactly noon on the first op april 2000, you will get

                            01/04/2014 12:00 AND NOT 01/04/2000 12:00:00

                            So if you use the content of this field in a find function, even preceeded by = or even == it wil give you as a result everything from 01/04/2000 12:00:00 to 01/04/2000 12:00:59

                            Of course, you could do a check if the time had 5 or 8 digits, but I'm using this very much in all kind of scripts, so I don't find this a good solution.

                            Adding decimals would resolve this, but it seems that even that cannot simply be done with the get(CurrentTimeStamp)-function.

                             

                            Best regards,

                             

                            Marc

                            • 12. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                              beverly

                              "=" & Get ( CurrentTimeStamp ) doesn't work either

                              ? Or am I missing something.

                               

                              There is also GetAsNumber ( Get ( CurrentTimeStamp ) ), which may be more precise.

                               

                               

                              -- sent from myPhone --

                              Beverly Voth

                              --

                              • 13. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                                DavidJondreau

                                OK, so the problem is not with how FIleMaker finds timestamps. The problem is with how FileMaker *stores* timestamps.

                                 

                                And I can see how that's really annoying.

                                 

                                A Filemaker field won't accept a FM-generated timestamp (say using Get ( CurrentTimestamp) ) with the second = 0. It "rounds" that to the minute, so that value will represent all values in that minute in a find.

                                 

                                I thought we could get around that with an auto-entered calc to force the "00" but FM rounds that too.

                                 

                                You can force it by hand-editing the field, or using Insert from Calculated Result[], but that's not very helpful.

                                 

                                I'd say it's worth a bug report.

                                • 14. Re: Timestamp format 12/31/4000 11:59:59.999999 PM in FM13
                                  beverly

                                  is it really the storage? What happens if you look at any Timestamp field and return the results as number? Could it be the display and not the storage?

                                   

                                  Beverly

                                  1 2 3 Previous Next