Date_bin grouping

CrateDB SQL
1

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-11T03:00:00.000Z
2022-09-12T03:00:00.000Z
…
2022-09-17T03:00:00.000Z

but I’m getting

2022-09-11T00:00:00.000Z
2022-09-12T00:00:00.000Z
…
2022-09-17T00:00:00.000Z

Is date_bin the wrong function ? should I use a window function ?

Many thanks in advance
David.

1 Like
2

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.

3 Likes
3

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