在 Snowflake/SQL 中将 Current_Timestamp 向下舍入到最近的半小时 [英] Round Down Current_Timestamp to Nearest Half Hour in Snowflake/SQL

问题描述

我正在尝试将 CURRENT_TIMESTAMP 向下舍入到雪花中最近的半小时.在 SQL 中，下面的代码会将时间戳 '2021-01-01 10:35:00' 舍入到 '2021-01-01 10:30:00' 或 '2021-01-01 17:20:00' 到'2021-01-01 17:00:00' 但是，此语法在 Snowflake 中不起作用.有谁知道如何在雪花中做到这一点?

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)

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

推荐答案

从纪元转换为秒，除以1800，取整，回到时间戳:

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               

或任意时间戳:

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     

这篇关于在 Snowflake/SQL 中将 Current_Timestamp 向下舍入到最近的半小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！