How to change start of the week in date_histogram based on 1w interval in group_by transformof pivot

I am trying to create a transform with group_by date_histogram for interval of 1w (1 week), by default Elasticsearch considers week as Monday-Sunday and aggregates data accordingly, I want the week to start from Sunday instead, which could have been achieved with the help of offset but since offset is not supported for transform API, issue for the same is open on github: [Transform] Support `offset` parameter in transform's date_histogram implementation · Issue #86452 · elastic/elasticsearch · GitHub

does anyone have other alternative to achieve this?

One way I can think of to achieve this is by generating a new date as [existing date + 1 day], and create transform based on new date, this way activity happened on Sunday will be treated as activity on Monday and will be counted in the Monday-Sunday week.

e.g.
{
date: 2023-01-15T03:10:10.000Z (Sunday)
name: random
}
{
date: 2023-01-21T03:10:10.000Z (Saturday)
name: random
}
{
date: 2023-01-22T03:10:10.000Z (Sunday)
name: random
}
{
date: 2023-01-23T05:11:10.000Z (Monday)
name: random
}

In existing transform API group_by based on date_histogram of 1w interval, Week will be considered as Monday-Sunday and data will be aggregated accordingly.
Result considering value_count aggregation on name field:
{
date: 2023-01-09T00:00:00.000Z //Week starting from Monday 9-Jan-2023
result: 1
}
{
date: 2023-01-16T00:00:00.000Z //Week starting from Monday 16-Jan-2023
result: 2
}
{
date: 2023-01-23T00:00:00.000Z //Week starting from Monday 23-Jan-2023
result: 1
}

But I want week as Sunday-Monday, so by generating new date as [existing date + 1 day], sample data looks like
{
date: 2023-01-15T03:10:10.000Z (Sunday)
new_date: 2023-01-16T03:10:10.000Z (Monday)
name: random
}
{
date: 2023-01-21T03:10:10.000Z (Saturday)
new_date: 2023-01-22T03:10:10.000Z (Sunday)
name: random
}
{
date: 2023-01-22T03:10:10.000Z (Sunday)
new_date: 2023-01-23T03:10:10.000Z (Monday)
name: random
}
{
date: 2023-01-23T05:11:10.000Z (Monday)
new_date: 2023-01-24T03:10:10.000Z (Tuesday)
name: random
}

And the result of transform on new field will be
{
date: 2023-01-16T00:00:00.000Z //Week starting from Monday 16-Jan-2023
result: 2
}
{
date: 2023-01-23T00:00:00.000Z //Week starting from Monday 23-Jan-2023
result: 2
}

Do you see any issue with this approach? or is their any better alternative to achieve this?

Thanks in advance.

Hi,
Thanks for providing a concrete use-case for offset. I'll look into the issue you linked ([Transform] Support `offset` parameter in transform's date_histogram implementation · Issue #86452 · elastic/elasticsearch · GitHub) to find out if there is much work needed to implement this (hopefully not).
The workaround you suggested seems correct. You can achieve this using a new indexed field or a runtime field. Please read Runtime fields | Elasticsearch Guide [8.6] | Elastic if you haven't already.

Thanks a lot Przemysław for quick response

FYI, I've just merged this PR to enable support of offset parameter.
This will allow you to achieve your goal directly, without the workaround mentioned.

This fix is targeted at 8.7.0.