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 ji...@svensktnaringsliv.se on 2014/05/21 16:12:18 UTC
Date truncation and time zone when searching
Hi,
What is the prefered way to do searches with date truncation with respect to a specific time zone? The dates are stored correctly, ie I can see the UTC date in the index and if I add 1 or 2 hours (depending on daylight saving time or not) I get the time in our time zone (CET/CEST). But when I search I want to be able to do something like:
datefield:[* TO NOW/MONTH]
or:
datefield:[* TO NOW/DAY+3DAYS]
etc...
...and I want the MONTH or DAY truncation to correspond to our time zone. How can I achieve this? Some of our dates are have the time set to 00:00 (CET/CEST), but since the date truncation logic in solr only handles UTC that would classify those documents to "belong to the day before", and if the date is on 00:00 on January 1, then the document is even considered to belong to the year before it's actual year!
Surely I can't be the only one hindered by this. What are the common workarounds used today? Some people mention that they trick solr by using the date and time expressed as for their own time zone, and then claiming that the time zone is UTC. Ie saying "2000-01-01T00:00:00Z" for the beginning of this millennium, even though it should be "1999-12-31T23:00:00Z". But I don't like this idea of storing incorrect date values in the index, since then we need to "convert" them again before we display them.
Regards
/Jimi
Re: Date truncation and time zone when searching
Posted by Chris Hostetter <ho...@fucit.org>.
: Try the TZ parameter on the query, as blah&TZ=GMT-4
Docs...
https://cwiki.apache.org/confluence/display/solr/Working+with+Dates
: There's a good discussion of why PDT is ambiguous here:
: https://issues.apache.org/jira/browse/SOLR-2690.
-Hoss
http://www.lucidworks.com/
SV: Date truncation and time zone when searching
Posted by ji...@svensktnaringsliv.se.
Thanks! I totally forgot to add the word "math" (as in 'solr date math time zone') when searching for a solution on this, so I never stumbled upon that jira issue. Will giv it a try.
/Jimi
> -----Ursprungligt meddelande-----
> Från: Erick Erickson [mailto:erickerickson@gmail.com]
> Skickat: den 21 maj 2014 17:25
> Till: solr-user@lucene.apache.org
> Ämne: Re: Date truncation and time zone when searching
>
> Try the TZ parameter on the query, as blah&TZ=GMT-4
>
> There's a good discussion of why PDT is ambiguous here:
> https://issues.apache.org/jira/browse/SOLR-2690.
>
> You can define whatever default parameters you want in your handler in the
> <defaults> section, the TZ parameter included.
>
>
> Best
> Erick
>
> On Wed, May 21, 2014 at 7:30 AM, <ji...@svensktnaringsliv.se>
> wrote:
> > OK. Feels a bit strange that one would have to do this manual calculation in
> every place that performs searches like this.
> > Would be much more logical if solr supported specifying the timezone in
> the query (with a default setting being possible to configure in solrconfig),
> and that solr itself did this conversion behind the scenes. Maybe that will
> come in the future?
> >
> > /Jimi
> >
> >> -----Ursprungligt meddelande-----
> >> Från: Michael Ryan [mailto:mryan@moreover.com]
> >> Skickat: den 21 maj 2014 16:23
> >> Till: solr-user@lucene.apache.org
> >> Ämne: RE: Date truncation and time zone when searching
> >>
> >> Well for CEST, which is 2 hours ahead, I would think you could just do...
> >>
> >> datefield:[* TO NOW/MONTH-2HOURS]
> >>
> >> That would give you everything up to 2014-04-30 22:00:00 GMT, which
> >> is
> >> 2014-05-01 00:00:00 CEST.
> >>
> >> Always always always store the correct value.
> >>
> >> -Michael
> >>
> >> -----Original Message-----
> >> From: jimi.hullegard@svensktnaringsliv.se
> >> [mailto:jimi.hullegard@svensktnaringsliv.se]
> >> Sent: Wednesday, May 21, 2014 10:12 AM
> >> To: solr-user@lucene.apache.org
> >> Subject: Date truncation and time zone when searching
> >>
> >> Hi,
> >>
> >> What is the prefered way to do searches with date truncation with
> >> respect to a specific time zone? The dates are stored correctly, ie I
> >> can see the UTC date in the index and if I add 1 or 2 hours
> >> (depending on daylight saving time or
> >> not) I get the time in our time zone (CET/CEST). But when I search I
> >> want to be able to do something like:
> >>
> >> datefield:[* TO NOW/MONTH]
> >> or:
> >> datefield:[* TO NOW/DAY+3DAYS]
> >> etc...
> >>
> >> ...and I want the MONTH or DAY truncation to correspond to our time
> zone.
> >> How can I achieve this? Some of our dates are have the time set to
> >> 00:00 (CET/CEST), but since the date truncation logic in solr only
> >> handles UTC that would classify those documents to "belong to the day
> >> before", and if the date is on 00:00 on January 1, then the document
> >> is even considered to belong to the year before it's actual year!
> >>
> >> Surely I can't be the only one hindered by this. What are the common
> >> workarounds used today? Some people mention that they trick solr by
> >> using the date and time expressed as for their own time zone, and
> >> then claiming that the time zone is UTC. Ie saying
> >> "2000-01-01T00:00:00Z" for the beginning of this millennium, even
> >> though it should be "1999-12-31T23:00:00Z". But I don't like this
> >> idea of storing incorrect date values in the index, since then we need to
> "convert" them again before we display them.
> >>
> >> Regards
> >> /Jimi
Re: Date truncation and time zone when searching
Posted by Erick Erickson <er...@gmail.com>.
Try the TZ parameter on the query, as blah&TZ=GMT-4
There's a good discussion of why PDT is ambiguous here:
https://issues.apache.org/jira/browse/SOLR-2690.
You can define whatever default parameters you want in your handler in
the <defaults> section, the TZ parameter included.
Best
Erick
On Wed, May 21, 2014 at 7:30 AM, <ji...@svensktnaringsliv.se> wrote:
> OK. Feels a bit strange that one would have to do this manual calculation in every place that performs searches like this.
> Would be much more logical if solr supported specifying the timezone in the query (with a default setting being possible to configure in solrconfig), and that solr itself did this conversion behind the scenes. Maybe that will come in the future?
>
> /Jimi
>
>> -----Ursprungligt meddelande-----
>> Från: Michael Ryan [mailto:mryan@moreover.com]
>> Skickat: den 21 maj 2014 16:23
>> Till: solr-user@lucene.apache.org
>> Ämne: RE: Date truncation and time zone when searching
>>
>> Well for CEST, which is 2 hours ahead, I would think you could just do...
>>
>> datefield:[* TO NOW/MONTH-2HOURS]
>>
>> That would give you everything up to 2014-04-30 22:00:00 GMT, which is
>> 2014-05-01 00:00:00 CEST.
>>
>> Always always always store the correct value.
>>
>> -Michael
>>
>> -----Original Message-----
>> From: jimi.hullegard@svensktnaringsliv.se
>> [mailto:jimi.hullegard@svensktnaringsliv.se]
>> Sent: Wednesday, May 21, 2014 10:12 AM
>> To: solr-user@lucene.apache.org
>> Subject: Date truncation and time zone when searching
>>
>> Hi,
>>
>> What is the prefered way to do searches with date truncation with respect to
>> a specific time zone? The dates are stored correctly, ie I can see the UTC date
>> in the index and if I add 1 or 2 hours (depending on daylight saving time or
>> not) I get the time in our time zone (CET/CEST). But when I search I want to
>> be able to do something like:
>>
>> datefield:[* TO NOW/MONTH]
>> or:
>> datefield:[* TO NOW/DAY+3DAYS]
>> etc...
>>
>> ...and I want the MONTH or DAY truncation to correspond to our time zone.
>> How can I achieve this? Some of our dates are have the time set to 00:00
>> (CET/CEST), but since the date truncation logic in solr only handles UTC that
>> would classify those documents to "belong to the day before", and if the
>> date is on 00:00 on January 1, then the document is even considered to
>> belong to the year before it's actual year!
>>
>> Surely I can't be the only one hindered by this. What are the common
>> workarounds used today? Some people mention that they trick solr by using
>> the date and time expressed as for their own time zone, and then claiming
>> that the time zone is UTC. Ie saying "2000-01-01T00:00:00Z" for the beginning
>> of this millennium, even though it should be "1999-12-31T23:00:00Z". But I
>> don't like this idea of storing incorrect date values in the index, since then we
>> need to "convert" them again before we display them.
>>
>> Regards
>> /Jimi
SV: Date truncation and time zone when searching
Posted by ji...@svensktnaringsliv.se.
OK. Feels a bit strange that one would have to do this manual calculation in every place that performs searches like this.
Would be much more logical if solr supported specifying the timezone in the query (with a default setting being possible to configure in solrconfig), and that solr itself did this conversion behind the scenes. Maybe that will come in the future?
/Jimi
> -----Ursprungligt meddelande-----
> Från: Michael Ryan [mailto:mryan@moreover.com]
> Skickat: den 21 maj 2014 16:23
> Till: solr-user@lucene.apache.org
> Ämne: RE: Date truncation and time zone when searching
>
> Well for CEST, which is 2 hours ahead, I would think you could just do...
>
> datefield:[* TO NOW/MONTH-2HOURS]
>
> That would give you everything up to 2014-04-30 22:00:00 GMT, which is
> 2014-05-01 00:00:00 CEST.
>
> Always always always store the correct value.
>
> -Michael
>
> -----Original Message-----
> From: jimi.hullegard@svensktnaringsliv.se
> [mailto:jimi.hullegard@svensktnaringsliv.se]
> Sent: Wednesday, May 21, 2014 10:12 AM
> To: solr-user@lucene.apache.org
> Subject: Date truncation and time zone when searching
>
> Hi,
>
> What is the prefered way to do searches with date truncation with respect to
> a specific time zone? The dates are stored correctly, ie I can see the UTC date
> in the index and if I add 1 or 2 hours (depending on daylight saving time or
> not) I get the time in our time zone (CET/CEST). But when I search I want to
> be able to do something like:
>
> datefield:[* TO NOW/MONTH]
> or:
> datefield:[* TO NOW/DAY+3DAYS]
> etc...
>
> ...and I want the MONTH or DAY truncation to correspond to our time zone.
> How can I achieve this? Some of our dates are have the time set to 00:00
> (CET/CEST), but since the date truncation logic in solr only handles UTC that
> would classify those documents to "belong to the day before", and if the
> date is on 00:00 on January 1, then the document is even considered to
> belong to the year before it's actual year!
>
> Surely I can't be the only one hindered by this. What are the common
> workarounds used today? Some people mention that they trick solr by using
> the date and time expressed as for their own time zone, and then claiming
> that the time zone is UTC. Ie saying "2000-01-01T00:00:00Z" for the beginning
> of this millennium, even though it should be "1999-12-31T23:00:00Z". But I
> don't like this idea of storing incorrect date values in the index, since then we
> need to "convert" them again before we display them.
>
> Regards
> /Jimi
RE: Date truncation and time zone when searching
Posted by Michael Ryan <mr...@moreover.com>.
Well for CEST, which is 2 hours ahead, I would think you could just do...
datefield:[* TO NOW/MONTH-2HOURS]
That would give you everything up to 2014-04-30 22:00:00 GMT, which is 2014-05-01 00:00:00 CEST.
Always always always store the correct value.
-Michael
-----Original Message-----
From: jimi.hullegard@svensktnaringsliv.se [mailto:jimi.hullegard@svensktnaringsliv.se]
Sent: Wednesday, May 21, 2014 10:12 AM
To: solr-user@lucene.apache.org
Subject: Date truncation and time zone when searching
Hi,
What is the prefered way to do searches with date truncation with respect to a specific time zone? The dates are stored correctly, ie I can see the UTC date in the index and if I add 1 or 2 hours (depending on daylight saving time or not) I get the time in our time zone (CET/CEST). But when I search I want to be able to do something like:
datefield:[* TO NOW/MONTH]
or:
datefield:[* TO NOW/DAY+3DAYS]
etc...
...and I want the MONTH or DAY truncation to correspond to our time zone. How can I achieve this? Some of our dates are have the time set to 00:00 (CET/CEST), but since the date truncation logic in solr only handles UTC that would classify those documents to "belong to the day before", and if the date is on 00:00 on January 1, then the document is even considered to belong to the year before it's actual year!
Surely I can't be the only one hindered by this. What are the common workarounds used today? Some people mention that they trick solr by using the date and time expressed as for their own time zone, and then claiming that the time zone is UTC. Ie saying "2000-01-01T00:00:00Z" for the beginning of this millennium, even though it should be "1999-12-31T23:00:00Z". But I don't like this idea of storing incorrect date values in the index, since then we need to "convert" them again before we display them.
Regards
/Jimi