SOLR 1.4.1 : Indexing DateField time zone problem

[Shanmugavel SRD <sr...@gmail.com>]

I am using SOLR 1.4.1. My SOLR runs in a server which is in EST zone.
I am trying to index a date field which is in MySQL as
'2007-08-08T05:36:50Z' but while indexing it becomes '2007-08-08T09:36:50Z'
where 4 hours got increased. But I want the date as is while indexing,
means, after indexing I want the value as '2007-08-08T05:36:50Z' in
'modified_d' field.

Can anyone help me on this?

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

I searched in this forum and there are discussions on this same problem but
on SOLR 1.3, that's why I am posting this query again.
-- 
View this message in context: http://lucene.472066.n3.nabble.com/SOLR-1-4-1-Indexing-DateField-time-zone-problem-tp1966118p1966118.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: SOLR 1.4.1 : Indexing DateField time zone problem

[Erik Fäßler <er...@uni-jena.de>]

Hm - but I observed this, too. And I didn't do anything with SQL at all. I was parsing date strings out of XML, creating a string which could be formatted using DIH's DateFormatTransformer. But the indexed dates have been a few hours too early in my case, switching back the dates to one day before. I didn't go deeply into this, I think I was experiencing a conversion of my dates strings from my time zone into UTC. My quick solution was to write another version of the DateFormatTransformer which takes a timeZone attribute. This way, the date strings shown in the indexed documents showed the correct date (which was what I wanted).
But I guess doing it this way also wasn't then best solution because when using date range math I ran into other time zone conversion problems, due to my own conversions earlier I think.

But until now I didn't go deeper into this so I don't know the exact reasons (although I'm sure it's not really a too challenging problem) and I haven't done a solution yet.

Best regards,

Erik


Am 25.11.2010 um 18:04 schrieb Erick Erickson <er...@gmail.com>:

> I don't believe this is a Solr issue at all. I suspect your MySql query is
> doing the
> timezone change. Solr doesn't apply any processing to the date, it doesn't
> need to because times are all Zulu.
> 
> There's a little known debug console for DIH, see:
> http://wiki.apache.org/solr/DataImportHandler#interactive
> <http://wiki.apache.org/solr/DataImportHandler#interactive>that might help a
> lot. I think what you need to do is apply a transformation in your
> SQL statement to get times in UTC, somthing like CONVERT_TZ or some such,
> see:
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz
> 
> Best
> Erick
> 
> On Thu, Nov 25, 2010 at 5:27 AM, Shanmugavel SRD
> <sr...@gmail.com>wrote:
> 
>> 
>> I am using SOLR 1.4.1. My SOLR runs in a server which is in EST zone.
>> I am trying to index a date field which is in MySQL as
>> '2007-08-08T05:36:50Z' but while indexing it becomes '2007-08-08T09:36:50Z'
>> where 4 hours got increased. But I want the date as is while indexing,
>> means, after indexing I want the value as '2007-08-08T05:36:50Z' in
>> 'modified_d' field.
>> 
>> Can anyone help me on this?
>> 
>> <field column="post_modified" name="modified_d"
>> dateTimeFormat="yyyy-MM-dd'T'hh:mm:ss'Z'" />
>> 
>> I searched in this forum and there are discussions on this same problem but
>> on SOLR 1.3, that's why I am posting this query again.
>> --
>> View this message in context:
>> http://lucene.472066.n3.nabble.com/SOLR-1-4-1-Indexing-DateField-time-zone-problem-tp1966118p1966118.html
>> Sent from the Solr - User mailing list archive at Nabble.com.
>> 

Re: SOLR 1.4.1 : Indexing DateField time zone problem

[Erick Erickson <er...@gmail.com>]

I don't believe this is a Solr issue at all. I suspect your MySql query is
doing the
timezone change. Solr doesn't apply any processing to the date, it doesn't
need to because times are all Zulu.

There's a little known debug console for DIH, see:
http://wiki.apache.org/solr/DataImportHandler#interactive
<http://wiki.apache.org/solr/DataImportHandler#interactive>that might help a
lot. I think what you need to do is apply a transformation in your
SQL statement to get times in UTC, somthing like CONVERT_TZ or some such,
see:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz

Best
Erick

On Thu, Nov 25, 2010 at 5:27 AM, Shanmugavel SRD
<sr...@gmail.com>wrote:

>
> I am using SOLR 1.4.1. My SOLR runs in a server which is in EST zone.
> I am trying to index a date field which is in MySQL as
> '2007-08-08T05:36:50Z' but while indexing it becomes '2007-08-08T09:36:50Z'
> where 4 hours got increased. But I want the date as is while indexing,
> means, after indexing I want the value as '2007-08-08T05:36:50Z' in
> 'modified_d' field.
>
> Can anyone help me on this?
>
> <field column="post_modified" name="modified_d"
> dateTimeFormat="yyyy-MM-dd'T'hh:mm:ss'Z'" />
>
> I searched in this forum and there are discussions on this same problem but
> on SOLR 1.3, that's why I am posting this query again.
> --
> View this message in context:
> http://lucene.472066.n3.nabble.com/SOLR-1-4-1-Indexing-DateField-time-zone-problem-tp1966118p1966118.html
> Sent from the Solr - User mailing list archive at Nabble.com.
>