Round Down Current_Timestamp to Nearest Half Hour in Snowflake/SQL

Question

I am trying to round CURRENT_TIMESTAMP down to the nearest half hour in Snowflake. In SQL, the code below would round a timestamp of '2021-01-01 10:35:00' to '2021-01-01 10:30:00' or '2021-01-01 17:20:00' to '2021-01-01 17:00:00', however, this syntax doesn't work in Snowflake. Does anyone know how to do this in Snowflake?

SELECT DATEADD(mi, DATEDIFF(mi, 0, GETDATE())/30*30, 0)

Answer 1

There is a built-in function in Snowflake for this called time_slice.

select time_slice(current_timestamp::timestamp_ntz, 30, 'MINUTE');

Answer 2

Transform to seconds from epoch, divide by 1800, round, and bring back to timestamp:

select to_timestamp(
  round(
    date_part(epoch_second, current_timestamp())/1800
  )*1800) nearest_half_hour  

# 2021-02-27T01:30:00Z               

Or any arbitrary timestamp:

select to_timestamp(
  round(
    date_part(epoch_second, to_timestamp('2020-10-10 17:51:01'))/1800
  )*1800) nearest_half_hour    

# 2020-10-10T18:00:00Z