You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by cyang2010 <ys...@hotmail.com> on 2011/03/02 03:21:24 UTC

indexing mysql dateTime/timestamp into solr date field

Hi,

I can't seem to be able to index to a solr date field from a query result
using DataImportHandler.  Anyone else know how to resoleve the problem?

        <entity name="title" 
        		query="select ID,  title_full as TITLE_NAME, YEAR,
COUNTRY_OF_ORIGIN,  modified as RELEASE_DATE from title limit 10">
        		
            <field column="ID" name="id" />
        
            <field column="TITLE_NAME" name="title_name" />
            
            <field column="YEAR" name="year" />
            <field column="COUNTRY_OF_ORIGIN" name="country" />

            <field column="RELEASE_DATE" name="release_date" />

When i check the solr document, there is no term populated for release_date
field.  All other fields are populated with terms.

The field, "release_date" is a solr date type field.


Appreciate your help.


-- 
View this message in context: http://lucene.472066.n3.nabble.com/indexing-mysql-dateTime-timestamp-into-solr-date-field-tp2608327p2608327.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: indexing mysql dateTime/timestamp into solr date field

Posted by cyang2010 <ys...@hotmail.com>.
Bill,

I did try to use the way you suggested above.  Unfortunately it does not
work either.

It is pretty much the same as my last reply, except the
dateTimeFormat="yyyy-MM-dd'T'hh:mm:ss"

Thanks,

cyang

--
View this message in context: http://lucene.472066.n3.nabble.com/indexing-mysql-dateTime-timestamp-into-solr-date-field-tp2608327p2609053.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: indexing mysql dateTime/timestamp into solr date field

Posted by William Bell <bi...@gmail.com>.
<field column="date" dateTimeFormat="yyyy-MM-dd'T'hh:mm:ss" />

Did you convert the date to standard GMT format as above in DIH?

Also add transformer="DateFormatTransformer,..."

http://lucene.apache.org/solr/api/org/apache/solr/schema/DateField.html



On Tue, Mar 1, 2011 at 7:54 PM, cyang2010 <ys...@hotmail.com> wrote:
> Yes, I am pretty sure every row has a modified field.   I did my testing
> before posting question.
>
> I tried with adding DateFormatTransformer, still not help.
>
>
>        <entity name="title"
>                        query="select ID,  title_full as TITLE_NAME, YEAR,
> COUNTRY_OF_ORIGIN,  modified as RELEASE_DATE from title limit 10"
>
>
> transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
>
>            <field column="ID" name="id" />
>
>            <field column="TITLE_NAME" name="title_name" />
>
>            <field column="YEAR" name="year" />
>            <field column="COUNTRY_OF_ORIGIN" name="country" />
>
>            <field column="RELEASE_DATE" name="release_date"
> dateTimeFormat="yyyy-MM-dd"/>
>
> I assume it is ok to just get the date part of the information out of a
> datetime field?
>
> Any thought on this?
>
> --
> View this message in context: http://lucene.472066.n3.nabble.com/indexing-mysql-dateTime-timestamp-into-solr-date-field-tp2608327p2608452.html
> Sent from the Solr - User mailing list archive at Nabble.com.
>

Re: indexing mysql dateTime/timestamp into solr date field

Posted by cyang2010 <ys...@hotmail.com>.
Yes, I am pretty sure every row has a modified field.   I did my testing
before posting question.

I tried with adding DateFormatTransformer, still not help.


        <entity name="title" 
        		query="select ID,  title_full as TITLE_NAME, YEAR,
COUNTRY_OF_ORIGIN,  modified as RELEASE_DATE from title limit 10"
        		
        	
transformer="RegexTransformer,DateFormatTransformer,TemplateTransformer">
        		
            <field column="ID" name="id" />
        
            <field column="TITLE_NAME" name="title_name" />
            
            <field column="YEAR" name="year" />
            <field column="COUNTRY_OF_ORIGIN" name="country" />

            <field column="RELEASE_DATE" name="release_date"
dateTimeFormat="yyyy-MM-dd"/>

I assume it is ok to just get the date part of the information out of a
datetime field?

Any thought on this?

-- 
View this message in context: http://lucene.472066.n3.nabble.com/indexing-mysql-dateTime-timestamp-into-solr-date-field-tp2608327p2608452.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: indexing mysql dateTime/timestamp into solr date field

Posted by Chris Hostetter <ho...@fucit.org>.
:         		query="select ID,  title_full as TITLE_NAME, YEAR,
: COUNTRY_OF_ORIGIN,  modified as RELEASE_DATE from title limit 10">

Are you certian that the first 10 results returned (you have "limit 10") 
all have a value in the "modified" field?

if modified is nullable you could very easily just happen to be getting 10 
docs that don't have values in that field.


-Hoss

Re: indexing mysql dateTime/timestamp into solr date field

Posted by cyang2010 <ys...@hotmail.com>.
It turn out you don't need to use dateFormatTransformer at all.  The reason
why the timestamp mysql column fail to be inserted to solr is because in
schema.xml i mistakenly set "index=false,  stored=false".  Of course that
won't make it come to index at all.  No wonder schema browser always show no
term for that field.

DataImportHanlder just take care of using jdbc to read timestamp/datetime
column, and format it into solr format out of box.  There is no need to use
any transformer on top it.  I am guessing that the DateFormatTransformer is
only needed for string value (somevalue from a xml source, rather than for
database column value)  to convert it to solr date type.  

--
View this message in context: http://lucene.472066.n3.nabble.com/indexing-mysql-dateTime-timestamp-into-solr-date-field-tp2608327p2611865.html
Sent from the Solr - User mailing list archive at Nabble.com.