Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Date Addition/Subtraction

  1. #1
    Join Date
    Jan 2014

    Default Date Addition/Subtraction

    Good evening,

    I am trying to extract two dates from a csv and calculate the difference measured in hours and days. I am not sure of the best approach to doing this, so I attempted a solution that probably ended up being overkill and may have some flaws (performance or accuracy) I overlooked. I am hoping someone can provide a bit of guidance.

    First, I extracted the data from a csv with the date fields as text (I have previously had data type problems using other types because the data comes from a suspect source). I should mention that one of the dates is provided as date and time separately, while the other contains both in the same field.

    Second, I add the date and time fields to produce a second datetime value using the Calculator step.

    Third, I cast both fields as numbers using the Select Values step.

    Fourth, I calculate the difference of the two dates as number using the Calculator step and one of the provided functions (A - B).

    Fifth, I copy the calculated difference field from step 4 as number using the Calculator step.

    Sixth, I set a constant (3600000) in the copied field using the Set Field Value to Constant step.

    Seventh, I calculate the "lag" in hours by dividing the value created in step four by the constant from step six using the Calculator step.

    Eighth, I copy the field created in step 4 as number using the Calculator step.

    Ninth, I set a constant (24) in the field from step 8 using the Set Field Value to Constant step.

    Tenth, I calculate the "lag" in days by dividing the value created in step 7 by the constant from step 9 using the Calculator step.

    Like I said, this is probably a really silly way of doing this, but it SEEMED to work well (there may be overlooked flaws, as I am new at this). I took a picture of the ETL but was unable to load it to the forum. What can I do to improve this process from an accuracy and performance standpoint? Any tips for dealing with extracted dates?


  2. #2
    Join Date
    Mar 2013



    If you are looking just for date difference then till step4 is enough, but if you need to consider timestamp difference then you need to perform till step10 but i think still if you try harder you can just minimize 1 or 2 steps.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.