JDBC将Timestamp转换为NULL（zeroDateTimeBehavior问题） [英] JDBC converting Timestamp to NULL (zeroDateTimeBehavior issue)

问题描述

我正在使用Pentaho数据集成（表输入步骤）从MySQL服务器提取数据。一些字段属于'Timestamp'类型，Pentaho因为时间戳为NULL（00：00：00.000000）而不断喷出错误。

<我将 zeroDateTimeBehavior = convertToNull 添加到应该处理错误时间戳的参数中，但它将我的Timestamp数据的所有转换为NULL 。

我认为可能发生的一个原因是因为我的一些好数据被表示为，例如，2013-03-14 04:55： 09.000000。虽然大部分日期都是好数据，但小数秒（.000000）可能被zeroDateTimeBehavior参数解释为坏数据，因此被转换为NULL。

我不确定这是不是为什么会发生这种情况，但是有人可以给我一些关于其他可能原因的见解以及世界上我如何阻止这种情况？也许转换成字符串可能是一个好主意，但我不知道如何去做。

建议？

解决方案

基于这个答案，我' d可能会将字段的数据类型更改为 datetime 而不是 timestamp 。如果不这样做，则必须在读取数据的SQL中进行转换。上一个答案给出了如何做到这一点的例子。

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?

解决方案

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.

这篇关于JDBC将Timestamp转换为NULL（zeroDateTimeBehavior问题）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！