Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Calculator operation 'remove time from a date' fails on summertime

  1. #1
    Join Date
    Oct 2014
    Posts
    26

    Default Calculator operation 'remove time from a date' fails on summertime

    There is a bug using the operation 'remove time from a date' from the Calculator step on summertime dates.

    If the date specified was a day where the summertime started, the operation fails with a java.lang.IllegalArgumentException:

    2014/11/24 14:35:31 - remove_time.0 - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) : Unexpected error
    2014/11/24 14:35:31 - remove_time.0 - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) : java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1
    2014/11/24 14:35:31 - remove_time.0 - at java.util.GregorianCalendar.computeTime(Unknown Source)
    2014/11/24 14:35:31 - remove_time.0 - at java.util.Calendar.updateTime(Unknown Source)
    2014/11/24 14:35:31 - remove_time.0 - at java.util.Calendar.getTimeInMillis(Unknown Source)
    2014/11/24 14:35:31 - remove_time.0 - at java.util.Calendar.getTime(Unknown Source)
    2014/11/24 14:35:31 - remove_time.0 - at org.pentaho.di.core.Const.removeTimeFromDate(Const.java:2759)
    2014/11/24 14:35:31 - remove_time.0 - at org.pentaho.di.core.row.ValueDataUtil.removeTimeFromDate(ValueDataUtil.java:1026)
    2014/11/24 14:35:31 - remove_time.0 - at org.pentaho.di.trans.steps.calculator.Calculator.calcFields(Calculator.java:413)
    2014/11/24 14:35:31 - remove_time.0 - at org.pentaho.di.trans.steps.calculator.Calculator.processRow(Calculator.java:162)
    2014/11/24 14:35:31 - remove_time.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)

    I want to calculate the difference of days between two dates, it should ignore the hour time of the day of both dates.

    I tried to perform this using 'remove time from a date' operation on both A and B dates and after apllying the operation 'Date A - Date B (in days)'.
    However, I got this bug on summertime dates.

    Does someone suggest another solution?

    Orair.

  2. #2
    Join Date
    Mar 2013
    Posts
    127

    Default

    Hi

    i didnt exactly know what dates/which format your using, but you can refer attached example, which works fine

    Regards,
    Mateen
    Attached Files Attached Files

  3. #3
    Join Date
    Oct 2014
    Posts
    26

    Default

    Hi Mateen,
    Thanks for your collaboration.

    However, your example also fails if the dates specified are a day when the summertime starts.
    It happens because Pentaho try to set the HOUR_OF_DAY to 0 and the Date implementation on Java does not accept this operation for this dates because the day has not a zero hour (and throws an IllegalArgumentException).

    Using your example you can reproduce this bug when specifying '2013/10/20 12:34:16' for Date A or Date B and the user.timezone java environment variable set to 'America/Sao_Paulo'.

    I´ve reported this bug on JIRA (http://jira.pentaho.com/browse/PDI-13228).

    Anyway, for future references, if someone want to datediff on days two dates ignoring time,
    I solved this specific need using Formula step.

    So, I replaced the calculator operations on Calculator step by the formula 'DATEDIF([DateB];[DateA];"d")' inside a Formula step.

    Br,
    Orair.

  4. #4
    Join Date
    Mar 2013
    Posts
    127

    Default

    Hi Orair,

    i have tried to simulate exception which u r getting in #1, i have attached transformation for this which is working fine and below is my log.


    2014/12/04 04:39:53 - Spoon - Transformation opened.
    2014/12/04 04:39:53 - Spoon - Launching transformation [RemoveTimeFromDate]...
    2014/12/04 04:39:53 - Spoon - Started the transformation execution.
    2014/12/04 04:39:53 - RemoveTimeFromDate - Dispatching started for transformation [RemoveTimeFromDate]
    2014/12/04 04:39:53 - Transformation metadata - Natural sort of steps executed in 0 ms (5 time previous steps calculated)
    2014/12/04 04:39:53 - Get Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 04:39:53 - Write timezone.0 -
    2014/12/04 04:39:53 - Write timezone.0 - ------------> Linenr 1------------------------------
    2014/12/04 04:39:53 - Write timezone.0 - Here is timezone.
    2014/12/04 04:39:53 - Write timezone.0 -
    2014/12/04 04:39:53 - Write timezone.0 - timezone = America/Sao_Paulo
    2014/12/04 04:39:53 - Write timezone.0 -
    2014/12/04 04:39:53 - Write timezone.0 - ====================
    2014/12/04 04:39:53 - Write datediff.0 -
    2014/12/04 04:39:53 - Write datediff.0 - ------------> Linenr 1------------------------------
    2014/12/04 04:39:53 - Write datediff.0 - Here is just date.
    2014/12/04 04:39:53 - Write datediff.0 -
    2014/12/04 04:39:53 - Write datediff.0 - just_date = 2013/10/20 00:00:00.000
    2014/12/04 04:39:53 - Write datediff.0 -
    2014/12/04 04:39:53 - Write datediff.0 - ====================
    2014/12/04 04:39:53 - Data Grid.0 - Finished processing (I=0, O=0, R=0, W=1, U=0, E=0)
    2014/12/04 04:39:53 - Calculator.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 04:39:53 - Write timezone.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 04:39:53 - Write datediff.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 04:39:53 - Spoon - The transformation has finished!!


    as i want to simulate this. please let me know which kettle version ur using

    Regards,
    Mateen
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2014
    Posts
    26

    Default

    Hi Mateen,
    I found the bug using the 5.1.0 version.
    I have upgraded to 5.2.0.0 version and executed the RemoveTimeFromDate2.ktr and the bug can be reproduced on this version.

    The output was:

    2014/12/04 12:25:23 - Spoon - Pedindo repositório
    2014/12/04 12:25:23 - RepositoriesMeta - Reading repositories XML file: d:\Users\c050826\.kettle\repositories.xml
    2014/12/04 12:25:26 - Spoon - Connected to metastore : Remessa, added to delegating metastore
    2014/12/04 12:25:35 - Spoon - Transformation opened.
    2014/12/04 12:25:35 - Spoon - Launching transformation [bug_remove_time_from_a_date_summertime]...
    2014/12/04 12:25:35 - Spoon - Started the transformation execution.
    2014/12/04 12:25:37 - Spoon - The transformation has finished!!
    2014/12/04 12:37:45 - Spoon - Transformation opened.
    2014/12/04 12:37:45 - Spoon - Launching transformation [RemoveTimeFromDate2]...
    2014/12/04 12:37:45 - Spoon - Started the transformation execution.
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - Expedindo in�cio para transforma��o [RemoveTimeFromDate2]
    2014/12/04 12:37:45 - Write timezone.0 -
    2014/12/04 12:37:45 - Write timezone.0 - ------------> Linenr 1------------------------------
    2014/12/04 12:37:45 - Write timezone.0 - Here is timezone.
    2014/12/04 12:37:45 - Write timezone.0 -
    2014/12/04 12:37:45 - Write timezone.0 - timezone = America/Sao_Paulo
    2014/12/04 12:37:45 - Write timezone.0 -
    2014/12/04 12:37:45 - Write timezone.0 - ====================
    2014/12/04 12:37:45 - Get Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 12:37:45 - Data Grid.0 - Finished processing (I=0, O=0, R=0, W=1, U=0, E=0)
    2014/12/04 12:37:45 - Calculator.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erro inesperado
    2014/12/04 12:37:45 - Calculator.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1
    2014/12/04 12:37:45 - Calculator.0 - at java.util.GregorianCalendar.computeTime(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at java.util.Calendar.updateTime(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at java.util.Calendar.getTimeInMillis(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at java.util.Calendar.getTime(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.core.Const.removeTimeFromDate(Const.java:2764)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.core.row.ValueDataUtil.removeTimeFromDate(ValueDataUtil.java:1026)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.trans.steps.calculator.Calculator.calcFields(Calculator.java:413)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.trans.steps.calculator.Calculator.processRow(Calculator.java:162)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2014/12/04 12:37:45 - Calculator.0 - at java.lang.Thread.run(Unknown Source)
    2014/12/04 12:37:45 - Write timezone.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 12:37:45 - Calculator.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - Transforma��o detectada
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - Transforma��o est� matando os outros steps!
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!
    2014/12/04 12:37:45 - Spoon - The transformation has finished!!
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!


    Probably the options on timezone (dstSavings and useDaylight) are different in our environments.

    Br,
    Orair.

  6. #6
    Join Date
    Apr 2015
    Posts
    2

    Lightbulb Start Solution

    Hi Everyone,

    I had this same problem and found that the error was cause by a problem with timezone at Singapure in 1982 as it says in http://bukhantsov.org/2013/02/removi...fails-in-java/ and http://stackoverflow.com/questions/1...inute-for-asia.

    So what I did was to filter the table input SQL, in my case, using MySQL, just correct the SQL from my Table Input using as below:

    Code:
    SELECT 
      ID,
      DATE(ENTRY_DATE)
    FROM
      TABLE;
    Hope it will be helpful for someone.

    Thanks!

    Quote Originally Posted by orair View Post
    Hi Mateen,
    I found the bug using the 5.1.0 version.
    I have upgraded to 5.2.0.0 version and executed the RemoveTimeFromDate2.ktr and the bug can be reproduced on this version.

    The output was:

    2014/12/04 12:25:23 - Spoon - Pedindo repositório
    2014/12/04 12:25:23 - RepositoriesMeta - Reading repositories XML file: d:\Users\c050826\.kettle\repositories.xml
    2014/12/04 12:25:26 - Spoon - Connected to metastore : Remessa, added to delegating metastore
    2014/12/04 12:25:35 - Spoon - Transformation opened.
    2014/12/04 12:25:35 - Spoon - Launching transformation [bug_remove_time_from_a_date_summertime]...
    2014/12/04 12:25:35 - Spoon - Started the transformation execution.
    2014/12/04 12:25:37 - Spoon - The transformation has finished!!
    2014/12/04 12:37:45 - Spoon - Transformation opened.
    2014/12/04 12:37:45 - Spoon - Launching transformation [RemoveTimeFromDate2]...
    2014/12/04 12:37:45 - Spoon - Started the transformation execution.
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - Expedindo in�cio para transforma��o [RemoveTimeFromDate2]
    2014/12/04 12:37:45 - Write timezone.0 -
    2014/12/04 12:37:45 - Write timezone.0 - ------------> Linenr 1------------------------------
    2014/12/04 12:37:45 - Write timezone.0 - Here is timezone.
    2014/12/04 12:37:45 - Write timezone.0 -
    2014/12/04 12:37:45 - Write timezone.0 - timezone = America/Sao_Paulo
    2014/12/04 12:37:45 - Write timezone.0 -
    2014/12/04 12:37:45 - Write timezone.0 - ====================
    2014/12/04 12:37:45 - Get Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 12:37:45 - Data Grid.0 - Finished processing (I=0, O=0, R=0, W=1, U=0, E=0)
    2014/12/04 12:37:45 - Calculator.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erro inesperado
    2014/12/04 12:37:45 - Calculator.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1
    2014/12/04 12:37:45 - Calculator.0 - at java.util.GregorianCalendar.computeTime(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at java.util.Calendar.updateTime(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at java.util.Calendar.getTimeInMillis(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at java.util.Calendar.getTime(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.core.Const.removeTimeFromDate(Const.java:2764)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.core.row.ValueDataUtil.removeTimeFromDate(ValueDataUtil.java:1026)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.trans.steps.calculator.Calculator.calcFields(Calculator.java:413)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.trans.steps.calculator.Calculator.processRow(Calculator.java:162)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2014/12/04 12:37:45 - Calculator.0 - at java.lang.Thread.run(Unknown Source)
    2014/12/04 12:37:45 - Write timezone.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 12:37:45 - Calculator.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - Transforma��o detectada
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - Transforma��o est� matando os outros steps!
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!
    2014/12/04 12:37:45 - Spoon - The transformation has finished!!
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!


    Probably the options on timezone (dstSavings and useDaylight) are different in our environments.

    Br,
    Orair.

  7. #7
    Join Date
    Apr 2015
    Posts
    2

    Default

    Hello everyone,

    I had this same problem and it seems that the bug comes from a date of Dec. 31st, 1982 when changed the timezone in Singapure... as it shows here: http://bukhantsov.org/2013/02/removi...fails-in-java/ and here: http://stackoverflow.com/questions/1...01843#29801843.

    So, in order the have a side solution in the forum (for newbies like me) follow below how I fixed.

    In the Object Table Input, I am using MySQL, I got the input SQL as below:

    Code:
    SELECT
     id,
     name,
     date(admission_date)
    FROM
     employees;
    Hope will be helpful for someone.

    Thanks!


    Quote Originally Posted by orair View Post
    Hi Mateen,
    I found the bug using the 5.1.0 version.
    I have upgraded to 5.2.0.0 version and executed the RemoveTimeFromDate2.ktr and the bug can be reproduced on this version.

    The output was:

    2014/12/04 12:25:23 - Spoon - Pedindo repositório
    2014/12/04 12:25:23 - RepositoriesMeta - Reading repositories XML file: d:\Users\c050826\.kettle\repositories.xml
    2014/12/04 12:25:26 - Spoon - Connected to metastore : Remessa, added to delegating metastore
    2014/12/04 12:25:35 - Spoon - Transformation opened.
    2014/12/04 12:25:35 - Spoon - Launching transformation [bug_remove_time_from_a_date_summertime]...
    2014/12/04 12:25:35 - Spoon - Started the transformation execution.
    2014/12/04 12:25:37 - Spoon - The transformation has finished!!
    2014/12/04 12:37:45 - Spoon - Transformation opened.
    2014/12/04 12:37:45 - Spoon - Launching transformation [RemoveTimeFromDate2]...
    2014/12/04 12:37:45 - Spoon - Started the transformation execution.
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - Expedindo in�cio para transforma��o [RemoveTimeFromDate2]
    2014/12/04 12:37:45 - Write timezone.0 -
    2014/12/04 12:37:45 - Write timezone.0 - ------------> Linenr 1------------------------------
    2014/12/04 12:37:45 - Write timezone.0 - Here is timezone.
    2014/12/04 12:37:45 - Write timezone.0 -
    2014/12/04 12:37:45 - Write timezone.0 - timezone = America/Sao_Paulo
    2014/12/04 12:37:45 - Write timezone.0 -
    2014/12/04 12:37:45 - Write timezone.0 - ====================
    2014/12/04 12:37:45 - Get Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 12:37:45 - Data Grid.0 - Finished processing (I=0, O=0, R=0, W=1, U=0, E=0)
    2014/12/04 12:37:45 - Calculator.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erro inesperado
    2014/12/04 12:37:45 - Calculator.0 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1
    2014/12/04 12:37:45 - Calculator.0 - at java.util.GregorianCalendar.computeTime(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at java.util.Calendar.updateTime(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at java.util.Calendar.getTimeInMillis(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at java.util.Calendar.getTime(Unknown Source)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.core.Const.removeTimeFromDate(Const.java:2764)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.core.row.ValueDataUtil.removeTimeFromDate(ValueDataUtil.java:1026)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.trans.steps.calculator.Calculator.calcFields(Calculator.java:413)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.trans.steps.calculator.Calculator.processRow(Calculator.java:162)
    2014/12/04 12:37:45 - Calculator.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2014/12/04 12:37:45 - Calculator.0 - at java.lang.Thread.run(Unknown Source)
    2014/12/04 12:37:45 - Write timezone.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2014/12/04 12:37:45 - Calculator.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - Transforma��o detectada
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - Transforma��o est� matando os outros steps!
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!
    2014/12/04 12:37:45 - Spoon - The transformation has finished!!
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!
    2014/12/04 12:37:45 - RemoveTimeFromDate2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Erros detectados!


    Probably the options on timezone (dstSavings and useDaylight) are different in our environments.

    Br,
    Orair.

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.