6

I'm using Pentaho Data Integration (Table Input step) to pull in data from a MySQL server. A couple of fields are of the type 'Timestamp', and Pentaho keeps spewing out errors because of the timestamp being NULL (0000-00-00 00:00:00.000000).

I added a zeroDateTimeBehavior=convertToNull to the parameters which should take care of the bad timestamps, but it's converting all of my Timestamp data to NULL.

One reason why I think it may be happening is because some of my 'good' data is represented as, for example, 2013-03-14 04:55:09.000000. While most of the date is 'good data', the fractional seconds (.000000) might be being interpreted as 'bad' data by the zeroDateTimeBehavior parameter, and is thus being converted to NULL.

I'm not sure if this is why it's happening, but could someone give me some insight as to other probable causes and how in the world I can stop this? Perhaps converting to a string might be a good idea, but I have no idea how to go about doing this.

Suggestions?

Dezzie
  • 934
  • 3
  • 18
  • 35

1 Answers1

2

Based on this answer, I'd probably change the data type of the field to datetime rather than timestamp. If you don't you'll have to do the conversion in the SQL that reads the data. The previous answer gives examples of how to do that.

Community
  • 1
  • 1
Brian.D.Myers
  • 2,448
  • 2
  • 20
  • 17
  • How can I do the conversion in the SQL? Can I use the CONVERT function? – Dezzie Mar 28 '14 at 01:25
  • Look at the example in the link. They do some conversions there. Also see the [docs](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html). You'll probably want to use the FROM_UNIXTIME function, but you might have to do some reformatting. See [this](http://stackoverflow.com/questions/5362874/how-to-convert-timestamp-to-datetime-in-mysql) answer for further examples. Sorry I'm not a MySQL guy. You'll have to play around a bit, or ask a separate question. – Brian.D.Myers Mar 28 '14 at 20:31