CODEWITHSUNDEEP
solr
michal.hubczyk
michal.hubczyk

Reputation: 697

Solr json.facet range query with month grouping

I have got a solr query (Solr version 6.0.2) which groups and sums sales per day. Everything works great with following parameters:

json.facet=sales:{
      type:range,
      field:date_add,
      start:'2016-05-31T22:00:00Z',
      end:'2016-10-31T22:59:59Z',
      gap:'+1DAY',
      facet:{
         ordersSum:'sum(sales_value)'
      }
   }

(The specified hour 22:00:00 in start parameter is because of time zone ,since Solr keeps dates in UTC and my application works in Europe/Warsaw time zone)

With that I've got nice sales values with respect to days specified between start and end parameters. However now I want to do the same, but for the month basis, so I want sales for june, july [..] october.

I've figured out that, It's possible to change gap parameter to '+1MONTH', but the values are not consitent, since months aren't equal:

"val":"2016-05-31T22:00:00Z",
"val":"2016-06-30T22:00:00Z",
"val":"2016-07-30T22:00:00Z",
"val":"2016-08-30T22:00:00Z",
"val":"2016-09-30T22:00:00Z",

As it can be seen, the dates defaults to the first occured lower number of days (ie. july and agust have 31 days). Again the problem can be solved by manipulation of start parameter to:

2016-05-31T22:00:00Z/MONTH

The /MONTH suffix tells Solr to default to first day of month, at midnight. Then fetched dates looks good:

"val":"2016-06-01T00:00:00Z",
"val":"2016-07-01T00:00:00Z",
"val":"2016-08-01T00:00:00Z",
"val":"2016-09-01T00:00:00Z",
"val":"2016-10-01T00:00:00Z",

However given date values are correct for UTC time zone. Is there a way for Solr to add months properly, or set timezone for results?

Upvotes: 1

Views: 1761

Answers (1)

michal.hubczyk
michal.hubczyk

Reputation: 697

Ok, I think I've figured it out. It's possible to set timezone globally for the Solr query by adding additional parameter to the query:

&TZ=Europe/Warsaw

In my case the results are following:

"val":"2015-08-31T22:00:00Z",
"val":"2015-09-30T22:00:00Z",
"val":"2015-10-31T23:00:00Z",
"val":"2015-11-30T23:00:00Z",

(I've changed start / end parameters a bit to show how daylight saving time is kept). Additionally, the /MONTH suffix is not needed in that case, it only helps to keep the starting time correct.

Upvotes: 1

Related Questions