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 Eric Katherman <ka...@gmail.com> on 2013/11/14 05:38:05 UTC
queries including time zone
Can anybody provide any insight about using the tz param? The behavior of this isn't affecting date math and /day rounding. What format does the tz variables need to be in? Not finding any documentation on this.
Sample query we're using:
path=/select params={tz=America/Chicago&sort=id+desc&start=0&q=application_id:51b30ed9bc571bd96773f09c+AND+object_key:object_26+AND+values_field_215_date:[*+TO+NOW/DAY%2B1DAY]&wt=json&rows=25}
Thanks!
Eric
Re: queries including time zone
Posted by Eric Katherman <ka...@gmail.com>.
Anybody have any additional suggestions for this TZ issue we're having? I've included the query below. The full debug output is in the gist link also included if you want to see it. As mentioned, the test solr installation has one document with a date that was set to the 15th, and when this query was run during the evening of the 14th we still got that one document in the response. Are there any other potential culprits here? E.g. DateField vs TrieDateField?
"params": {
"debugQuery": "true",
"indent": "true",
"q": "values_field_66_date:[* TO NOW/DAY+1DAY]",
"TZ:'America/Los_Angeles'": "",
"_": "1384487341231",
"wt": "json",
"rows": "25"
}
https://gist.github.com/anonymous/7478773
Thanks,
Eric
On Nov 14, 2013, at 10:58 PM, Eric Katherman <ka...@gmail.com> wrote:
> We're still not seeing the proper result. I've included a gist of the query and its debug result. This was run on a clean index running 4.4.0 with just one document. That document has a date of 11/15/2013 yet the date in the included TZ it is the 14th but I still get that document returned. Hoping someone can help.
>
> https://gist.github.com/anonymous/7478773
>
>
> On Nov 14, 2013, at 3:06 PM, Chris Hostetter <ho...@fucit.org> wrote:
>
>>
>> I've beefed up the ref guide page on dates to include more info about all
>> of this...
>>
>> https://cwiki.apache.org/confluence/display/solr/Working+with+Dates
>>
>>
>> -Hoss
>
Re: queries including time zone
Posted by Chris Hostetter <ho...@fucit.org>.
: We're still not seeing the proper result. I've included a gist of the
: query and its debug result. This was run on a clean index running 4.4.0
: with just one document. That document has a date of 11/15/2013 yet the
: date in the included TZ it is the 14th but I still get that document
: returned. Hoping someone can help.
:
: https://gist.github.com/anonymous/7478773
1) please don't use gist/pastie URLs in email -- they can be
changed/deleted after you send your email, making it impossible for people
reading email archives to udnerstand your question or learn aything from
the examples later. Just include the output in the body of your email
2) you haven't included enough information (either in your email or in
the gist) to make sense of the behavior you are seeing:
a) what *exact* value is in the index for this document?
b) what *exact* time did you run the query?
you use expressions like "date of 11/15/2013" and "the 14th" but those are
vague concepts that could mean specific moments in time in ranges of up
to 48 hours (depending on what timezone you mean when you refer to them
as a string in terms of "days")
Here's some simple, concrete, examples using the Solr example configs to
help show you how the TZ option affects date math. These examples don't
require any documents to be indexed...
w/o TZ, so all math is relative to UTC...
$ date --utc && curl -s 'http://localhost:8983/solr/collection1/query?omitHeader=true&debug=query&q=*:*&fq=now_dt:NOW&fq=today_dt:NOW/DAY&fq=tomorrow_dt:NOW/DAY%2B1DAY&&fq=lastmonth_dt:NOW-1MONTH/DAY&fq=specific_rounded_dt:"2013-07-04T12:34:56Z/DAY"' | grep 2013 | grep -v DAY
Mon Nov 18 18:58:57 UTC 2013
"parsed_filter_queries":["now_dt:2013-11-18T18:58:57.813Z",
"today_dt:2013-11-18T00:00:00Z",
"tomorrow_dt:2013-11-19T00:00:00Z",
"lastmonth_dt:2013-10-18T00:00:00Z",
"specific_rounded_dt:2013-07-04T00:00:00Z"]}}
w/TZ = America/Los_Angeles...
$ date --utc && curl -s 'http://localhost:8983/solr/collection1/query?omitHeader=true&debug=query&q=*:*&fq=now_dt:NOW&fq=today_dt:NOW/DAY&fq=tomorrow_dt:NOW/DAY%2B1DAY&&fq=lastmonth_dt:NOW-1MONTH/DAY&fq=specific_rounded_dt:"2013-07-04T12:34:56Z/DAY"&TZ=America/Los_Angeles' | grep 2013 | grep -v DAY
Mon Nov 18 18:58:27 UTC 2013
"parsed_filter_queries":["now_dt:2013-11-18T18:58:27.477Z",
"today_dt:2013-11-18T08:00:00Z",
"tomorrow_dt:2013-11-19T08:00:00Z",
"lastmonth_dt:2013-10-18T07:00:00Z",
"specific_rounded_dt:2013-07-04T07:00:00Z"]}}
Below is my best effort to reproduce what you seemed to be describing that
you were trying to do, which works as expected. Note that the document
i've indexed has a field value of "2013-11-19T01:00:00Z".
In the first query, at "Mon Nov 18 19:12:07 UTC 2013" we ask Solr to
compute "NOW/DAY+1DAY" using UTC to define the start of the current day:
as a result that document does not match our query. In the second query,
executed at "Mon Nov 18 19:12:29 UTC 2013", we tell solr to use
America/Los_Angeles when rounding to the nearest "day": as a result, the
document *does* match our query, because 2013-11-19T01:00:00Z is before
the start of the "tomorrow" in terms of the start of day conventions
for America/Los_Angeles (ie: ...T08:00:00 UTC).
(One thing to note about these examples, is that unlike the simple
term queries from my above examples, when outputing a range
query over a date, the debug output displays the ranges in terms of "ms
since epoch) so i've also included some basic re-formatting after each
example query)
$ curl -H 'Content-Type: application/xml' -d '<add><doc><field name="id">HOSS</field><field name="my_dt">2013-11-19T01:00:00Z</field></doc></add>' 'http://localhost:8983/solr/collection1/update?commit=true'
<?xml version="1.0" encoding="UTF-8"?>
<response>
<lst name="responseHeader"><int name="status">0</int><int
name="QTime">562</int></lst>
</response>
$ date --utc && curl 'http://localhost:8983/solr/collection1/query?q=my_dt:\[*+TO+NOW/DAY%2B1DAY\]&debug=query'
Mon Nov 18 19:12:07 UTC 2013
{
"responseHeader":{
"status":0,
"QTime":6,
"params":{
"q":"my_dt:[* TO NOW/DAY+1DAY]",
"debug":"query"}},
"response":{"numFound":0,"start":0,"docs":[]
},
"debug":{
"rawquerystring":"my_dt:[* TO NOW/DAY+1DAY]",
"querystring":"my_dt:[* TO NOW/DAY+1DAY]",
"parsedquery":"my_dt:[* TO 1384819200000]",
"parsedquery_toString":"my_dt:[* TO 1384819200000]",
"QParser":"LuceneQParser"}}
$ date --utc --date="@1384819200"
Tue Nov 19 00:00:00 UTC 2013
$ date --utc && curl 'http://localhost:8983/solr/collection1/query?q=my_dt:\[*+TO+NOW/DAY%2B1DAY\]&TZ=America/Los_Angeles&debug=query'
Mon Nov 18 19:12:29 UTC 2013
{
"responseHeader":{
"status":0,
"QTime":2,
"params":{
"q":"my_dt:[* TO NOW/DAY+1DAY]",
"debug":"query",
"TZ":"America/Los_Angeles"}},
"response":{"numFound":1,"start":0,"docs":[
{
"id":"HOSS",
"my_dt":"2013-11-19T01:00:00Z",
"_version_":1452070046839865344}]
},
"debug":{
"rawquerystring":"my_dt:[* TO NOW/DAY+1DAY]",
"querystring":"my_dt:[* TO NOW/DAY+1DAY]",
"parsedquery":"my_dt:[* TO 1384848000000]",
"parsedquery_toString":"my_dt:[* TO 1384848000000]",
"QParser":"LuceneQParser"}}
$ date --utc --date="@1384848000"
Tue Nov 19 08:00:00 UTC 2013
-Hoss
Re: queries including time zone
Posted by Eric Katherman <ka...@gmail.com>.
We're still not seeing the proper result. I've included a gist of the query and its debug result. This was run on a clean index running 4.4.0 with just one document. That document has a date of 11/15/2013 yet the date in the included TZ it is the 14th but I still get that document returned. Hoping someone can help.
https://gist.github.com/anonymous/7478773
On Nov 14, 2013, at 3:06 PM, Chris Hostetter <ho...@fucit.org> wrote:
>
> I've beefed up the ref guide page on dates to include more info about all
> of this...
>
> https://cwiki.apache.org/confluence/display/solr/Working+with+Dates
>
>
> -Hoss
Re: queries including time zone
Posted by Chris Hostetter <ho...@fucit.org>.
I've beefed up the ref guide page on dates to include more info about all
of this...
https://cwiki.apache.org/confluence/display/solr/Working+with+Dates
-Hoss
Re: queries including time zone
Posted by Chris Hostetter <ho...@fucit.org>.
: Can anybody provide any insight about using the tz param? The behavior
: of this isn't affecting date math and /day rounding. What format does
: the tz variables need to be in? Not finding any documentation on this.
it's not "tz" it's "TZ"
The input/output format is always in UTC, but TZ will affect all of the
date math...
https://wiki.apache.org/solr/CoreQueryParameters#TZ
-Hoss
Re: queries including time zone
Posted by Erick Erickson <er...@gmail.com>.
IMO you will save yourself endless grief just biting the bullet and working
with UTC
at all times. The instant you have uses in even adjacent but different time
zones,
you'll have to deal with this anyway.
FWIW,
Erick
On Thu, Nov 14, 2013 at 12:26 AM, Jack Krupansky <ja...@basetechnology.com>wrote:
> I believe it is the TZ column from this table:
> http://en.wikipedia.org/wiki/List_of_tz_database_time_zones
>
> Yeah, it's on my TODO list for my book.
>
> I suspect that "tz" will not affect "NOW", which is probably UTC. I
> suspect that "tz" only affects literal dates in date math.
>
> -- Jack Krupansky
>
> -----Original Message----- From: Eric Katherman
> Sent: Wednesday, November 13, 2013 11:38 PM
> To: solr-user@lucene.apache.org
> Subject: queries including time zone
>
>
> Can anybody provide any insight about using the tz param? The behavior of
> this isn't affecting date math and /day rounding. What format does the tz
> variables need to be in? Not finding any documentation on this.
>
> Sample query we're using:
>
> path=/select params={tz=America/Chicago&sort=id+desc&start=0&q=
> application_id:51b30ed9bc571bd96773f09c+AND+object_key:object_26+AND+
> values_field_215_date:[*+TO+NOW/DAY%2B1DAY]&wt=json&rows=25}
>
> Thanks!
> Eric=
>
Re: queries including time zone
Posted by Jack Krupansky <ja...@basetechnology.com>.
I believe it is the TZ column from this table:
http://en.wikipedia.org/wiki/List_of_tz_database_time_zones
Yeah, it's on my TODO list for my book.
I suspect that "tz" will not affect "NOW", which is probably UTC. I suspect
that "tz" only affects literal dates in date math.
-- Jack Krupansky
-----Original Message-----
From: Eric Katherman
Sent: Wednesday, November 13, 2013 11:38 PM
To: solr-user@lucene.apache.org
Subject: queries including time zone
Can anybody provide any insight about using the tz param? The behavior of
this isn't affecting date math and /day rounding. What format does the tz
variables need to be in? Not finding any documentation on this.
Sample query we're using:
path=/select
params={tz=America/Chicago&sort=id+desc&start=0&q=application_id:51b30ed9bc571bd96773f09c+AND+object_key:object_26+AND+values_field_215_date:[*+TO+NOW/DAY%2B1DAY]&wt=json&rows=25}
Thanks!
Eric=