datetime field in solr schema

3
votes

I am using Solr to make search on my database, and i need to get a field with (datetime) data type from mysql database, so i set the select query to get this field in dataconfig.xml, and i added field name for this field in schema.xml as follow:

<field name="parsed_at" type="datetime" indexed="true" stored="true" />

but solr didn't do any indexing after these modifications. i have tried to

select date(parsed_at) as parsed_at ...

and to specify type as date in schema.xml, but this didn't work for unknown reason
does solr support any data type compatible with datatime or timestamp in mysql??

2
mysqldatetimesolr

2 Answers

6
votes

Solr is compatible with typical MySql date formats in an MySQL table, like Y-m-d (and so on) If parsed_at column in MySQL is from type datetime, so you don't need any modifications on select time, as long your field name(solr)=cloumn name(MySQL).

So try select parsed_at from .... Solr will process the date

<field name="parsed_at" type="datetime" indexed="true" stored="true" />

Be sure, that an fieldType (type=)datetime in Solr's schema.xml exits. If it is so, please post the configuration here. If not, check if, the following line exits in schema.xml, which is default:

<fieldType name="tdate" class="solr.TrieDateField" omitNorms="true" precisionStep="6" positionIncrementGap="0"/>

Field type tdate can handle date-formats. If you have this field definition in your schema.xml, your parsed_at field could use that type:

<field name="parsed_at" type="tdate" indexed="true" stored="true" />
2
votes

Are you using the DataImportHandler? In that case you will need to tell SOLR how the date is stored. You do this using the DateFormatTransformertransformer:

<dataConfig>
    <dataSource
      type='JdbcDataSource' 
      driver='com.mysql.jdbc.Driver' 
      url='jdbc:mysql://localhost/DATABASE?zeroDateTimeBehavior=convertToNull'
      batchSize='-1' 
      user='USER' 
      password='PASS'/>
    <document name='objects'>
        <entity name='object' pk='id' transformer='DateFormatTransformer' query='BLA' >
    <field column='id' />
            <field column='created' dateTimeFormat='yyyy-MM-dd HH:mm:ss' />
        </entity>
    </document>
</dataConfig>

Note that I stuck ?zeroDateTimeBehavior=convertToNullbehind the url. This prevents errors with empty dates.