# SQL histogram returns unexpected offset

**URL:** <https://discuss.elastic.co/t/sql-histogram-returns-unexpected-offset/289187>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [November 15, 2021, 11:10am UTC](https://discuss.elastic.co/t/sql-histogram-returns-unexpected-offset/289187 "2021-11-15T11:10:01Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![HarmenB](https://avatars.discourse-cdn.com/v4/letter/h/b9e5f3/32.png) [@HarmenB](https://discuss.elastic.co/u/HarmenB)\
**Post date:** [November 15, 2021, 11:10am UTC](https://discuss.elastic.co/t/sql-histogram-returns-unexpected-offset/289187/1 "2021-11-15T11:10:01Z")

</div>

I'm trying to create a Histogram query using the SQL language. My goal is to get 3 buckets covering 30 days each. The following query does work but gives me an unexpected offset:

```auto
POST _sql?format=txt
{ 
  "query": "SELECT HISTOGRAM (\"@timestamp\" , INTERVAL 30 DAY) AS date_event_startperiod, MIN(\"@timestamp\") as minTimestamp, MAX(\"@timestamp\") as maxTimestamp, COUNT (*) as event_count FROM myindex-* WHERE \"@timestamp\" BETWEEN DATE_TRUNC('day', NOW() - INTERVAL 90 DAY) AND DATE_TRUNC('day', NOW()) GROUP BY date_event_startperiod" }

```

The firstbucket is indicated as "date\_event\_startperiod" = '2021-08-01T00:00:00.000Z' and the 2nd 30days later. However the first document in this bucket dates from 2021-08-17 (which corresponds with the " NOW()-INTERVAL 90 DAYS" ).

so this is the full table I get back

```auto
 date_event_startperiod | minTimestamp | maxTimestamp | event_count  
------------------------+------------------------+------------------------+---------------
2021-08-01T00:00:00.000Z|2021-08-17T00:00:00.021Z|2021-08-30T23:59:59.917Z|42950380       
2021-08-31T00:00:00.000Z|2021-08-31T00:00:00.351Z|2021-09-29T23:59:59.817Z|112407968      
2021-09-30T00:00:00.000Z|2021-09-30T00:00:00.807Z|2021-10-29T23:59:59.201Z|120844970      
2021-10-30T00:00:00.000Z|2021-10-30T00:00:00.140Z|2021-11-14T23:59:59.860Z|55325122  

```

Any suggestions how to fix this?

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [November 17, 2021, 4:38pm UTC](https://discuss.elastic.co/t/sql-histogram-returns-unexpected-offset/289187/2 "2021-11-17T16:38:09Z")

</div>

This is how the `date_histogram` works in Elasticsearch (ES SQL uses a `date_histogram` aggregation for a date sql HISTOGRAM) and the value you see there is the start date of the 30 days bucket in which those documents fall in. You could use the `_translate` API and see what query DSL we generate and run that in ES itself.

What was your expectation regarding the output of the HISTOGRAM function?

---

<div class="post-metadata">

**Author:** ![HarmenB](https://avatars.discourse-cdn.com/v4/letter/h/b9e5f3/32.png) [@HarmenB](https://discuss.elastic.co/u/HarmenB)\
**Post date:** [November 17, 2021, 5:42pm UTC](https://discuss.elastic.co/t/sql-histogram-returns-unexpected-offset/289187/3 "2021-11-17T17:42:22Z")

</div>

@Andrei_Stefan thanks a lot for reacting to my post!  
I was hoping (maybe not so much expecting ;)) that my WHERE clause would effect where the HISTOGRAM would start. Imho the start of the month is somewhat arbritary, why not the start of the year?  
So is there a way for me to influence where the HISTOGRAM starts, so the result of my query would give me 3 buckets of each 30days " wide" which would cover exactly the last 90 days?

---

<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:** [December 15, 2021, 5:42pm UTC](https://discuss.elastic.co/t/sql-histogram-returns-unexpected-offset/289187/4 "2021-12-15T17:42:52Z")

</div>

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