Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Best Way to Handle Time Fileds

  1. #1
    Join Date
    Sep 2010
    Posts
    6

    Default Best Way to Handle Time Fileds

    In my data sent I have a time field that is imported as a string but in the format hh:mm:ss. I would like to use this as a filter value so I can pull records that were crated after a certain time (ex. time>11:15:00).

    What is the best way to handle this?

    I have been trying to add a constant for todays date (haven't figured this our) and then using the calculate to concant the two fileds (Add time B to date A). My logic is I could then use todays date to normalize the date time and let me get the values I want.

    I'm open to suggestions. Thanks in advance for helping a newbie.

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    Use the Get System Info step to retrieve the current date and then concatenate that date with your incoming time strings and the cutoff time using the Calculator step. Lastly, use a Filter Rows step to determine which times are before the cutoff and which ones are after. Here are the steps I used.
    1. Data Grid - generate some time strings to test the logic. This is where your incoming data would be read.
    2. Add Constants - add a time string constant to compare against. This really should be a parameter or read from a file.
    3. Select Values - convert the fields from 1. and 2. to Date datatype using HH:mm:ss format.
    4. Get System Info - retrieve the current date
    5. Calculator - concatenate the current date to the time values and the cutoff time value.
    6. Filter Rows - compare the datetime values with the cutoff datetime.
    Name:  OnOrAfter.jpg
Views: 36
Size:  12.2 KB
    Attached Files Attached Files
    Last edited by darrell.nelson; 11-08-2011 at 01:50 PM. Reason: Provide more detail
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Sep 2010
    Posts
    6

    Default

    Thanks, this worked great.

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.