I want to round off timestamp up to second in snowflake. e.g. 2023-09-03 08:17:48.919 should be converted to 2023-09-03 08:17:49

我想在雪花中将时间戳四舍五入到秒。如2023-09-03 08：17：48.919应转换为2023-09-03 08：17：49

I tried using TO_CHAR(column_name, 'YYYY-MM-DD HH24:MI:SS') but it is not doing roundoff.

我尝试使用TO_CHAR(COLUMN_NAME，‘YYYY-MM-DD HH24：MI：SS’)，但它不执行舍入。

select TO_CHAR(column_name, 'YYYY-MM-DD HH24:MI:SS') from table_name

SELECT FORMAT(DATEADD(SECOND, CASE WHEN DATEPART(MILLISECOND, '2023-09-03 
08:17:48.919') >= 500 THEN 1 ELSE 0 END, '2023-09-03 08:17:48.919'), 'yyyy-MM-dd 
HH:mm:ss') AS rounded_timestamp;

So the classic way to "round anything" is to add half the size of the unit of rounding (thus here 500 milliseconds) and then truncate towards zero. But given time tends to not be negative (yes it can, but I am not solving for that)..

因此，“舍入任何东西”的经典方法是将舍入单位大小的一半(这里是500毫秒)相加，然后向零截断。但是，如果时间不是负的(是的，它可以，但我不会解决这个问题)..

we can use DATEADD and DATE_TRUNC like so:

我们可以这样使用DATEADD和DATE_TRUNC：

select $1 as input
    ,dateadd('milliseconds', 500, input) as shift_up
    ,date_trunc('second', shift_up) as rounded_to_sec
from values 
    ('2023-09-03 08:17:48.919'::timestamp),
    ('2023-09-03 08:17:49.419'::timestamp);

gives the answer we are expecting.

给出了我们期待的答案。