5 Replies Latest reply on Oct 28, 2009 3:46 PM by gregl

    EPOCH date



      EPOCH date


      I have a text field in my FMP 10 database, that represents the date, in EPOCH format. An example value is as follows: 1244830808


      I would like to populate an existing (but empty) date field in the same database, called Customer::DateSent.


       What would the script command look like for this.


      All help is appreciated. 

        • 1. Re: EPOCH date

          A simple calculation field should convert the date for you without any need for a script.


          What date is represented by 1244830808?

          • 2. Re: EPOCH date
            June 12, 2009 @ 2:20pm
            When I have that value in Excel, here is the formula that I use, where B2 is 1244830808
            • 3. Re: EPOCH date

              I believe this is the same as what other posters have called "Unix" time. See this link For more info:




              In my experiments with the math, I just did a straight conversion:


              Epoch + 62135582392


              In a calculation field set to return Time Stamp, but that appears to ignore time zone issues.



              • 4. Re: EPOCH date

                I finally got this working -- thank you for your help. Here was the solution:


                > EPOCH + 62135596800 + TZ * 3600


                > where TZ is the difference, in hours, between your local time and UTC 

                > (Unix time is always UTC).


                Thanks to my friend, Dave! :-) 


                • 5. Re: EPOCH date

                  Since Excel was mentioned, don't forget that Excel supports two different Epochs: 31 December, 1899 CE, and January 1, 1904 CE (Macintosh epoch). The UNIX epoch starts from midnight beginning January 1, 1970 CE.


                  Time values in each epoch are given as seconds since midnight beginning on those dates.  Whichever epoch is in use is controlled by a setting in Excel "Use 1904 Date System", and is preserved in the Excel file.


                  When FileMaker Pro exports dates or date-times to Excel files, it takes care of the conversion to the appropriate epoch value. If you're exporting your own number fields to spreadsheets expecting them to be treated like date/time values, then you must be aware of the epoch in use by the spreadsheet file.


                  You've noticed the fudge value that you need to add to your number in order to translate to a FileMaker Pro date/time field is quite large (62135596800 to translate from UNIX epoch)... this is because FileMaker Pro's internal epoch (when it needs to use one) is said to start from midnight beginning January 1, 0001 CE.