Why DATEDIFF() function in Snowflake works differently while getting date difference in weeks

The week numbers differ between both examples:

SELECT '2022-09-05T23:39:20.123-07:00'::TIMESTAMP AS col, WEEK(col),
       '2022-09-18T01:39:20.123-07:00'::TIMESTAMP AS col2, WEEK(col2),
       DATEDIFF('week', col, col2)
UNION ALL     
SELECT '2022-09-07T23:39:20.123-07:00'::TIMESTAMP AS col, WEEK(col),
       '2022-09-20T01:39:20.123-07:00'::TIMESTAMP AS col2, WEEK(col2),
       DATEDIFF('week', col, col2);

Output:

A simpler example:

ALTER SESSION SET WEEK_START = 1; -- Monday  
       
SELECT DATEDIFF('week', '2022-09-11', '2022-09-12');
-- 1 week, even though there is one day difference

If the goal is to calcualte the weeks not as week numbers but rather a number of days, then more appriate code should be:

SELECT '2022-09-05T23:39:20.123-07:00'::TIMESTAMP AS col, 
       '2022-09-18T01:39:20.123-07:00'::TIMESTAMP AS col2, 
       FLOOR(DATEDIFF('DAY', col, col2)/7)
UNION ALL     
SELECT '2022-09-07T23:39:20.123-07:00'::TIMESTAMP AS col, 
       '2022-09-20T01:39:20.123-07:00'::TIMESTAMP AS col2,
       FLOOR(DATEDIFF('DAY', col, col2)/7);

Output:

the datediff truncate to the unit you are finding the diff over.

I will use floating point maths to make my point

Some time you expect the diff in "days" between 1.9 and 2.1 to be 0.2 days, but Snowflake will produce 1 because 2 is 1 more than 1.

It does this for every unit, second, hour, day, month.. to get a cleanly formatted duration like you might in PostgreSQL you have to roll your own functions.

select column1::timestamp as a
    ,column2::timestamp as b
    ,column3
    ,case column3
        when 'second' then datediff('seconds', a, b) 
        when 'minute' then datediff('minute', a, b) 
        when 'hour' then datediff('hour', a, b) 
        when 'year' then datediff('year', a, b) 
    end as diff
    ,case column3
        when 'second' then datediff('millisecond', a, b)/1000 
        when 'minute' then datediff('second', a, b)/60
        when 'hour' then datediff('minute', a, b)/60
        when 'year' then datediff('day', a, b)/365
    end as f_diff
from values
('2022-09-12 13:16:59.999','2022-09-12 13:17:01.001', 'second'),
('2022-09-12 13:16:59','2022-09-12 13:17:01', 'minute'),
('2022-09-12 13:59:59','2022-09-12 14:03:01', 'hour'),
('2022-09-12 13:16:59','2023-01-12 13:17:01', 'year');

gives: