How to Group by Time in Snowflake

When you need to group by minute, hour, day, week, etc., you may think you can simply group by your timestamp column.

If you do that, though, you'll get one group per second -- probably not what you want. Instead you need to “truncate” your timestamp to the granularity you want, like minute, hour, day, week, etc. The function you need here isdate_trunc():

-- returns number of sessions grouped by particular timestamp fragment
select
  date_trunc('DAY',start_date), --or WEEK, MONTH, YEAR, etc
  count(id) as number_of_sessions
from sessions
group by 1
;
Cta

Better SQL for the people

Get more done with PopSQL and Snowflake