从Solr DataImportHandler中的Oracle日期获取正确的时间 [英] Getting correct time from Oracle date in Solr DataImportHandler

问题描述

我正在尝试使用Solr的DataImportHandler来索引来自Oracle DB的一些文档，除了正确读取Oracle Date列到我的文档中之外，一切正常。

I'm trying to use Solr's DataImportHandler to index some documents from an Oracle DB, and everything is working fine with the exception of correctly reading an Oracle Date column into my document.

我的Solr架构中的字段定义为

I have the field in my Solr schema defined as

<field name="release_date" type="date" indexed="true" stored="true" multiValued="false"/>

我首先尝试在DataImportHandler中只做一个日期列的基本select语句，但是所有的使用不正确的时间值索引日期。例如，2004年1月12日09:28 AM（美国东部时间）在数据库中的日期被编入索引：

I first tried doing just a basic select statement of the date column in my DataImportHandler, but all of the dates are being indexed with incorrect time values. For instance, a date that is in the DB as Jan. 12, 2004 09:28 AM (EST) is being indexed as:

<date name="release_date">2004-01-12T05:00:00Z</date>

所有日期值都有正确的日期，但它们都有T05：00：00Z作为他们的时间。我最好的猜测是，它正在将数据库中的时间读取为午夜并将其转换为UTC。如果是这种情况，我希望正确的值读取T14：28：00Z。

All of the date values have the correct day, but they all have T05:00:00Z as their time. My best guess as to what is happening is that it's reading the time from the DB as midnight and converting it to UTC. If this is the case, I would expect the correct value to read T14:28:00Z.

为什么它没有拿起数据库列的时间部分？我知道DIH附带的日期变换器，但我是不完全清楚它应该如何工作。我也尝试过做

Why is it not picking up the time portion of the DB column? I know that there is a transformer for dates that comes with the DIH, but I'm not totally clear on how it's supposed to work. I also tried doing

<field column="RELEASE_DATE" name="release_date" dateTimeFormat="yyyy-MM-dd'T'hh:mm:ss'Z'" /> 

，但这似乎没有任何改变。

in the DIH, but that didn't seem to change anything.

推荐答案

以下是最后一个答案的完整代码（为了更加清晰）。

Here's the full code to go along with the last answer (for extra clarity).

在您的data-config.xml文件中，从DB读取日期并转换为时间戳：

In your data-config.xml file read the date from the DB and cast to a timestamp:

select cast(STRT_DT as timestamp) as STRT_DTTS from DATES

放入DataImportHandler实体，如下所示：

Put into an DataImportHandler entity, that looks like this:

<entity name="startDate" transformer="script:startDateTransform"
        query="select cast(STRT_DT as timestamp) as STRT_DTTS from DATES" >
    <field column="STRT_DTTS" name="STRT_DT" /> 
</entity>

此查询将返回oracle.sql.TIMESTAMP，但不会直接映射到日期。因此需要脚本转换器。因此我们引入了脚本：startDateTransform 。在相同的data-config.xml中，您可以像这样插入JavaScript：

This query will return a oracle.sql.TIMESTAMP, but it won't map directly to date. A script transformer is therefore required. Thus we introduce script:startDateTransform. In the same data-config.xml, you can insert JavaScript like so:

function startDateTransform(row){
    // Get the timestamp and convert it to a date
    var dateVal = row.get("STRT_DTTS").dateValue();

    // Put the correct date object into the original column
    row.put("STRT_DTTS", dateVal);

    return row;
}

这里我们将时间戳转换为日期，更新列值并返回使用新信息的行。

Here we convert the timestamp to a date, update the column value and return the row with the new information.

字段 STRT_DT ：

<field column="STRT_DTTS" name="STRT_DT" />

现在应包含正确的日期。

should now contain the correct date.

这篇关于从Solr DataImportHandler中的Oracle日期获取正确的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！