EpochToTimestampWithTZ ( epochMs ; tzOffset )
Convert Epoch to Timestamp with timezone
Be the first to rate this function Log in to vote
|
Zohaib - Show more from this author
Idiosol https://idiosol.com/ |
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
Purpose of This Code
This custom function converts:
Epoch time in milliseconds + Timezone offset
➡ into a FileMaker Timestamp adjusted for the given timezone.
It is typically used to convert JavaScript / API epoch timestamps into readable local timestamps inside FileMaker.
🔎 Step-By-Step Breakdown
The calculation uses Let() to define variables and then returns a final timestamp.
1️⃣ Convert Milliseconds to Seconds
epochSeconds = epochMs / 1000 ;
What it does:
Epoch time is usually provided in milliseconds.
FileMaker timestamps work in seconds.
✅ So this converts milliseconds → seconds.
Example:
epochMs = 1772046000000
epochSeconds = 1772046000
2️⃣ Extract Timezone Sign (+ or -)
tzSign = Case ( Left ( tzOffset ; 1 ) = "-" ; -1 ; 1 ) ;
What it does:
Checks the first character of tzOffset.
If it starts with - → timezone is negative.
Otherwise → timezone is positive.
Example:
tzOffset = "-5:00"
tzSign = -1
tzOffset = "5:00"
tzSign = 1
✅ This determines whether to subtract or add the offset.
3️⃣ Remove the + / - Sign for Parsing
tzClean = Substitute ( tzOffset ; "-" ; "" ) ;
What it does:
Removes the minus sign so we can extract hours & minutes cleanly.
Example:
tzOffset = "-5:30"
tzClean = "5:30"
4️⃣ Extract Hours and Minutes
tzHours = GetValue ( Substitute ( tzClean ; ":" ; "¶" ) ; 1 ) ;
tzMinutes = GetValue ( Substitute ( tzClean ; ":" ; "¶" ) ; 2 ) ;
What it does:
Replace : with a line break
Extract:
Line 1 → Hours
Line 2 → Minutes
Example:
tzClean = "5:30"
Becomes:
5
30
Then:
tzHours = 5
tzMinutes = 30
5️⃣ Convert Timezone Into Seconds
totalOffsetSeconds = tzSign * ( ( tzHours * 3600 ) + ( tzMinutes * 60 ) ) ;
What it does:
Converts timezone hours & minutes into total seconds.
Formula:
Hours → multiply by 3600
Minutes → multiply by 60
Add them
Apply + or - sign
Example:
For:
tzOffset = "-5:30"
Calculation:
(5 × 3600) = 18000
(30 × 60) = 1800
Total = 19800 seconds
Apply - sign = -19800
✅ This gives the correct timezone offset in seconds.
6️⃣ Define Epoch Start Date
epochStart = Timestamp ( Date ( 1 ; 1 ; 1970 ) ; Time ( 0 ; 0 ; 0 ) )
What it does:
Defines:
January 1, 1970 00:00:00
This is the standard Unix epoch start.
FileMaker doesn't automatically assume this — so it's defined manually.
🚀 Final Calculation
epochStart + epochSeconds + totalOffsetSeconds
What it does:
It builds the final timestamp by adding:
Component Meaning
epochStart Base date (1970)
epochSeconds Seconds since 1970
totalOffsetSeconds Adjust for timezone
✅ What The Function Ultimately Returns
It returns:
A correctly adjusted FileMaker Timestamp
Converted from milliseconds + timezone offset.
🧠 Real Example
Input:
epochMs = 1772046000000
tzOffset = "-5:00"
Process:
Convert milliseconds → seconds
Convert timezone → seconds
Add everything to 1970 timestamp
Output:
✅ Local timestamp adjusted to -5:00 timezone
Comments
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.