Calculator Step: Unparseable Date Problem

[Ryan LaPlante]

We run PDI 3.1.0-826 and have a transform that reads csv files that have names of the form: name.log.yyyy-mm-dd-hh -- there is a log file for each hour of each day in each set of logs.

Today I had a failure (see below) in the transform on a calculator step that converts the file extension into a date type using the conversion mask: 'yyyy-MM-dd-HH'.

This is the same transform that's been running for months, no changes. The log files have not changed in name format (or content).

I was stumped until a colleague pointed out that this file is from the hour of the spring ahead time change that occured this past weekend. As odd as it sounded, I couldn't ignore the coincidence so I stuffed all the data from the 2010-03-14-02 file into the 2010-03-14-01 file, deleted the -02 and reran the transform successfully on data files before and after the -02 hour.

Perhaps it's just a coincidence, but it certainly looks like the time change was related to my problem. Any thoughts?

Thank you,
/Ryan

Text from error output:

2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : !Calculator.ErrorInStepRunning :
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : result String : couldn't convert string [2010-03-14-02] to a date using format [yyyy-MM-dd-HH]
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Unparseable date: "2010-03-14-02"
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : !
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Unexpected error :
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : org.pentaho.di.core.exception.KettleStepException:
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Error while running the step
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : result String : couldn't convert string [2010-03-14-02] to a date using format [yyyy-MM-dd-HH]
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Unparseable date: "2010-03-14-02"
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.calculator.Calculator.p rocessRow(Calculator.java:184)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.step.BaseStep.runStepThread(B aseStep.java:2664)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.calculator.Calculator.r un(Calculator.java:524)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Caused by: org.pentaho.di.core.exception.KettleValueException :
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : result String : couldn't convert string [2010-03-14-02] to a date using format [yyyy-MM-dd-HH]
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Unparseable date: "2010-03-14-02"
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.row.ValueMeta.convertStringToD ate(ValueMeta.java:594)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.row.ValueMeta.getDate(ValueMet a.java:1624)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.row.ValueMeta.convertData(Valu eMeta.java:3005)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.calculator.Calculator.c alcFields(Calculator.java:496)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.calculator.Calculator.p rocessRow(Calculator.java:165)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ... 2 more
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Caused by: java.text.ParseException: Unparseable date: "2010-03-14-02"
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at java.text.DateFormat.parse(DateFormat.java:337)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.row.ValueMeta.convertStringToD ate(ValueMeta.java:589)
2010/03/17 22:08:35 - Calc Dates.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ... 6 more

[gutlez]

Time doesn't exist....

The clock goes:
2010-03-14 01:58
2010-03-14 01:59
2010-03-14 03:00
2010-03-14 03:01

So... You don't have a date of "2010-03-14 02:00" Thus the parse fails.

[Ryan LaPlante]

Thank you for the quick response!

I think 02:00 does exist if you're in UTC, or a timezone that doesn't observe daylight savings, right? Is the code aware of the timezone of the kettle box and deciding whether or not 02:00 exists based on that?

I know our kettle box and the app server that is creating these logfiles are both currently on EST and are automatically changing. This is something we intend to change - do you know if we can expect that change to prevent this failure next year? (take your time, got a whole year to figure it out! )

Thanks!
/Ryan

[gutlez]

It is aware of the timezone it's in, but I forget how.