timestamp - 在 SQL Snowflake 中将时间戳截断为自定义时间单位

Question

Snowflake 中的函数 DATE_TRUNC(文档 here)允许您将给定的时间戳截断为给定的默认时间单位，可用:

 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE' and 'SECOND'

举个例子:

DATE_TRUNC('MINUTE', '2015-05-08T23:39:35.123') --> 2015-05-08T23:39:00.000'

如果我们想将日期截断为自定义时间单位，我们会怎么做:

 '5 MINUTE' OR '30 SECONDS'

例如，这些自定义时间将给出:

DATE_TRUNC('30 SECONDS', '2015-05-08T23:39:35.123') --> 2015-05-08T23:39:30.000'
DATE_TRUNC('30 SECONDS', '2015-05-08T23:39:15.123') --> 2015-05-08T23:39:00.000'

DATE_TRUNC('5 MINUTES', '2015-05-08T23:39:35.123') --> 2015-05-08T23:35:00.000'
DATE_TRUNC('5 MINUTES', '2015-05-08T23:34:35.123') --> 2015-05-08T23:30:00.000'

是否有解决方法或替代函数来获得这些结果？

谢谢

Answer 1

更改为 epoch seconds , 部门, floor , 相乘

因此 30 秒步骤:

SELECT 
    TO_TIMESTAMP_NTZ('2015-05-08T23:39:35.123') as date
    ,DATE_PART(epoch_second, date) as epoch_sec
    ,floor(epoch_sec / 30) * 30 as epoch_in_30sec_steps
    ,TO_TIMESTAMP_NTZ(epoch_in_30sec_steps) as time_in_30sec_steps;

给出:

<表类="s-表"><头><日>日期 EPOCH_SECEPOCH_IN_30SEC_STEPSTIME_IN_30SEC_STEPS<正文>2015-05-08 23:39:35.123143112837514311283702015-05-08 23:39:30.000

因此 5 分钟是:

TO_TIMESTAMP_NTZ(floor(epoch_sec / 300) * 300) as time_in_5min_steps