Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: How can we do calculation on time data type column ?

  1. #1
    Join Date
    Aug 2008
    Posts
    187

    Default How can we do calculation on time data type column ?

    I have a employee table like this:
    create employee_table (
    emp_id int(10),
    str_time_in varchar(20),
    str_time_out varchar(30)
    )
    The content example of this table is like this:
    emp_id str_time_in str_time_out
    1 08:10:20 17:20:20

    What I want to do is convert both str_time_in and str_time_out columns to time data type (time_in and time_out) and do substract operation (time_delta = time_out - time_in).
    Finally, all these three columns will be written to a table.
    I managed to convert both str_time_in and str_time_out to columns which are time data type using java script step and store both values to a table, but I don't know how to perform substract operation on both columns and store the result in the same table.
    I know that this sql statement wil produce time_delta
    Code:
    select subtime(time_out, time_in) from table_output
    But I don't know how to insert/update this on the target table.

    I appreciate your time taking a look at this post and give some advice on this...
    Thanks.

    (note: ktr file attached)
    Specification:
    kettle 3.0.4
    windows server 2003
    java 1.5.0_09
    Attached Files Attached Files

  2. #2

    Default

    Hi Nashrul,

    Based on your case, I've created a wiki article answering your question. You can check it in :

    http://pentaho-en.phi-integration.co...n-text-columns

    I also attached a ktr file in that article. In additional for that, you can also see how we define date/time format in Kettle in this article :

    http://pentaho.phi-integration.com/k...at-jam-tanggal

    It's in Indonesian language as I bet you can understand it

    Regards,

    Feris

  3. #3
    Join Date
    Aug 2008
    Posts
    187

    Default

    Thanks bro for your wonderful articles..
    It really helps..

  4. #4
    Join Date
    Nov 1999
    Posts
    9,728

    Default

    Of-course, date/time differences are easy to calculate with this trick as well:

    Date1 --> Convert to Integer1
    Date2 --> Convert to Integer2

    Calculate the difference in milliseconds:

    DiffMs = Date2 - Date1

    Divide by 1000 to get the difference in Seconds, etc.

    Matt

  5. #5

    Default

    Hi Matt,

    I never used to convert date to Integer. But have tried it and appears to be simpler.

    Thanks for this useful tip

    Regards,

    Feris
    Last edited by feristhia; 01-12-2009 at 01:52 PM. Reason: Grammar editing

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 - 2017 Pentaho Corporation. All Rights Reserved.