Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum
naplesweb

Millisecond Unix timestamp human conversion craziness

Recommended Posts

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.

 

 

 

 

 

 

Share this post


Link to post
Share on other sites
naplesweb

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

image.thumb.png.88fb9a601b3e144e11c0cd8e606eba74.png

DigestRawPerformance.fmp12

Share this post


Link to post
Share on other sites
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.

  • Like 1

Share this post


Link to post
Share on other sites
naplesweb
Posted (edited)

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
naplesweb
Posted (edited)
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use