'Why is date_diff different between Trino and Snowflake?

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)

sqlsnowflake-cloud-data-platformtrino


Solution 1:[1]

Date difference is 23, if you are counting number of different days.

I'm guessing that Trino also looks at the difference in hours between the two timestamps to approximate the result down if it's less than 24 hours.

As long as the timestamps are in different days, Snowflake counts the difference in days as 1, even if it's only 2 seconds across midnight:

select timediff(day, '2017-01-09 23:59:59', '2017-01-10 00:00:01')
-- 1

Snowflake's implementation seems to be in line with most other implementations, as most care about crossing boundaries.

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Felipe Hoffa
Solution 2 Simeon Pilgrim

Related Questions