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

    EPOCH date

    DrDave

      Title

      EPOCH date

      Post

      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
          philmodjunk
            

          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
            DrDave
              
            June 12, 2009 @ 2:20pm
             
            When I have that value in Excel, here is the formula that I use, where B2 is 1244830808
             
            =(((B2-(4*3600))/86400)+25569) 
            • 3. Re: EPOCH date
              philmodjunk
                

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

               

              http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=27145&query.id=153990#M27145

               

              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
                DrDave
                  

                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
                  gregl
                    

                  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.