ESQL: Docs: date histogramoffset parameter equivalent #114167
Description
Description
It might be useful to document how to replicate date histogram agg's offset parameter in ESQL.
Example:
{
"aggs": {
"sales_over_time": {
"date_histogram": {
"field": "@timestamp",
"calendar_interval": "1w",
"offset": "-1d"
}
}
}
}
The date_histogram applies the requested rounding, then the offset and obtains a bucket value "in one go".
An ESQL equivalent | STATS ... BY b = BUCKET(@timestamp, 1 week) - 1 day would be wrong, since expressions in STATS are supported by implicit subsequent EVALs (which is not documented -- should it?), so the - 1 day wouldn't be considered for bucketing.
A | STATS ... BY b = BUCKET(@timestamp - 1 day, 1 week) would be wrong as well, since it'd first apply the offset and only afterwards apply the rounding (instead of the other way around).
Meaning that the offset needs an "opposing" compensation: | STATS ... BY b = BUCKET(@timestamp + 1 day, 1 week) - 1 day.