Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: PDI table input step problems with daylight saving time !!??

  1. #1
    Join Date
    Jan 2011
    Posts
    16

    Default PDI table input step problems with daylight saving time !!??

    Hi all,

    I've got a mysterious problem with my table input step in PDI 3.2 (verified also in 4.1).

    The times between 02:00 and 03:00 on 27.03.2011 appears in PDI one hour earlier !?!?!

    I do a simple query on a table (<select.txt>).
    You can see the result of the table input preview in <PDI_table_input_preview.txt>
    This result set is ordered on column rda_timestamp. But the times between 2 and 3 have lost one hour. They are in the right order but have times between 1 and 2 !?
    Same result set in an Excel file (records are marked) <PDI_Table_Input_Result.xls> .
    The result of the query, done with a DB-tool (records are marked, too) <DB_SQL_Result.xls>.

    Is there something wrong with my configuration (PDI, Oracle DB) ?
    Is there a problem with this step ?

    Any hint to solve this problem would be highly appreciated.

    Thank you

    Armin
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2009
    Posts
    687

    Default

    On that day time was change to summer time. So in many countries there was no time between 02:00 and 03:00. So perhaps this is the reason.

    Johan

  3. #3
    Join Date
    Jan 2011
    Posts
    16

    Default

    Hi Johan,

    sure, this is the reason.
    but why does this PDI step make 01:04:48 out of 02:04:48.
    I would have expected an conversion to 03:04:48 but not one hour earlier.
    btw. there should be no change of the date and time when I read a date column from an Oracle DB and write it without any conversion in another date column in an Oracle DB. The time is 02:04:48. If I want the local time I do a conversion (as you can see in the query statement).
    It is also very astonishing that the time in the converted column is correct (UTC 02:04:48 is in CEST 04:04:48).



    Armin

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

    Default

    Just to be clear on this matter: PDI does not change any time. It is delivered like that to us by the JDBC driver in question.
    Searching for Oracle JDBC related help might give you a better idea.

  5. #5
    Join Date
    Jan 2011
    Posts
    16

    Default

    Hi Matt,

    I made a query with the same JDBC connection as PDI does (with SQL Developer).
    ==> jdbcracle:thin:@192.xxx.xx.xx:1521:dwh
    The result was correct !

    So i exported that rows to Excel an made a "excel input" step in PDI.
    No I get the time 03:04:48 from the source 02:04:48 !!!
    The result of the preview of this step is attached.
    Is there another JDBC to check ?

    please help the really confused

    Armin
    Attached Files Attached Files

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

    Default

    What Johan said above was correct. The time "2011-03-27 02:04:48" is not a legal time in many countries (including mine) because the third hour was skipped from 02 to 03.
    You could obviously consider setting a different time-zone for your computer, your user or even in Kettle. For example, in Kettle you can set the default Java timezone in a job with a JavaScript job entry:
    java.util.TimeZone.setDefault( java.util.TimeZone.getTimeZone("EST") );
    Transformations and jobs executed after that job entry will be using the new time zone.
    Please note that doing so will simply expose a different unavailable hour. If the data in your database is stored in UTC, you can set that too. Just make sure that you know which time-zone the source data is using!

    Matt

    P.S. I guess Excel really isn't all that smart :-)

  7. #7
    Join Date
    Jan 2011
    Posts
    16

    Default

    So Java does something with my dates.
    The Parameter (java.util.TimeZone.getDefault();) looks like this

    sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=143,lastRule=java.util.SimpleTimeZone[id=Europe/Berlin,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]]

    And this makes something weird with "non-legal" dates.

    So I will place a "Modified Java Script Value" - Step on the starting point of my jobs to guarantee correct values for this session. ==> java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("UTC"));

    thanks for the hints to find the way out of this mystery (but I even do not know why he writes one hour backwards !?!?!) :-)

    Armin

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

    Default

    I didn't mean the "Modified Java Script Value" step, rather the "JavaScript" job entry and feel free to ignore my other comments as well.

  9. #9
    Join Date
    Jan 2011
    Posts
    16

    Default

    sorry Matt,

    I'll place the Java Code in the first job and use the JavaScript job entry.
    And please give me a hint which comments I've ignored ?!

    Armin

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

    Default

    You need to know in which time-zone the dates are stored in the database. Otherwise you will always see a time-change when you convert to another timezone.

Tags for this Thread

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.