Spec gap: DATEDIFF does not say whether it counts boundaries crossed or whole units elapsed

Open
#743 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
52/100
Issue type
Documentation
Clarity
Mostly clear
Activity status
Active
Tech stack
python
Domain
documentation

Research direction

Start with core-spec/expression_language.md, in the Date Arithmetic section, and compare its current DATEDIFF definition with the Snowflake, DAX, and Databricks behaviors summarized in the issue. The open question is which semantics the spec should require; check the portability implications for the listed date parts. Done means the spec states a chosen behavior, or clearly limits the portable guarantee to DATEDIFF(day, ...) over DATE values.

Written by the indexing model from the issue text.

Description

needs-triage

core-spec/expression_language.md (Date Arithmetic, REQUIRED) defines DATEDIFF(part, start_date, end_date) as "Difference between dates", with DATEDIFF(month, start_date, end_date) -- Months between dates. Engines read that in two incompatible ways, so the same portable expression returns different numbers depending on where it runs. I ran into this while translating DAX DATEDIFF for the Microsoft converter (apache/ossie-converters#23).

Two readings

counts DATEDIFF(month, DATE '2015-12-31', DATE '2016-01-01') source
Snowflake part boundaries crossed: "compare the specified date or time part, not the complete date or time" 1 (and year is 1) docs
DAX "the number of interval boundaries between two dates" 1 docs
Databricks whole units elapsed: "one month is considered elapsed when the calendar month has increased" and the day and time are equal or later 0 (datediff(MONTH, TIMESTAMP'2021-02-28 12:00:00', TIMESTAMP'2021-03-28 11:59:59') is documented as 0, "one second shy of a month") docs

The difference is not an edge case: any month, quarter, year or week difference whose end is earlier in its period than the start (31 Dec to 1 Jan, 28 Nov to 5 Dec) differs by one. hour, minute and second differ the same way on timestamps, and so does day on timestamps (Databricks counts 86,400-second days; Snowflake and DAX count date boundaries). day on DATE values is the one case where both readings agree, which is also what the M6 portability fixture uses.

Proposal (either is fine, the point is to pick one)

  1. Define DATEDIFF as counting boundaries of part crossed (the Snowflake and DAX reading), and note that engines with elapsed-unit semantics must emit an equivalent, e.g. (YEAR(e) - YEAR(s)) * 12 + MONTH(e) - MONTH(s) for month; or
  2. Define it as whole units elapsed (the Databricks reading), with the equivalent noted for boundary engines.

If neither fits the current version, a note that only DATEDIFF(day, ...) over DATE values is portable would already let converters stay exact.

Until it is settled, the Microsoft converter refuses DAX DATEDIFF with that reason (apache/ossie-converters#175, which translates the other DAX date functions); once the spec picks a reading, it can translate it exactly.

Happy to open a PR against expression_language.md once there is agreement.

Dominant language
Python
Stars
2.7k
Forks
353
Avg merge
3d 19h
Merged PRs (30d)
86

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from apache/ossie

All issues in apache/ossie

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.