Spec gap: DATEDIFF does not say whether it counts boundaries crossed or whole units elapsed
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
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)
- Define
DATEDIFFas counting boundaries ofpartcrossed (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)formonth; or - 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
- No Dockerfile or Docker Compose file
- Has a pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from apache/ossie
-
needs-triage
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
Maintainers usually reply within 1 day
-
validation: field reference resolution is case-insensitive but name uniqueness is case-sensitivePossibly taken @MikeNitsenko claimed this 6 days ago. Openneeds-triage
Difficulty 5/5 Over a week Newbie friendliness 25/100
Maintainers usually reply within 1 day
-
needs-triage
Difficulty 5/5 Over a week Newbie friendliness 25/100
apache/ossie#710 · 6 comments ·
Maintainers usually reply within 1 day
-
needs-triage
Difficulty 5/5 Over a week Newbie friendliness 25/100
apache/ossie#707 · 3 comments ·
Maintainers usually reply within 1 day
-
needs-triage
Difficulty 5/5 Over a week Newbie friendliness 30/100
apache/ossie#694 · 3 comments ·
Maintainers usually reply within 1 day
Similar issues
-
integration: media_player
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
home-assistant/core#193344 · 2 comments ·
Maintainers usually reply within 1 day
-
Difficulty 1/5 Under an hour Newbie friendliness 82/100
daniel-ospina/tortoise#13607 ·
Maintainers usually reply within 1 day
-
documentation priority: medium
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 66/100
Maintainers usually reply within 1 day
-
Difficulty 1/5 1-3 hours Newbie friendliness 66/100
Robin-IA/Congressional-Letters-Dev#1347 ·
Maintainers usually reply within 1 day