How to Group by Time in Snowflake 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
  date_trunc('DAY',start_date), --or WEEK, MONTH, YEAR, etc 
  count(id) as number_of_sessions
from sessions
group by 1

Finally, a unified workspace for your SQL development

Get more done, together, with PopSQL and Snowflake