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.