Consider timezone when making a response with dates to Kibana

[Nick-S-2018]

Bug Description:

In requests with datetime fields, Kibana passes information about timezone in a specific field, time_zone. We need to consider it when we make a response. For example, for the following date range request:

"date_range": {
    "field": "f5",
    "ranges": [
        {
            "from": "2024-01-01",
            "to": "2024-01-02"
        }
    ],
    "time_zone": "Asia/Novosibirsk"
}

Manticore and Elastic produce responses different by the value of the timezone shift. Elastic's one:

{
    "doc_count": 1,
    "from": 1704042000000.0,
    "from_as_string": "2024-01-01T00:00:00.000+07:00",
    "key": "2024-01-01T00:00:00.000+07:00-2024-01-02T00:00:00.000+07:00",
    "to": 1704128400000.0,
    "to_as_string": "2024-01-02T00:00:00.000+07:00"
}

Manticore's one:

{
    "doc_count": 1,
    "from": "2024-01-01T00:00:00",
    "key": "2024-01-01T00:00:00-2024-01-02T00:00:00",
    "to": "2024-01-02T00:00:00"
}

Also, for the sake of consistency, we could pass timestamp values in from and to fields, like Elastic does, while also add from_as_string and to_as_string fields with the values of our current from and to. Kibana seems to understand the current format too, but it probably would be better to keep it the same as Elastic's.

Manticore Search Version:

Manticore 6.3.3 1de3cb364@24062617 dev

Operating System Version:

Ununtu 22.04 LTS

Have you tried the latest development version?

Yes

Internal Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

- [ ] Implementation completed - [ ] Tests developed - [ ] Documentation updated - [ ] Documentation reviewed - [ ] Changelog updated

[sanikolaev]

@Nick-S-2018 Does this issue block anything?

[sanikolaev]

Can you also provide an MRE of what we return now (starting from create table ...) and what Kibana expects (what we should return instead of what we return now)?

[Nick-S-2018]

Here is an MRE:

 curl localhost:9408/cli -d 'create table test(f timestamp)'

 curl localhost:9408/cli -d "insert into test(f) values ('2024-01-01T15:00:00Z')"

curl localhost:9408/test/_search -d '
{"aggs":{"3":{"date_range":{"field":"f","ranges":[{"from":"2024-01-01","to":"2024-01-02"}],"time_zone":"Asia/Novosibirsk"}}},"size":0,"stored_fields":["*"],"script_fields":{},"docvalue_fields":[{"field":"f","format":"date_time"}],"_source":{"excludes":[]},"query":{"bool":{"must":[],"filter":[],"should":[],"must_not":[]}}}
'

The current response is:

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "total_relation": "eq",
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "3": {
      "buckets": [
        {
          "key": "2024-01-01T00:00:00-2024-01-02T00:00:00",
          "from": "2024-01-01T00:00:00",
          "to": "2024-01-02T00:00:00",
          "doc_count": 1
        }
      ]
    }
  },
  "status": 200
}

The response we need is:

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "total_relation": "eq",
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "3": {
      "buckets": [
        {
          "from": 1704042000000.0,
          "from_as_string": "2024-01-01T00:00:00.000+07:00",
          "key": "2024-01-01T00:00:00.000+07:00-2024-01-02T00:00:00.000+07:00",
          "to": 1704128400000.0,
          "to_as_string": "2024-01-02T00:00:00.000+07:00"
          "doc_count": 1
        }
      ]
    }
  },
  "status": 200
}

[Nick-S-2018]

@Nick-S-2018 Does this issue block anything?

No, it causes minor discrepancies in date containing results between Manticore and Elastic, but this isn't something critical, I think.