How can I select every nth date in Snowflake?

Question

Say I have a table in Snowflake with fields delivery_date, formatted as a DATETIME, and daily_volume formatted as an INT. Daily_volume follows a pattern that repeats every 10 days.

How would I query this table to compare today's daily_volume, compared to the historical average of that same point in the 10 day cycle?

My first instinct would be to do something like:

WHERE MOD(current_date(),10) = MOD(delivery_date,10)

then take the average, but Snowflake doesn't seem to have any easy way to convert a DATETIME to an INT so that I can make the MOD function work.

ChatGPT suggested something along the lines of

WHERE MOD(DATEDIFF('days', '1970-01-01'::DATE, current_date), 10) 
   = MOD(DATEDIFF('days', '1970-01-01'::DATE, delivery_date), 10)

which works, I guess, but I'm guessing there's a better way?

Answer 1

TIME_SLICE:

Calculates the beginning or end of a “slice” of time, where the length of the slice is a multiple of a standard unit of time (minute, hour, day, etc.).

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

SELECT 
    TIME_SLICE(delivery_date, 10, 'DAY', 'START') AS START_OF_SLICE
   ,TIME_SLICE(delivery_date, 10, 'DAY', 'END') AS END_OF_SLICE
FROM tab

---

EDIT:

date - '1970-01-01'::DATE - returns number of days

WHERE MOD(current_date() - '1970-01-01'::DATE,10) 
    = MOD(delivery_date - '1970-01-01'::DATE,10)