# How to change start of the week in date\_histogram based on 1w interval in group\_by transformof pivot

**URL:** <https://discuss.elastic.co/t/how-to-change-start-of-the-week-in-date-histogram-based-on-1w-interval-in-group-by-transformof-pivot/323675>\
**Category:** Elasticsearch\
**Tags:** transforms\
**Created:** [January 22, 2023, 12:41pm UTC](https://discuss.elastic.co/t/how-to-change-start-of-the-week-in-date-histogram-based-on-1w-interval-in-group-by-transformof-pivot/323675 "2023-01-22T12:41:38Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![sagarkhatri0605](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sagarkhatri0605/32/116254_2.png) [@sagarkhatri0605](https://discuss.elastic.co/u/sagarkhatri0605)\
**Post date:** [January 22, 2023, 12:41pm UTC](https://discuss.elastic.co/t/how-to-change-start-of-the-week-in-date-histogram-based-on-1w-interval-in-group-by-transformof-pivot/323675/1 "2023-01-22T12:41:38Z")

</div>

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](https://github.com/elastic/elasticsearch/issues/86452)

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.

---

<div class="post-metadata">

**Author:** ![przemekwitek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/przemekwitek/32/79526_2.png) [@przemekwitek](https://discuss.elastic.co/u/przemekwitek)\
**Post date:** [January 23, 2023, 10:49am UTC](https://discuss.elastic.co/t/how-to-change-start-of-the-week-in-date-histogram-based-on-1w-interval-in-group-by-transformof-pivot/323675/2 "2023-01-23T10:49:48Z")

</div>

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](https://github.com/elastic/elasticsearch/issues/86452)) 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](https://www.elastic.co/guide/en/elasticsearch/reference/current/runtime.html) if you haven't already.

---

<div class="post-metadata">

**Author:** ![sagarkhatri0605](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sagarkhatri0605/32/116254_2.png) [@sagarkhatri0605](https://discuss.elastic.co/u/sagarkhatri0605)\
**Post date:** [January 25, 2023, 4:58am UTC](https://discuss.elastic.co/t/how-to-change-start-of-the-week-in-date-histogram-based-on-1w-interval-in-group-by-transformof-pivot/323675/3 "2023-01-25T04:58:52Z")

</div>

Thanks a lot Przemysław for quick response

---

<div class="post-metadata">

**Author:** ![przemekwitek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/przemekwitek/32/79526_2.png) [@przemekwitek](https://discuss.elastic.co/u/przemekwitek)\
**Post date:** [January 25, 2023, 1:08pm UTC](https://discuss.elastic.co/t/how-to-change-start-of-the-week-in-date-histogram-based-on-1w-interval-in-group-by-transformof-pivot/323675/4 "2023-01-25T13:08:26Z")

</div>

FYI, I've just merged [this PR](https://github.com/elastic/elasticsearch/pull/93203) 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`.

---

<div class="post-metadata">

**Author:** ![sagarkhatri0605](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sagarkhatri0605/32/116254_2.png) [@sagarkhatri0605](https://discuss.elastic.co/u/sagarkhatri0605)\
**Post date:** [February 1, 2023, 10:55am UTC](https://discuss.elastic.co/t/how-to-change-start-of-the-week-in-date-histogram-based-on-1w-interval-in-group-by-transformof-pivot/323675/5 "2023-02-01T10:55:09Z")

</div>

Thanks Przemysław.

When is the 8.7.0 release planned?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 1, 2023, 10:55am UTC](https://discuss.elastic.co/t/how-to-change-start-of-the-week-in-date-histogram-based-on-1w-interval-in-group-by-transformof-pivot/323675/6 "2023-03-01T10:55:48Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.