Explained: TIME_SLICE() function in Snowflake?

Are you looking to find how to use the TIME_SLICE() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, or may be you are looking how to slice the time in Snowflake using any function? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what is it and how to use TIME_SLICE() function. I will explain the TIME_SLICE() function by taking a practical example. So don’t waste time, let’s start a step by step guide to understand the TIME_SLICE() expression.

What is TIME_SLICE() Function ?

TIME_SLICE() function helps in calculating the start or end of a ‘slice’ of time. The length of the slice standard unit can be hour, minute, day and many more.

How can we use TIME_SLICE() Function ?

TIME_SLICE() function can be used to calculate the start and end times of fixed-width “buckets” into which data can be categorized

What is the Syntax of the TIME_SLICE function?

-- Syntax :
TIME_SLICE(<date_or_time_expr>, <slice_length>, <date_or_time_part> [,<start_or_end>]

TIME_SLICE() Argument Details:

Date or time expression:

The expression must be of type DATE or TIMESTAMP_NTZ.

Slice length:

It indicates the width of the slice, basically the number of units. For example: if the unit is YEAR and <slice_length> is 5, then each slice will be of 5 years.

Date or Time part:

This is the time unit of the slice length and it must contain one of the below strings:

For DATE input expression, it could be YEAR, QUARTER, MONTH, WEEK, DAY and for TIMESTAMP_NTZ input expression, it could be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND.

Start or End:

This is the optional parameter which determines whether the start or end of slice should be returned.

Examples of TIME_SLICE() function in Snowflake:

Example for time_slice is as follows:

Select ‘2022-02-28’ :: date as “DATE”
time_slice("DATE", 8, 'MONTH', 'START') as "START OF SLICE",
time_slice("DATE", 8, 'MONTH', 'END') as "END OF SLICE", 
+------------------+------------------------+---------------------+
| DATE             | START OF SLICE| END OF SLICE |
+------------------+------------------------+---------------------+
| 2022-02-28    | 2022-02-01          | 2022-10-01 	     |
+------------------+------------------------+---------------------+

What is the difference between DATE and TIME_SLICE?

Ideally there shouldn’t be comparison between the date and the time_slice function. Reason is simple, date is data type and time_slice is function.

When you should use TIME_SLICE() Function in Snowflake?

There are certain use case scenarios when it is recommended to use the TIME_SLICE function within the Snowflake cloud data warehouse which are as follows: 

• You will use it, In case you need to get the start and end range. For example assume that you might be looking for quarterly date range then you can use time_slice there.

When should you not use TIME_SLICE() Function in Snowflake?

• You shouldn’t use the Time_Slice when you want to just get only the month, year, date. This function doesn’t give the specific month date however it will return entire date.
• You can’t use the Time_Slice when your slice size is as millisecond.

Real World Use Case Scenarios for TIME_SLICE Function in Snowflake

• Let’s consider there are 3 unpaid bills of 3 different dates between 2 weeks, one can calculate the total unpaid amount by putting the slice length of 2 weeks.

TIME_SLICE() Snowflake Official Documentation Link

Limitations of TIME_SLICE() Function in Snowflake?

• Time slicing can be done only for YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND
• Time slicing need an input as Date or Timestamp.

Final Thoughts

In this article, we have learned about TIME_SLICE function and its uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible in normal scenarios. I hope this information which is provided here helped in gaining the knowledge and make you action ready on Time_Slice.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

• Date_Trunc() in Snowflake
• Date_Add() in Snowflake
• Parse_Json() in Snowflake
• Insert() in Snowflake
• Right() in Snowflake
• StartsWith() in Snowflake
• Replace () in Snowflake
• Position() function
• ListAgg() function
• Like() Function
• LikeAll() Function
• Like Any() Function
• Current_Date() Function
• Row_Number() Function
• Cast() Function
• Show() Function
• ISNULL() Function
• Lag() Function
• ILIKE() Function
• DateDiff() Function
• Substring() Function
• ILIKE ANY() Function
• Round() Function
• To_Date() Function
• Concat() Function
• Length() Function
• TRIM(), LTRIM(), RTRIM()
• SPLIT() Function
• Reverse() Function
• Repeat() Function
• Unicode() Function
• IFNULL() Function
• Pivot() Function
• IFF() Function

Prateek Gupta