date_diff operation works differently on Postgres and Snowflake

[radeusgd]

In either backend our date_diff operation delegates to SQL DATE_DIFF/DATEDIFF.

However, the two backends have differing semantics in some edge cases.

See parts of the test "should allow computing a SQL-like difference":

t1 = table_builder [["X", [Date.new 2021 11 3]], ["Y", [Date.new 2021 12 5]]]
(t1.at "X").date_diff (Date.new 2021 12 1) Date_Period.Month . to_vector . should_equal [0]

In the Snowflake backend this yields 1 instead of 0.

That is because, as documented, when computing a month difference, only year and month fields are considered - so the DB sees that the month differs and returns 1, disregarding the day field that shows us that not a full month passed yet.

In Snowflake we can check that even:

SELECT DATEDIFF('month', '2021-11-30', '2021-12-01')

which compares two dates just 1 day apart, still yields a 1 month difference if asked for months.

[radeusgd]

Possible solutions:

• try implementing our own date diff
  • maybe we could compare the differences in both days and months and compute an effective difference from that? But it may make the queries very complex, causing similar problems like we have with banker's rounding: Banker's rounding creates extremely complex query in Postgres #10306
• ignore the inconsistency
• ignore the inconsistency but warn about it
  • because it is the Snowflake behaviour that is rather unexpected, we could warn the user if the Month date period is used in date_diff in Snowflake and tell them that the results will be inconsistent with in-memory or some other DBs. Then the user can choose to perform the computation in-memory, or acknowledge the inconsistency.
• something else?