Dear Solr Team,

One use case we have with solr (we use 9.5) is to facet over months on a 
specific fields in our documents. We want to know how many documents there are 
each Month.
But as you probably have guessed, the users of our app don't life in Greenwich 
(also their time changes two times a year ... yuck ...)
So what we would need is to have the ability to get range facets but using 
another timezone.
One simple way that one would probably try first is the following:



GET https://<link-to-solr>/solr/<collection>/query
Accept: application/json
Content-Type: application/json

{
    "query": "*:*",
    "limit": 0,
    "facet": {
       "series": {
          "field": "<date-field>",
          "gap": "+1MONTH",
          "start": "2023-12-31T23:00:00Z/MONTH",
          "end": " 2024-04-30T22:00:00Z",
          "type": "range",
            "time-zone": "America/Los_Angeles"
       }
    }
}


This will obviously not work, as Solr calculates dates using UTC, and thus has 
no idea that 23:00 is actually midnight. It will continue to use 23:00 even for 
summer months (when some weird people have decided that in the summer midnight 
should be at 22:00 UTC). That's not what we want.
But even more drastically, the first time February comes along, all precision 
concerning date calculations jump out the window for good, as solr will now 
continue to use the 29th 23:00 as the starting point for every month after it. 
This will now be 1 day and 1 hour off for all future winter months that are 
preceded by a 30 day month, and 2 days and 2 hours off for all summer months 
preceded by a 31 day month. As 2024 is a leap year the problem is huge, but not 
colossal as it would be in most other years where February only has 28 days.
This is what we get (which is, as I have described, not what one would want to 
get)


{
               "responseHeader": {
                              "zkConnected": true,
                              "status": 0,
                              "QTime": 13,
                              "params": {
                                            "json": "<the json request>"
                              }
               },
               "response": {
                              "numFound": 2392751,
                              "start": 0,
                              "maxScore": 1.0,
                              "numFoundExact": true,
                              "docs": []
               },
               "facets": {
                              "count": 2392751,
                              "series": {
                                            "buckets": [
                                                           {
                                                                          
"val": "2023-12-31T23:00:00Z",
                                                                          
"count": 20749
                                                           },
                                                           {
                                                                          
"val": "2024-01-31T23:00:00Z",
                                                                          
"count": 18716
                                                           },
                                                           {
                                                                          
"val": "2024-02-29T23:00:00Z",
                                                                          
"count": 19230
                                                           },
                                                           {
                                                                          
"val": "2024-03-29T23:00:00Z",
                                                                          
"count": 19788
                                                           },
                                                           {
                                                                          
"val": "2024-04-29T23:00:00Z",
                                                                          
"count": 9215
                                                           }
                                            ]
                              }
               }
}


So the solution would be to use TZ as described in 
https://solr.apache.org/guide/solr/9_5/indexing-guide/date-formatting-math.html#tz
But somehow this does not work as advertised. It wrongly returns starting 
points in the perspective of utc aka: 00:00:00Z instead of 23:00:00Z) . In your 
example where you use America/Los_Angeles it shows that it correctly uses 
2013-11-01T07:00:00Z and so on. I strongly suspect that this is called 
differently in the json api (considering "q" becomes "query" in the json api)



GET https://<link-to-solr>/solr/<collection>/query
Accept: application/json
Content-Type: application/json

{
    "query": "*:*",
    "limit": 0,
    "facet": {
       "series": {
          "hardend": false,
          "field": "<date-field>",
          "gap": "+1MONTH",
          "start": "2024-01-01T00:00:00Z/MONTH",
          "end": "2024-05-01T00:00:00Z",
          "type": "range",
          "TZ": "Europe/Zurich"
       }
    }
}


I even added "/MONTH" as I suspect that it would not work without. But sadly 
this does not make a difference. I also tried "tz" (not uppercase), "timezone", 
"time-zone", "time_zone" as a key, but none seem to work.

Even when it would work, we would continue to have a problem, as solrj does not 
expose the functionality to supply a timezone as all constructors that are 
available are: it also does not allow one to use String for dates to use more 
advanced date features as NOW and /MONTH and calculations eg NOW-10YEARS.
I know this is an edge case, but what if I want to use NOW when the request is 
processed by the solr server (as opposed to when its processed on our server 
before sending the request to solr)



public RangeFacetMap(String field, long start, long end, long gap)

public RangeFacetMap(String field, double start, double end, double gap)

public RangeFacetMap(String field, Date start, Date end, String gap)


Obviously I could use the less elegant way of constructing it by using a normal 
Map, but the main problem is still that it does not seem to apply the time zone.


request.withFacet("series", Map.of(
       "hardend": false,
       "field": "<date-field>",
       "gap": "+1MONTH",
       "start": "2024-01-01T00:00:00Z/MONTH",
       "end": "2024-05-01T00:00:00Z",
       "type": "range",
       "TZ", "Europe/Zurich"
));


If there is anything unclear with my question please feel free to demand 
clarification.

With Kind Regards,
Dario Viva

Reply via email to