10 Replies Latest reply on May 15, 2011 4:20 AM by LaRetta_1

    Calc time elapsed

    eibcga

      Title

      Calc time elapsed

      Post

      I keep a database of when a certain event occurred, each record with a date field and a time field (in 24 hour format).

      I'd like to be able to calculate the elapsed time, in hours and minutes (in decimal form), between the date and time on one record to the date and time of the previous record.  It works as intended when the time in the previous record is on the same date, but I get negative results when the time of the previous record is on a different date (see attached screenshot).

      I have a Duration "dur" calculation field, result as number, with formula:

      (Hour ( time )+Minute(time)/60)-(Hour ( prevtime )+Minute(prevtime)/60)

      I also have a Previous Time "prevtime" calculation field, result as time, with formula:

      If((Get(RecordNumber)-1) ≠ 0;GetNthRecord(time;Get(RecordNumber)-1);0)

      Please help!

      Screen_shot_2011-04-16_at_9.12.23_AM.png

        • 1. Re: Calc time elapsed
          bumper

          Create two fields, StartTimestamp and EndTimestamp. Make buttons to set the respective fields with Get(currentTimestamp)

          Third field, Duration, a calculation: EndTimestamp-StartTimestamp, calculation result set to Time. 

          Bonus this will correctly calculate across multiple days.

          • 2. Re: Calc time elapsed
            eibcga

            Thanks Bumper, I have considered this.  However, I'm interested in the time elapsed since a certain event occurred, not how long the event lasted for.  For example, a database to track how many visits a bird makes to a certain bird feeder, and the time elapsed since the bird last visited that same feeder (not how long the bird stayed for a visit at the feeder).  Anyway, your solution has given me an idea.  I do in fact have a timestamp field for each record (which I didn't show in the screenshot) indicating the time I created the record.  Since I create the record at essentially the same time as when the event occurred, why not just calculate the difference between the timestamp in the current record, and the timestamp in the previous record.  Thanks!

            • 3. Re: Calc time elapsed
              eibcga

              I cannot seem to have the elapsed time calculate correctly when the previous time record is a day or more ago. For example, from the screenshot in my previous post above, an entry is made on April 13, 2011 at 20:17.  The next entry is on April 15, 2011 at 21:24.  So, the elapsed time between the two records should be 25:12 or [24-(20+17/60)+(21+24/60)], not 1:12, since 24 hours is missing.

              I currently have the following fields:

              dur, calculation field, result as number, Hour(date_create-prevtime)+Minute ( date_create-prevtime )/60

              prevtime, calculation field, result as time, If((Get(RecordNumber)-1) ≠ 0;GetNthRecord(date_create;Get(RecordNumber)-1);0)

              date_create, timestamp field, creation timestamp(date and time)

              Please help!  Thank you.

              • 4. Re: Calc time elapsed
                LaRetta_1

                Using a calculation based upon GetNthRecord() will be very slow and get slower as your database grows. It has other limitations such as a bug which causes it to break if used with List(), Min() or Max().  I would suggest instead that you consider this method:  Go to your relational graph and create another copy of this Sightings table (using the ++).  This will create a table occurrence named Sightings 2.  Now join as:

                Sightings::CreationTimestamp > Sightings 2::CreationTimeStamp

                ... and in the relational box below, sort Sightings 2 in descending order on that CreationTimeStamp.  You now have a relationship from your current Sighting record to the prior Sighting record and Sightings 2::CreationTimeStamp can be placed on your layout any time to show you the last time the bird was sighted AND it allows a calculation to easily handle it.  So now create a calculation in your table called cElapsedTime with:

                Case (
                Sightings 2::CreationTimeStamp ;
                CreationTimeStamp - Sightings 2::CreationTimeStamp
                )

                Set the result as TIME.  This will display as hours, minutes and seconds since the last sighting and remember that you can place the Sightings 2::CreationTimeStamp next to it to display the date it was last sighted.  If you add different birds to this table, add the bird in the relationship as:

                Sightings::CreationTimestamp > Sightings 2::CreationTimeStamp
                AND
                Sightings::BirdID = Sightings 2::BirdID

                ... and that calc (and the date display) will filter for only that particular bird. Also, you can add a calculation to count how many times the bird has appeared using same method ... simply create calc with:  Count ( Sightings 2::CreationTimeStamp )

                • 5. Re: Calc time elapsed
                  eibcga

                  You indicated that " You now have a relationship from your current Sighting record to the prior Sighting record " but I don't know how that can be?  Using the method suggested the result I get for cElapsedTime is just 0:00:00.  How do I add a screenshot to this post?  I can easily add a screenshot on new posts, but do not seem to get the same option when replying to an existing post.

                  • 6. Re: Calc time elapsed
                    LaRetta_1

                    "You indicated that " You now have a relationship from your current Sighting record to the prior Sighting record " but I don't know how that can be?"

                    I explained this:

                    Go to your relational graph and create another copy of this Sightings table (using the ++).  This will create a table occurrence named Sightings 2.  Now join as:

                    Sightings::CreationTimestamp > Sightings 2::CreationTimeStamp

                    I don't know how to attach pictures here but you should be able to using the tree icon (third from left) in the header of the message.   But truly, if you step through my instructions step by step, you will have success; I guarantee it.  As you step through it, if something doesn't make sense, post exactly where you are in the steps, what you did, what you see and what dosn't make sense. 

                    We'll get you through this.  An alternative is to send me your file (it can be empty clone).  If you wish to do this, send me a private message here and I will provide my emai address.  But I'm sure you can step through this and get it done.  :^)

                    • 7. Re: Calc time elapsed
                      LaRetta_1

                      Let me ask this way:

                      Did you go to the relational graph, select your table and click ++?

                      Did it create a second box (table occurrence of your table with a 2 at the end?

                      Did you join them (point at the timestamp field in the first table and drag cursor to timestamp field in second occurrence) and use the join operator of >

                      If you go to your layout and Insert > Field and select the table with the 2 at the end and place it on the layout, do you see anything?  It won't produce anything on the first record because there is no prior record to calculate against.

                      If you've done all of this but your calc still doesn't produce a result, then please paste your calc here. :^)

                      • 8. Re: Calc time elapsed
                        eibcga

                        Thank you very much indeed LaRatta, for taking your time and expertise to take a look at my cloned file.  I really appreciate it.  I am learning a lot about FileMaker and it's my favourite database software!  Easy to use and it just works.

                        • 9. Re: Calc time elapsed
                          eibcga

                          How would the suggestion change if I am already using a separate date field, and time field, rather than one create timestamp field?  Is there a way I can convert the existing records in the date and time fields to a timestamp so I can re-import my existing records into the new setup?

                          • 10. Re: Calc time elapsed
                            LaRetta_1

                            Hi Erik,

                            1) You would create a timestamp field (for present time, do not turn on auto-enter creation).
                            2) Back up your file for safety just in case
                            3) Put your cursor in the new timestamp field (maybe call it CreationTimeStamp or CreationTS) and then
                            4) Select Records > Replace Field Contents > By calculation and enter this calculation:
                                TimeStamp ( YourDateField ; YourTimefield )
                            5) Say OK and then REPLACE

                            Once done, and if this is for auto-enter then you can turn auto-enter creation timestamp on for the field. ;-)