Millisecond Unix timestamp human conversion craziness

[naplesweb]

I'm posting here to attempt to preserve my hairline - as I've been tugging at my hair since Friday since I'm hoping someone in the community has run into this already.

I have log data from a cloud based load-test with a millisecond epoch timestamp like 
1582668354014

In Excel, (sorry - I dislike it too)  I have found I can get a "human" readable timestamp for this by 
=((A20/86400000)+DATE(1970,1,1))-TIME(5,0,0)   
(Where A20 is my timestamp and the -Time(5,0,0) adjusts for GMT-5 (US East))
Excel resolves the above calculation to 43886.7124307176 and

if I apply a format as "mm/dd/yyyy h:mm:ss.000 AM/PM" , it displays:
02/25/2020 5:05:54.014 PM  - BINGO -  exactly the human version of what I need - except that it's in Excel  

I don't even really technically need the .000 ms at the end although it's great to have it.

I found a number of references to functions that sound like my requirement and tried them.

The most promising reference was in this forum by AHunter3 in this post 
However, I am still having no luck getting this to resolve to a timestamp.

My millisecond epoch field is a numeric field named "unixTimeStamp" 
My calculated field, called HumanTimestamp calculation is: 

GetAsDate ( "1/1/1970" ) +unixTimeStamp with calculation result is "timestamp"

My result is "?"  - maddeningly nonspecific

I also tried adapting a function from Brian Dunning https://www.briandunning.com/cf/64 
Same result - "?" 

PS: I've also tried storing the original value as text and using "getAsDate" or "getAsTimestamp" in case there is a type mismatch.
Any help/guidance is GREATLY appreciated.

 

 

 

 

[naplesweb]

So far, no go with everything else I've tried.
Currently still loading these silly files into Excel.  

[AHunter3]

Quote

The most promising reference was in this forum by AHunter3 in this post 
However, I am still having no luck getting this to resolve to a timestamp.

Are you setting the result of your calculation field to "timestamp"?  If so, and it still isn't working, what are you getting?  A question mark that shows gibberish when you click into it?  Something that looks like a timestamp but has the wrong value in it?  Something else?

Edited to Add:  if those are MILLISECONDS instead of seconds (seriously?) you would need to divide by 1000 to get seconds.  Timestamps in Filemaker-ese are in seconds.

[naplesweb]

Super appreciative of the review.

Yep, the calc result is defined as timestamp.
The result (even if clicking in the field) is a simple "?" character.
Tried the basic formula, also tried dividing the timestamp by 1000 (to get to seconds from ms) - same result.
I've also tried having the source timestamp field be text vs number - no real change from that.

Below I attached a screenshot with a couple things I've tried,

I also attached a mostly emptied out fmp file with one sample record sample in case it helps visualize the issue.

Inside the fmp, on the workspace there is a screenshot from excel with the working formula from there too.

DigestRawPerformance.fmp12

[Josh Ormond]

This isn't the most efficient calc, but should show you the process to step through and convert the milliseconds.

Let ( 

[

	input = 1582668354014 ; 
	secs = input / 1000 ; 
	epochTimeStamp = GetAsNumber ( GetAsTimestamp ( "1/1/1970" ) ) ; 
	offset = GetAsNumber ( Time ( -5 ; 0 ; 0 ) ) ; 
	totalsecs = secs + epochTimeStamp + offset ; 
	result = GetAsTimestamp ( totalsecs )

] ; 

	List ( input ; secs ; epochTimeStamp ; offset ; totalsecs ; result )

)

Obviously, your final answer is in 'result'. So remove the list and just return result, and replace the input with whatever you are using in your file.

[naplesweb]

ABSOLUTELY WHAT THE DOCTOR ORDERED!
THANK YOU THANK YOU THANK YOU!

Quote

Let ( 
[
input = unixTimeStamp;
secs = input / 1000;
epochTimeStamp = (GetAsTimestamp ( "1/1/1970" ));
offset = GetAsNumber ( Time (-5;0;0));
totalsecs = secs + epochTimestamp + offset;
result = GetAsTimestamp ( totalsecs )
];
result
)

 

DigestRawPerformance.fmp12

Edited March 10, 2020 by naplesweb

[Josh Ormond]

I would make sure you get the GetAsNumber ( ) around the epochTimeStamp. That converts, or more accurately, reveals the number of seconds that FileMaker stores. Most of the time FileMaker handles the timestamp fine, I just prefer to be a touch more explicit.

[naplesweb]

On 3/10/2020 at 3:34 PM, Josh Ormond said:

I would make sure you get the GetAsNumber ( ) around the epochTimeStamp. That converts, or more accurately, reveals the number of seconds that FileMaker stores. Most of the time FileMaker handles the timestamp fine, I just prefer to be a touch more explicit.

Thank you for the update/clarification.   I really do appreciate this forum.
It has helped me understand more of the concepts I would never have grasped.

It is noted and updated in my calc- no impact seen in the result, but I do like doing this as correct as possible for best and most predictable outcome!:

Below is the updated final calc for my "HumanTimestamp" field:  
 

Quote

Let ( 
[
input = unixTimeStamp;
secs = input / 1000;
epochTimeStamp = (GetAsTimestamp ( "1/1/1970" ));
offset = GetAsNumber ( Time (-5;0;0));
totalsecs = secs + GetAsNumber ( epochTimestamp ) + offset;
result = GetAsTimestamp ( totalsecs )
];
result
)

 

Edited March 17, 2020 by naplesweb