Why is date_diff different between Trino and Snowflake?

Question

This code on Trino/Presto says that the day difference is 22:

with period as (
  select
    timestamp '2017-01-09 10:49:49' as period_start,
    timestamp '2017-02-01 07:02:32' as period_end
)

select date_diff('day', period_start, period_end) as duration_days
from period

Snowflake says 23:

with period as (
  select
    timestamp '2017-01-09 10:49:49' as period_start,
    timestamp '2017-02-01 07:02:32' as period_end
)
select datediff(day, period_start, period_end) as duration_days
from period;

Why are the results of datediff different?

(source: conversation with a customer)

Answer 1

A more general form of the question is Snowflake takes the simpler approach, and answer all units of date_diff in the difference of the values at the unit compared.

Compared to true difference in values, and then that being expressed in a time unit.

Snowflake does:

unit_answer = TRUNC( unit, to_date ) -  TRUNC( unit, from_date); 

compared to:

unit_answer TRUNC( unit, to_date - from_date);

For simple spans like minute, hour, day, ether works (albeit maybe not how you expect), but once you are looking at year/month how many base units of days are in a year. And month many base units worth of days are in a month (28,29,30,31)??. So there is merit is Snowflakes way. I would just believe it is not the first class citizen datetime perspective, most people would be expecting.

Where as 31st Dec 2021 23:59:59 and 1st Jan 2022 00:00:01 are 2 seconds part, but are also 1 month apart, and 1 year apart.