# Date\_bin grouping

**URL:** <https://community.cratedb.com/t/date-bin-grouping/1249>\
**Category:** SQL\
**Created:** [October 12, 2022, 2:17pm UTC](https://community.cratedb.com/t/date-bin-grouping/1249 "2022-10-12T14:17:35Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [October 12, 2022, 2:17pm UTC](https://community.cratedb.com/t/date-bin-grouping/1249/1 "2022-10-12T14:17:35Z")

</div>

Hi all,

I’ve got a bit of an issue understanding **date\_bin** , as its not returning what I think it should.

I’ve got a data set that is 1 minute apart for each row and I’m doing a specific operation of dealing with one day at a time

> SELECT  
> date\_bin(cast(‘1 DAY’ as interval), basetable.ts, 0) AS bucketts
> 
> FROM safe\_1526595589 AS basetable  
> WHERE basetable.id = ‘801f125aa65d0001’  
> AND basetable.roundts BETWEEN CAST(‘1662908400000’ AS TIMESTAMP WITH TIME ZONE) AND CAST(‘1663426800000’ AS TIMESTAMP WITH TIME ZONE )  
> group BY 1  
> order by 1;

The date range is from  
Sunday, September 11, 2022 **3:00:00** PM  
to  
Saturday, September 17, 2022 **3:00:00** PM

So I’m expecting 7 rows , a day apart , with timestamps with 3pm :-

2022-09-11T **03:00** :00.000Z  
2022-09-12T **03:00** :00.000Z  
…  
2022-09-17T **03:00** :00.000Z

but I’m getting

2022-09-11T **00:00** :00.000Z  
2022-09-12T **00:00** :00.000Z  
…  
2022-09-17T **00:00** :00.000Z

Is date\_bin the wrong function ? should I use a window function ?

Many thanks in advance  
David.

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [October 12, 2022, 2:33pm UTC](https://community.cratedb.com/t/date-bin-grouping/1249/2 "2022-10-12T14:33:26Z")

</div>

Hi @djbestenergy,

this behavior is due to the `origin` parameter (the third one) when calling `date_bin`. When setting it `0` (equals to `1970-01-01T00:00:00.000Z`), the reference time will be midnight.

In your case, if you pass Sunday, September 11, 2022 3:00:00 PM as the origin, it should behave the way you expect with each date bin starting at 3 PM: `date_bin(cast('1 day' as interval), basetable.ts, 1662908400000) AS bucketts`.

---

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [October 12, 2022, 2:43pm UTC](https://community.cratedb.com/t/date-bin-grouping/1249/3 "2022-10-12T14:43:29Z")

</div>

Ah I see.  
Thanks for clearing that up. Thinking about it, I had copied the statement from elsewhere where the times involved midnight.