Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Apply time zone information to time value

  1. #1

    Default Apply time zone information to time value

    Hi,

    I have a Date field with the information:

    2017-11-09 16:00:00

    From a business perspective, I know that that is the valid time for the time zone

    America/Chicago (UTC-06:00)

    Now I have a Postgres DB with a table that has a column of type

    time with time zone

    and I want to see a value like

    16:00:00-06:00

    in there.

    Problem is: when I try to apply the time zone in the Select values step, it is automatically recalculating the time to

    15:00:00-06:00

    and I don't understand why.
    Am I missing something very obvious regarding time zones here or is there something else I have to do?
    Attached the transformation.

    Bobse

    timeZone.ktr
    PDI 7
    Postgres
    Windows 7

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

    Default

    Does your PDI machine happen to be in Eastern Standard Time?

  3. #3

    Default

    That is correct.
    PDI 7
    Postgres
    Windows 7

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

    Default

    So PDI is taking the implied time:
    2017-11-09 16:00:00 EST
    and converting it to CST...

    You can try creating a copy of your Spoon/Kitchen/Pan scripts that change the Java Options
    Code:
    set OPT=%OPT% %PENTAHO_DI_JAVA_OPTIONS% "-Dhttps.protocols=TLSv1,TLSv1.1,TLSv1.2" "-Djava.library.path=%LIBSPATH%" "-DKETTLE_HOME=%KETTLE_HOME%" "-DKETTLE_REPOSITORY=%KETTLE_REPOSITORY%" "-DKETTLE_USER=%KETTLE_USER%" "-DKETTLE_PASSWORD=%KETTLE_PASSWORD%" "-DKETTLE_PLUGIN_PACKAGES=%KETTLE_PLUGIN_PACKAGES%" "-DKETTLE_LOG_SIZE_LIMIT=%KETTLE_LOG_SIZE_LIMIT%" "-DKETTLE_JNDI_ROOT=%KETTLE_JNDI_ROOT%"
    to have an extra value on the end:
    Code:
    set OPT=%OPT% %PENTAHO_DI_JAVA_OPTIONS% "-Dhttps.protocols=TLSv1,TLSv1.1,TLSv1.2" "-Djava.library.path=%LIBSPATH%" "-DKETTLE_HOME=%KETTLE_HOME%" "-DKETTLE_REPOSITORY=%KETTLE_REPOSITORY%" "-DKETTLE_USER=%KETTLE_USER%" "-DKETTLE_PASSWORD=%KETTLE_PASSWORD%" "-DKETTLE_PLUGIN_PACKAGES=%KETTLE_PLUGIN_PACKAGES%" "-DKETTLE_LOG_SIZE_LIMIT=%KETTLE_LOG_SIZE_LIMIT%" "-DKETTLE_JNDI_ROOT=%KETTLE_JNDI_ROOT%" "-Duser.timezone=America/Chicago"
    Then the implied time (time received without a timezone parameter) will be in CST.

  5. #5

    Default

    I have just tried to run this SQL

    INSERT INTO db_amz.timestamps_complete
    (order_no, cutoff_time)
    VALUES('max100', '1970-01-01 16:00:00 -06:00');

    and that does exactly what I want.

    Then I have changed the date format in the Select Values step to

    yyyy-MM-dd HH:mm:ss.SSS -06:00

    to hard code the time zone and blanked out the time zone information.

    Then I have a Table Output to send it to the database and can see this in the PDI log:

    Table output.0 - Written row: [max202], [1970-01-01 16:00:00 -06:00]

    Checking in the database it comes back as

    16:00:00-05:00

    Isn't there any other way to fix this than changing the parameter when starting the JVM?
    PDI 7
    Postgres
    Windows 7

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

    Default

    If you blank out timezones, then it will take the timezone that PDI is running in... which (unless you add the Java Parameter) will be the timezone your PC is in, which is Eastern (UTC -5)

    The only other way I can think of is an UGLY hack:
    Convert your date/time into a string.
    Append "-06:00" (or -05:00 as is appropriate for date of year!)
    Convert your new string into a Date.

    What you need to keep in mind is that Java counts time in "# of Milliseconds since 1970-01-01 00:00:00 UTC" and having the string literal "-06:00" on your date won't adjust your time.

  7. #7

    Default

    Looks like we are going with adjusting the time zone on the database server and the start up option of the JVM.
    Thanks!
    PDI 7
    Postgres
    Windows 7

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

    Default

    Quote Originally Posted by Bobse View Post
    Looks like we are going with adjusting the time zone on the database server and the start up option of the JVM.
    Thanks!
    So... If the field in the DB is configured to support Timezone, then it shouldn't matter the timezone of the DB Server, as most interfaces will convert it to local time when it brings the data down to be modified...

  9. #9

    Default

    Probably but at least we are on the safe side if somebody runs a SQL with now() and hasn't specified the timezone for the JVM prior.
    PDI 7
    Postgres
    Windows 7

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.