How to use the datebucket filter

Question

Trying to use the :datebucket filter but it doesn't seem to work.

select date, address from database.table where address = 'xyz' group by :datebucket(date)

This returns the error that date isn't in the group by statement, but it is. If it add it separately to the group by statement, it just groups by the individual date instead of respecting the date bucket selection.

Not finding anything in the Snowflake documentation about how this filter is suppose to work, just that it exists.

Answer 1

In this site: https://www.webagesolutions.com/blog/querying-data-in-snowflake was example like this about databucket function

SELECT COUNT(ORDER_DATE) as COUNT_ORDER_DATE, ORDER_DATE 
FROM ORDERS 
GROUP BY :datebucket(ORDER_DATE), ORDER_DATE 
ORDER BY COUNT_ORDER_DATE DESC; 

So could your query work if it was modified like this:

SELECT 
  date, 
  address 
FROM
  database.table 
WHERE 
  address = 'xyz' 
GROUP BY :datebucket(date), date

Answer 2

Datebucket is truncating the date, to buckets. But you have selected the raw date.

This is like grouping by decade '60,'70,'80 of what great years, but want the actual year.

SELECT column1 as year,
    truncate(year,-1) as decade
FROM VALUES (1),(2),(3),(14),(15),(16),(27),(28),(29);

gives:

YEAR	DECADE
1	0
2	0
3	0
14	10
15	10
16	10
27	20
28	20
29	20

so if I try select

SELECT column1 as year
FROM VALUES (1),(2),(3),(14),(15),(16),(27),(28),(29)
GROUP BY truncate(year,-1)
ORDER BY 1;

gives the error

Error: 'VALUES.COLUMN1' in select clause is neither an aggregate nor in the group by clause. (line 15)

So if we move the decade into the selection, it makes sense:

SELECT truncate(column1,-1) as decade
FROM VALUES (1),(2),(3),(14),(15),(16),(27),(28),(29)
GROUP BY decade
ORDER BY 1;

and we get the

DECADE
0
10
20

So the problem is not :datebucket(date) but the fact while :datebucket(date) and date are related, from the perspective of GROUPING they are unrelated.