4 Replies Latest reply on Aug 27, 2010 1:38 AM by MikhailEdoshin

    Calculating time from timestamps

    dkdkdkd

      Title

      Calculating time from timestamps

      Post

      Can you treat timestamps like numbers, adding and subtracting, then converting the result to a readable time?

      For example, I' have a program to keep track of the time I spend on a project. I have a button that makes a timestamp when I start and another when I finish. This works great, but if I work past midnight,  my second timestamp value is less than my first, leading to a negative value.

        • 1. Re: Calculating time from timestamps
          sunmoonstar.13

          Can you treat timestamps like numbers, adding and subtracting, then converting the result to a readable time?

          Short answer: yes, but you don't necessarily need to "convert" anything.

          Presumably, you have two actual Timestamp fields that show date and time (I'll call them t_start and t_end) and a Calculation field (to calculate the difference between the two fields). Set the result of the calculation to "Time" then use the following calculation:


          Case ( t_start and t_end ; t_end - t_start )

          The calculation field will then show the elapsed time in hours, minutes and seconds when there are values in both Timestamp fields.

           

          Nick

           

          • 2. Re: Calculating time from timestamps
            MikhailEdoshin

            From the original post it seems that the fields' type is Time, not Timestamp. So the first step is to change these fields to Timestamp type. Timestamp saves both date and time. When you subtract one timestamp from another, you get a time value, which can be formatted as required.

            • 3. Re: Calculating time from timestamps
              nilsw

              internally the timestamp is stored as an integer counting seconds from 01/01/0001 00:00:01

              GetAsNumber( Timestamp( "01/01/0001"; "00:00:01")) returns 1

              or vice versa

              GetAsTimestamp( 3600 ) returns "01/01/0001 01:00"

              Nils

              • 4. Re: Calculating time from timestamps
                MikhailEdoshin

                @filemakergarage: You're right; but when you subtract Timestamps, the result is Time, which is also stored as number of seconds. Unlike Timestamp, Time a) can be negative and b) has a much wider span than timestamp. Basically Time serves double duty in FileMaker: as a real ‘time of day’ type, and as a time delta type.