3 Replies Latest reply on Sep 27, 2016 1:48 AM by Arkadiusz Bryndza

    Pentaho converting Table Input timestamp from Epoch

    Brent Goodman
      Share:|

      Epoch has been beaten to death, but here is another... With a Pentaho transformation, I am pulling data out of the BMC_ComputerSystem table vi the Table Input step. One of the fields is a timestamp field. The data is being pushed back into a form with the AROutput step. I use a JavaScript step to manipulate the epoch and convert to a date field. The AR Form however shows the standard 1970 year opposed to 2016.

       

      The script I am using looks like this. Any ideas how to correct this:

       

      //Script here

      var LAST_myActivatedDate;

       

      if (myACTIVATEDDATE == null || myACTIVATEDDATE == "")
      {
      LAST_myActivatedDate = null;
      }
      else
      {

       

      myACTIVATEDDATE = myACTIVATEDDATE * 1000;
      LAST_myActivatedDate = new Date(myACTIVATEDDATE);
      }

        • 1. Re: Pentaho converting Table Input timestamp from Epoch

          The script seems to be OK, but probably an epoch date with miliseconds is an issue for AROutput step.

          I would try adding a 'Select values' step just after your step with the script and set Format on 'Meta-data' tab to the one with seconds (like: yyyy/MM/dd HH:mm:ss)

          • 2. Re: Pentaho converting Table Input timestamp from Epoch
            Brent Goodman

            Looking at the date values in the database, we are seeing numbers like 2457568 and 2457590. Looking at the remedy form, the field type is a "Date" only field (holds no time).

             

            From the Remedy UserTool, the system appears to resolve the 2457568 to be 07/28/2016.

             

            Putting this date into an Epoch converter, we get 1469664000 for 07/28/2016 @ 12:00am UTC. Big difference in numbers.

             

            After some digging, we found a BMC Communities article from 2013 that gave a solution.

             

            It seems that the the date field contain the difference in days as the info from the concept manual said:

             

            date data type

            The data type used for fields containing date values. Date values can range from January 1,

            4713 B.C.E., to January 1, 9999 C.E. Date values are stored as the number of days from

            the beginning of the date field’s range. For example, January 1, 2009, is stored as the

            number 2454833 because it is 2,454,833 days after the first day in the date range.

             

            (page 80 from the Concept manual)

             

            In fact it seems the the date field are keeping a julian date and the formula to change the julian date to timestamp would be:

             

            unix_time_stamp = ( JD -2440587.5) * 86400

            • 3. Re: Pentaho converting Table Input timestamp from Epoch

              OK, so it's a Date field, not Date/Time (you used a term "timestamp")

              Great you found a solution.

              One hint - if you use JavaScript step only to convert a Julian date, you may prefer to simplify the script and handle date conversion in a Table Input step, something like:

              select ..., to_char(to_date(<your Date field>, 'j'), 'yyyy-mm-dd'), ...from ...  (in case of Oracle)