Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Add Time to Date

  1. #1
    Join Date
    Apr 2008
    Posts
    4,696

    Default Add Time to Date

    Ok,

    So I'm CLEARLY missing something obvious, and want the forum's help to find what I'm missing.

    I have several fields:
    Date Time1 Time2 Time3
    090301 000021 000327 015831

    I can get the Date to come in as a Date (Date 00:00:00), and Time1, Time2, Time3 to come in as Dates (more accurately 1970-01-01 Time) and I want to add the date and time together for storing accurately in a database.

    I tried calculator, but get error "The 'plus' function only works on numeric data and Strings."
    I tried Modified JS as var OutDT = Date+Time1, format as date, but I get the two timestamps appended to each other (String Concatenation)

    Is this really a difficult thing to do?

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Not really. Make sure you add 2 strings and you'll be fine. If you insist the input of 090301 is a data, first convert it back to a String (Select Values, metadata tab). Then concatenate (Calculator for example) and convert back to a date (Select values, Metadata tab) with format yyMMddHHmmss.

  3. #3
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    I would have thought that it would be easier, since both the date and time are internally dates, and I know you can subtract dates in the JS step

    It should be pretty easy to convert them to milliseconds (since 1970-01-01 00:00:00 UTC), and add them, but I just can't quite spot how to do it in a single step.

    I'm having to do this because the source text actually is coming in as
    yyMMdd0HHmmss - I'm not sure whose idea that was, but the input format doesn't like having a 0 in the middle, so I have to make it 3 fields, and drop the middle one.

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

    Default

    Sure, converting a Date to an Integer gets you the Epoch time (time since 1970/1/1 00:00:00 in ms). However, there is no internal Time format at all.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Aha!
    I knew I wasn't explaining what I had done carefully enough!

    Field Date is imported as Date with mask yyMMdd
    Filed Time1 is imported as Date with mask HHmmss

    So I'm trying to add two Dates together, rather than "Date + N days"

    I can understand how adding two Dates would result in nonsensical data though (add 2009-04-01 + 2009-03-01 -> 2048/05/30 ?)

    I guess I just have to do it using a few "Select Values" and "Calculator" 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.