Storing MYSQL DATETIME field in solr as String

[manju16832003]

Hi All,
In database, MYSQL DateTime format is 2013-10-17 15:32:48 [yyyy-MM-dd
hh:mm:ss] however, when I store this DateTime field in solr as String, it
would look something like this 2013-10-17 15:32:48*.0*.
Why the 0 is appended at the end of the DateTime?

For the same date field, I do have another field in solr that stores date as
UTC. The reason I'm storing date in String with different field to display
user as it appears in MySQL database.

But when DateTime stored as String in solr, it appends .0 end of the date.
Is it the behaviour of Solr? is there a reason behind that?

Thanks



--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836.html
Sent from the Solr - User mailing list archive at Nabble.com.

[Shalin Shekhar Mangar]

This is not a Solr specific problem. The java.sql.Timestamp class
which is what you get for a DateTime field contains a nano-second
component. This shows up as a .0 when you do a toString() conversion.
If you want to show this values to your client in a diffferent or
mysql-specific format, you can format the date value at display time.
Alternately, you can use the DateFormatTransformer to convert the date
into a string format of your choice before storing it into Solr.

See http://wiki.apache.org/solr/DataImportHandler#DateFormatTransformer

Post by manju16832003
Hi All,
In database, MYSQL DateTime format is 2013-10-17 15:32:48 [yyyy-MM-dd
hh:mm:ss] however, when I store this DateTime field in solr as String, it
would look something like this 2013-10-17 15:32:48*.0*.
Why the 0 is appended at the end of the DateTime?
For the same date field, I do have another field in solr that stores date as
UTC. The reason I'm storing date in String with different field to display
user as it appears in MySQL database.
But when DateTime stored as String in solr, it appends .0 end of the date.
Is it the behaviour of Solr? is there a reason behind that?
Thanks
--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836.html
Sent from the Solr - User mailing list archive at Nabble.com.

[manju16832003]

Hi Shekhar,
Thanks for replying :-).
Yes your answer is justifiable.
DateFormatTransformer was a good consideration. However it doesn't out the
format I expected. Rather it outputs in the following manner

<str name="publish_date">Tue Jun 05 00:00:00 MYT 2012</str>

Here is DIH configuration
<field name="publish_date" column="publish_date" xpath="/RDF/item/date"
dateTimeFormat="yyyy-MM-dd'T'HH:mm:ss" locale="en"/>

And also I tried this way

<field name="publish_date" column="publish_date" xpath="/RDF/item/date"
dateTimeFormat="yyyy-MM-dd HH:mm:ss" locale="en"/>*-- without having 'T'*

Output is the same :-)

I expected date format to be *yyyy-MM-dd HH:mm:ss*





--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4106849.html
Sent from the Solr - User mailing list archive at Nabble.com.

[Shalin Shekhar Mangar]

Post by manju16832003
Hi Shekhar,
Thanks for replying :-).
Yes your answer is justifiable.
DateFormatTransformer was a good consideration. However it doesn't out the
format I expected. Rather it outputs in the following manner
<str name="publish_date">Tue Jun 05 00:00:00 MYT 2012</str>
Here is DIH configuration
<field name="publish_date" column="publish_date" xpath="/RDF/item/date"
dateTimeFormat="yyyy-MM-dd'T'HH:mm:ss" locale="en"/>
And also I tried this way
<field name="publish_date" column="publish_date" xpath="/RDF/item/date"
dateTimeFormat="yyyy-MM-dd HH:mm:ss" locale="en"/>*-- without having 'T'*
Output is the same :-)
I expected date format to be *yyyy-MM-dd HH:mm:ss*

That is unlikely. It won't give the same output. Did you re-index
after changing the config? Did you specify the DateFormatTransformer
on the <entity> too?

[manju16832003]

Hi Shekhar,
Yes, I did specify transformer="DateFormatTransformer" in <entity > and I
did re-index :-(.

Bit strange output.

Even I tried to change the Data type of the data field in schema.xml as
well.
I have tried with changing date type from String to Date and Date to String
by applying DateFormatTransformer. No luck :-(.



--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4106852.html
Sent from the Solr - User mailing list archive at Nabble.com.

[manju16832003]

OK here are the scenarios I tried.

*Scenario - 1: *

dih.xml (aka data-config.xml)

<entity dataSource="solr" name="listing" query="..."
transformer="DateFormatTransformer">

<field name=&quot;publish_date&quot; column=&quot;publish_date&quot;
xpath=&quot;/RDF/item/date&quot;
dateTimeFormat=&quot;&lt;b>yyyy-MM-dd'T'HH:mm:ss*" locale="en"/>

schema.xml
<field name="publish_date" type="date" indexed="true" stored="true"
multiValued="false" default="NOW" />

It does not work. Throws an following exception
WARN org.apache.solr.handler.dataimport.DateFormatTransformer – Could not
parse a Date field
java.text.ParseException: Unparseable date: "2013-12-05 15:40:03.0"

*Scenario - 2*
dih.xml (aka data-config.xml)

<entity dataSource="solr" name="listing" query="..."
transformer="DateFormatTransformer">

<field name=&quot;publish_date&quot; column=&quot;publish_date&quot;
xpath=&quot;/RDF/item/date&quot; dateTimeFormat=&quot;&lt;b>yyyy-MM-dd
HH:mm:ss*" locale="en"/>

schema.xml
<field name="publish_date" type="date" indexed="true" stored="true"
multiValued="false" default="NOW" />

Output is
<date name="publish_date">2012-12-05T07:38:27Z</date>

*Scenario - 3*
dih.xml (aka data-config.xml)

<entity dataSource="solr" name="listing" query="..."
transformer="DateFormatTransformer">

<field name=&quot;publish_date&quot; column=&quot;publish_date&quot;
xpath=&quot;/RDF/item/date&quot; dateTimeFormat=&quot;&lt;b>yyyy-MM-dd
HH:mm:ss*" locale="en"/>

schema.xml
<field name="publish_date" type="string" indexed="true" stored="true"
multiValued="false" default="NOW" />

Output is
<str name="publish_date">Thu Dec 05 15:38:27 MYT 2013</str>

My expectation was *2012-12-05 07:38:27* (MySQL DateTime format)



--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4106854.html
Sent from the Solr - User mailing list archive at Nabble.com.

[Shalin Shekhar Mangar]

I'm sorry. I thought you wanted to parse a date stored as string into
a java.util.Date. Clearly, you are trying to go the other way round.

There's nothing in DIH which will convert a mysql date to a string in
specific format. You will need to write a custom transformer either in
javascript or in java to do this.

http://wiki.apache.org/solr/DIHCustomTransformer
http://wiki.apache.org/solr/DataImportHandler#ScriptTransformer

Post by manju16832003
OK here are the scenarios I tried.
*Scenario - 1: *
dih.xml (aka data-config.xml)
<entity dataSource="solr" name="listing" query="..."
transformer="DateFormatTransformer">
<field name=&quot;publish_date&quot; column=&quot;publish_date&quot;
xpath=&quot;/RDF/item/date&quot;
dateTimeFormat=&quot;&lt;b>yyyy-MM-dd'T'HH:mm:ss*" locale="en"/>
schema.xml
<field name="publish_date" type="date" indexed="true" stored="true"
multiValued="false" default="NOW" />
It does not work. Throws an following exception
WARN org.apache.solr.handler.dataimport.DateFormatTransformer – Could not
parse a Date field
java.text.ParseException: Unparseable date: "2013-12-05 15:40:03.0"
*Scenario - 2*
dih.xml (aka data-config.xml)
<entity dataSource="solr" name="listing" query="..."
transformer="DateFormatTransformer">
<field name=&quot;publish_date&quot; column=&quot;publish_date&quot;
xpath=&quot;/RDF/item/date&quot; dateTimeFormat=&quot;&lt;b>yyyy-MM-dd
HH:mm:ss*" locale="en"/>
schema.xml
<field name="publish_date" type="date" indexed="true" stored="true"
multiValued="false" default="NOW" />
Output is
<date name="publish_date">2012-12-05T07:38:27Z</date>
*Scenario - 3*
dih.xml (aka data-config.xml)
<entity dataSource="solr" name="listing" query="..."
transformer="DateFormatTransformer">
<field name=&quot;publish_date&quot; column=&quot;publish_date&quot;
xpath=&quot;/RDF/item/date&quot; dateTimeFormat=&quot;&lt;b>yyyy-MM-dd
HH:mm:ss*" locale="en"/>
schema.xml
<field name="publish_date" type="string" indexed="true" stored="true"
multiValued="false" default="NOW" />
Output is
<str name="publish_date">Thu Dec 05 15:38:27 MYT 2013</str>
My expectation was *2012-12-05 07:38:27* (MySQL DateTime format)
--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4106854.html
Sent from the Solr - User mailing list archive at Nabble.com.

[Raymond Wiker]

To me, the obvious way of doing this would be to CAST the DATETIME to
CHAR(n), or (probably better) use DATE_FORMAT().


On Tue, Dec 17, 2013 at 5:21 AM, Shalin Shekhar Mangar <

Post by Shalin Shekhar Mangar
I'm sorry. I thought you wanted to parse a date stored as string into
a java.util.Date. Clearly, you are trying to go the other way round.
There's nothing in DIH which will convert a mysql date to a string in
specific format. You will need to write a custom transformer either in
javascript or in java to do this.
http://wiki.apache.org/solr/DIHCustomTransformer
http://wiki.apache.org/solr/DataImportHandler#ScriptTransformer

Post by manju16832003
OK here are the scenarios I tried.
*Scenario - 1: *
dih.xml (aka data-config.xml)
<entity dataSource="solr" name="listing" query="..."
transformer="DateFormatTransformer">
<field name=&quot;publish_date&quot; column=&quot;publish_date&quot;
xpath=&quot;/RDF/item/date&quot;
dateTimeFormat=&quot;&lt;b>yyyy-MM-dd'T'HH:mm:ss*" locale="en"/>
schema.xml
<field name="publish_date" type="date" indexed="true" stored="true"
multiValued="false" default="NOW" />
It does not work. Throws an following exception
WARN org.apache.solr.handler.dataimport.DateFormatTransformer – Could

not

Post by manju16832003
parse a Date field
java.text.ParseException: Unparseable date: "2013-12-05 15:40:03.0"
*Scenario - 2*
dih.xml (aka data-config.xml)
<entity dataSource="solr" name="listing" query="..."
transformer="DateFormatTransformer">
<field name=&quot;publish_date&quot; column=&quot;publish_date&quot;
xpath=&quot;/RDF/item/date&quot; dateTimeFormat=&quot;&lt;b>yyyy-MM-dd
HH:mm:ss*" locale="en"/>
schema.xml
<field name="publish_date" type="date" indexed="true" stored="true"
multiValued="false" default="NOW" />
Output is
<date name="publish_date">2012-12-05T07:38:27Z</date>
*Scenario - 3*
dih.xml (aka data-config.xml)
<entity dataSource="solr" name="listing" query="..."
transformer="DateFormatTransformer">
<field name=&quot;publish_date&quot; column=&quot;publish_date&quot;
xpath=&quot;/RDF/item/date&quot; dateTimeFormat=&quot;&lt;b>yyyy-MM-dd
HH:mm:ss*" locale="en"/>
schema.xml
<field name="publish_date" type="string" indexed="true" stored="true"
multiValued="false" default="NOW" />
Output is
<str name="publish_date">Thu Dec 05 15:38:27 MYT 2013</str>
My expectation was *2012-12-05 07:38:27* (MySQL DateTime format)
--

http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4106854.html

Post by manju16832003
Sent from the Solr - User mailing list archive at Nabble.com.

--
Regards,
Shalin Shekhar Mangar.

[manju16832003]

Hi Raymond,
You mean during the DIH execution?



--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4107136.html
Sent from the Solr - User mailing list archive at Nabble.com.

[Raymond Wiker]

Post by manju16832003
Hi Raymond,
You mean during the DIH execution?

Yes; as part of the sql statement.

[manju16832003]

Hi Raymond,
Let me give a try. Thanks for suggestion :-).

Cheers,



On Wed, Dec 18, 2013 at 12:38 AM, Raymond Wiker [via Lucene] <

On 17 Dec 2013, at 17:30 , manju16832003 <[hidden email]<http://user/SendEmail.jtp?type=node&node=4107138&i=0>>

Post by manju16832003
Hi Raymond,
You mean during the DIH execution?

Yes; as part of the sql statement.
------------------------------
If you reply to this email, your message will be added to the discussion
http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4107138.html
To unsubscribe from Storing MYSQL DATETIME field in solr as String, click
here<http://lucene.472066.n3.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=4106836&code=bWFuanUxNjgzMjAwM0BnbWFpbC5jb218NDEwNjgzNnwtMTU0ODA3NzA3>
.
NAML<http://lucene.472066.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>

--
Thank you,
Manjunath




--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4107195.html
Sent from the Solr - User mailing list archive at Nabble.com.

[manju16832003]

I found a way to store MySQL DateTime as a string in Solr

Here is the way

in data-config.xml in the SQL query we could convert the date directly to
char

CAST(l.creation_date as char) as creation_date,
CAST(l.modification_date as char) as modification_date,

in schema.xml
<field name="creation_date" type="string" indexed="true" stored="true"
multiValued="false" default="" />
<field name="modification_date" type="string" indexed="true" stored="true"
multiValued="false" default="" />


Output would be
<str name="creation_date">2013-11-13 10:26:32</str>
<str name="modification_date">2013-11-13 10:26:32</str>

This is exactly what I was looking for.

If you guys have any other wise please free to share. :-).

Happy Solr!!!





--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4109720.html
Sent from the Solr - User mailing list archive at Nabble.com.

[manju16832003]

Hi Tariq,
I'm glad that helped you :-).

Thanks



--
View this message in context: http://lucene.472066.n3.nabble.com/Solved-Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4112979.html
Sent from the Solr - User mailing list archive at Nabble.com.

[tariq]

Hello manju,

Thank you! It's really helpful for me.



--
View this message in context: http://lucene.472066.n3.nabble.com/Solved-Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4112977.html
Sent from the Solr - User mailing list archive at Nabble.com.

[manju16832003]

Hi Shekhar,

Yes :-).

Thanks for the references. Will try out the options you mentioned.

Thanks



--
View this message in context: http://lucene.472066.n3.nabble.com/Storing-MYSQL-DATETIME-field-in-solr-as-String-tp4106836p4107135.html
Sent from the Solr - User mailing list archive at Nabble.com.