4 Replies Latest reply on Sep 11, 2012 12:10 PM by Eric Munisteri

    Is there an assist with Timestamp Conversion?

    Eric Munisteri

      I am attempting to convert a timestamp to the following format Month, Day, Year  HH:MM:SS.  My test Timestamp is 1346282945737 which should give me August 29, 2012 23:29:05 UTC but I receive January 4, 44632 instead using "DATEADD('second',INT([Event Audit Time Stamp]),#1970-01-01#)"

       

       

      I am not an expert and need an assist.  Thanks,

       

      E

        • 1. Re: Is there an assist with Timestamp Conversion?
          Jonathan Drummey

          Your timestamp is down to the millisecond, try this calc instead:

           

          DATEADD('second',[Event Audit Time Stamp]/1000,#1970-01-01#)

           

          Jonathan

          • 2. Re: Is there an assist with Timestamp Conversion?
            Eric Munisteri

            thanks for the reply, Jonathan

             

            That calculation is one that I tried early on (from looking at the forum), but it errors: "Can't divide a string value by integer value".

             

            Also tried DATEADD('second',INT([Event Audit Time Stamp])/1000,#1970-01-01#) but same error.

             

            It's close with the INT inserted...but not correct, hence the incorrect Month and Day, not to mention the 44632 that tails the result.

             

            Still at a loss.


            E

            • 3. Re: Is there an assist with Timestamp Conversion?
              Jonathan Drummey

              I set up a workbook with the timestamp as a string field and tried your version of it in the latest reply and I get the correct result. Is there any chance you can post a packaged workbook (.twbx) with just that row of data?

               

              Jonathan

              • 4. Re: Is there an assist with Timestamp Conversion?
                Eric Munisteri

                Jonathan, Thank you for your timely responses.

                Ok.  I am perplexed.  Started stripping the proprietary data so I could package the workbook and upload it…looked at the data again and wha-la…it works.

                I actually do a two-step process:

                1)     Pull the Time Stamp Data from a field that has multiple sets of data separated by comma’s and/or carriage returns using this formula: Event Audit Time Stamp = MID(STR([Event Annotation Audit Trai]),3,13)

                2)     Convert the finding to a human readable Date and Time using this formula - DATEADD('second',INT([Event Audit Time Stamp])/1000,#1970-01-01 00:00:00#)

                 

                Lastly, make these calculations BEFORE you extract the data.  I continued to received errors until I stopped using the extract, created the calculated fields, then extracted the data.

                 

                Hopefully this helps others with similar situations.