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

# Timestamp Calculations

Hello,

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 )

Thanks

• ###### 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

• ###### 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.

Thanks

• ###### 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.

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)