ESQL: optimize date grouping with formatting

[costin]

Description

It's common when dealing with date grouping/histograms to perform date truncation and formatting at the same time - for example, assume I want to group by month AND format the date to something else:
The natural way to do that is :

FROM mydata
| STATS my_sum = SUM(fieldtosum) BY month = DATE_FORMAT("yyyy-MM", @timestamp)

However a more efficient way to do this would be:

FROM mydata 
// group on date trunction first
| STATS my_sum = SUM(fieldtosum) BY month= DATE_TRUNC(1 month, @timestamp) 
// then perform the formatting
| EVAL group = DATE_FORMAT("yyyy-MM", month) 

That is, we'd like to first group on the date (using truncation) - potentially by applying filters and apply the formatting just on the results.

P.S. Since at the moment DATE_TRUNC is not optimized, there's not much difference between the two.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[bpintea]

a more efficient way to do this would be

This wouldn't be (potentially) more efficient only, it'd also be correct: DATE_TRUNC() and BUCKET() allows now to provide a Period or Duration, which will then prompt the appropriate rounding (considering TZ and DST). DATE_FORMAT() doesn't.

[jackpan123]

@costin Can I have a try?

[jackpan123]

This should display the month field in the format yyyy-MM when I execute this ESQL query as shown below:

FROM mydata 
// group on date trunction first
| STATS my_sum = SUM(fieldtosum) BY month= DATE_TRUNC(1 month, @timestamp) 

On then other hand, this should display the year field in the format yyyy when I execute the following ESQL query:

FROM mydata 
// group on date trunction first
| STATS my_sum = SUM(fieldtosum) BY year= DATE_TRUNC(1 year, @timestamp)