Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Millisecond Unix timestamp human conversion craziness


naplesweb
 Share

Recommended Posts

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.

 

 

 

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

image.thumb.png.88fb9a601b3e144e11c0cd8e606eba74.png

DigestRawPerformance.fmp12

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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
)


 

filemakertodayForum_2020-03-10_12-18-59.thumb.png.1ca32d9399ba0e5b70e920e1bca5ead1.png

DigestRawPerformance.fmp12

Edited by naplesweb
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by naplesweb
Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use