How to import the correct datetime from mysql to solr?

Question

I don't figure out how to import the correct datetime from mysql to solr via the DataImportHandler. After the import the datetime values get substracted 2 hours

mysql "created_at 2013-04-05 15:04:21" gets in solr to "created_at":"2013-04-05T13:04:21Z"

mysql @@global.time_zone, @@session.time_zone are both system and display the correct CET time.

Here my data-config.xml

<dataConfig>  
    <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost/test"
        user="+++" password="++++/>  <document>
<entity name="id"
    query="SELECT table.created_at, ... from table"
    <field column="created_at" name="created_at"/>

I tried to use the CONVERT_TZ command. In mysql it worked out. But with solr I have no success, the created_at value is the not indexed at all.

<entity name="id"
    query="SELECT query="SELECT CONVERT_TZ(table.created_at,'+00:00','+01:00'), ... from table"
    <field column="created_at" name="created_at"/>

See http://stackoverflow.com/questions/5149606/solr-not-saving-time-in-utc-format. Solr is converting the CET time you fed to UTC and that is the only format Solr will accept dates. — arun, Apr 06 '13 at 06:33
My solution is now to feed Solr with UTC and format the datetime in the browser client via javascript toLocaleString(); [https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/Date#Date_instances] — domfry, Apr 09 '13 at 14:56

score 0 · Answer 1 · answered Apr 08 '13 at 06:03

0

try this : i used this in my indexing,

$query= "SELECT DATE_FORMAT(CONVERT_TZ(table.created_at,'+00:00','+01:00'),'%Y-%m-%dT%TZ'),.. FROM table ";

DateField Maual of SOLR

answered Apr 08 '13 at 06:03

Suhel Meman

3,702
1
18
26

ok, thanks. I indexed dates in utc format 2013-01-09T15:57:50Z .. that worked out fine. Is there a way to query the dates form solr in CET time? or do I have to transform the timezone outside of solr in my user interface via eg. javascript – domfry Apr 08 '13 at 11:58
Suhel Meman: thanks, after i added this sql statement to my data-config.xml the created_at field was not indexed at all anymore – domfry Apr 08 '13 at 12:04

How to import the correct datetime from mysql to solr?

1 Answers1