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.
1 of 1 people found this helpful
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
Thanks for your help
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.
I still miss what you want to achieve.
(inputs) - (mystery) - (outputs)
usually the inputs and outputs are on you, the mystery is on us.
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.
1 of 1 people found this helpful
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.
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.
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)