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

Aperta
#743 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
2/5
Tempo stimato
1-3 ore
Idoneità per principianti
52/100
Tipo di issue
Documentazione
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
python
Ambito
documentation

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.

Lingua principale
Python
Stelle
2.7k
Fork
353
Merge medio
3g 19h
PR unite (30g)
86

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di apache/ossie

Tutte le issue di apache/ossie

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.