How to Round Timestamps in Snowflake using date_trunc Function
Rounding and/or truncating timestamps is useful when you're grouping by time. There are a few approaches.
The DATE_TRUNC function
select date_trunc('second', now()) -- or minute, hour, day, month
Predefined functions in Snowflake
If you are rounding by year, you can use the year()
function (or month()
, week()
, day()
, etc:
select year(getdate()) as year;
Be careful though. Using the month()
function will, for example, make January 2020 and January 2019 both just translate to 1
. That may not be what you want
Round and format result as a string, e.g. '05-2020'
However, if you want to distinguish between months of different years, you need to use to_varchar()
function:
select to_varchar(getdate(), 'mm-yyyy'); -- round to month
select to_varchar(getdate(),'dd-mm-yyyy'); -- round to day
select to_varchar(getdate(),'dd-mm-yyyy hh'); -- round to hour
select to_varchar(getdate(),'dd-mm-yyyy hh:mm'); -- round to minute
select to_varchar(getdate(),'dd-mm-yyyy hh:mm:ss'); -- round to second
Previous
How to Query Date and Time