FAQ: How to round a date to an arbitrary precision?

Here’s another FAQ we see:
How do I round a Datetime to the nearest 5 minute increment?

DateTrunc, for context, only allows the following precisions: “year”, “quarter”, “month”, “week”, “week_starting_sunday”, “week_starting_monday”, “day”, “hour”, “minute”, and “second”

The following formula works for any warehouse:
Date(Div(DatePart("epoch", [Date]), 300) * 300)

This converts the date to epoch time using the DatePart function, then uses the Div function with a 300 second (5 minute) divisor to return the integer component of a division operation, multiplied by 300 (5 minutes) and finally converted back to a date format.

In Snowflake, one can use the CallDatetime passthrough function to pass the TIME_SLICE function to Snowflake. The TIME_SLICE function doesn’t support TIMESTAMP_LTZ so it’s necessary to first convert to TIMESTAMP_NTZ.

For example:
CallDatetime("TIME_SLICE", CallDatetime("TO_TIMESTAMP_NTZ", [Date]), 5, "MINUTE")

1 Like

Added faq

Added DateTrunc, Featured How-To Articles