andygrove opened a new issue, #237:
URL: https://github.com/apache/datafusion-spark/issues/237

   Part of #175 and apache/datafusion-comet#7300.
   
   ### What the function does
   
   `months_between(timestamp1, timestamp2[, roundOff])` returns the number of 
months from `timestamp2` to
   `timestamp1` as a `DOUBLE`. The result is positive when `timestamp1` is 
later. If both values fall on the
   same day of month, or both fall on the last day of their month, the time of 
day is ignored and the result is
   the whole-month difference. Otherwise the fractional part is computed 
assuming 31 days per month, and the
   result is rounded to 8 decimal places unless `roundOff` is `false` (default 
`true`).
   
   `inputTypes` is `(TIMESTAMP, TIMESTAMP, BOOLEAN)` with 
`ImplicitCastInputTypes`, so `DATE`, `STRING` and
   `TIMESTAMP_NTZ` arguments are implicitly cast to `TIMESTAMP` (session time 
zone). The expression is
   null-intolerant: any NULL argument (including a NULL `roundOff`) gives NULL. 
It is time-zone aware: both
   values are split into date and time-of-day in `spark.sql.session.timeZone`. 
Since 1.5.0. No aliases.
   
   ### Examples
   
   ```sql
   SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
   -- 3.94959677
   SELECT months_between('1997-02-28 10:30:00', '1996-10-30', false);
   -- 3.9495967741935485
   
   -- Same day of month: time of day is ignored
   SELECT months_between(timestamp'2015-09-16 12:00:00', date'2015-02-16');
   -- 7.0
   SELECT months_between(timestamp'2015-01-30 11:52:00', timestamp'2015-01-30 
11:50:00');
   -- 0.0
   
   -- Both last day of month: whole months, time ignored
   SELECT months_between(timestamp'2014-09-30 23:30:00', date'2015-07-31');
   -- -10.0
   SELECT months_between(timestamp'2015-01-31 00:00:00', timestamp'2015-03-31 
22:00:00');
   -- -2.0
   SELECT months_between(timestamp'2015-03-31 22:00:00', timestamp'2015-02-28 
00:00:00');
   -- 1.0
   
   -- Fractional: 15.5 days / 31
   SELECT months_between(timestamp'2014-09-30 23:30:00', '2014-09-15 11:30:00');
   -- 0.5
   
   -- Session time zone decides the calendar day (spark.sql.session.timeZone)
   SET spark.sql.session.timeZone=America/Los_Angeles;
   SELECT months_between(timestamp'2000-02-28 16:00:00', timestamp'1997-02-28 
16:00:00');
   -- 36.0
   SET spark.sql.session.timeZone=UTC;
   -- the same two instants, written in UTC
   SELECT months_between(timestamp'2000-02-29 00:00:00', timestamp'1997-03-01 
00:00:00');
   -- 35.90322581
   ```
   
   (The `-10.0`, `7.0`, `0.5` rows come from `DateFunctionsSuite`; the 
time-zone rows from `DateTimeUtilsSuite`,
   which evaluates the same instants with `LA` and `UTC`.)
   
   ### Edge cases to test
   
   - NULL in each of the three arguments, including `roundOff` NULL, returns 
NULL.
   - `roundOff` as a column, not just a literal (Spark accepts any BOOLEAN 
expression).
   - Same day of month with different times: whole months, time ignored, for 
both `roundOff` values.
   - Last day of month on both sides when the days differ (Feb 28 vs Mar 31; 
leap Feb 29 vs Feb 28 of a
     non-leap year): `months_between('1997-02-28 10:30:00', '2000-02-29')` is 
`-36` (from `DateTimeUtilsSuite`).
   - Feb 28 in a leap year is not the last day of month, so against a different 
day of month (e.g. Mar 31) the
     fractional path applies.
   - Rounding: Spark computes `Math.round(diff * 1e8) / 1e8`. Java `Math.round` 
rounds half toward positive
     infinity, while Rust `f64::round` rounds half away from zero, so negative 
results that land exactly on a
     half differ. Use `(x + 0.5).floor()` semantics.
   - Seconds of day are taken as whole seconds (`MICROSECONDS.toSeconds`), so 
sub-second parts are dropped
     before the fraction is computed; test two timestamps that differ only in 
microseconds on different days.
   - Seconds of day are measured from the start of the local day 
(`daysToMicros(date, zoneId)`), i.e. elapsed
     time since local midnight, not wall-clock fields. On a DST transition day 
in the session zone these differ
     by an hour (e.g. `America/Los_Angeles`, 2011-03-13 and 2011-11-06).
   - Calendar day is chosen in the session time zone, so the same pair of 
instants can switch between the
     whole-month and fractional paths depending on `spark.sql.session.timeZone` 
(see examples).
   - `DATE` arguments become midnight in the session zone; `STRING` arguments 
are cast to `TIMESTAMP`
     (invalid strings give NULL with ANSI off and `CAST_INVALID_INPUT` with 
`spark.sql.ansi.enabled=true`).
   - `TIMESTAMP_NTZ` arguments are cast to `TIMESTAMP` in the session zone 
first; in a DST gap the local time
     shifts forward.
   - Very old and far-future values (year 1, year 9999, beyond 9999, negative 
years): Spark uses the proleptic
     Gregorian calendar via `java.time`.
   - Scalar and array for each argument.
   
   ### Differences across Spark versions
   
   None found. `DateTimeUtils.monthsBetween` and the `MonthsBetween` expression 
are identical in 3.4.3, 3.5.8,
   4.0.4, 4.1.3 and 4.2.0. The only indirect change is that ANSI mode is on by 
default from 4.0, which affects
   the implicit `STRING` to `TIMESTAMP` cast of malformed inputs.
   
   ### Implementation notes
   
   - Spark: 
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala`
     (`MonthsBetween`) and `DateTimeUtils.monthsBetween` in
     
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala`.
   - DataFusion core has no `months_between` or equivalent; write it from 
scratch.
   - In datafusion-spark this belongs in `crates/spark/src/function/datetime/`. 
Read the session time zone from
     `config_options().execution.time_zone` as `date_trunc.rs` does, and use 
`timezone.rs` (`SparkTimeZone`) so
     Java zone IDs and short IDs resolve as in Spark. Coerce `Date32`, `Utf8*` 
and `Timestamp(_, None)` to
     `Timestamp(Microsecond, Some(session_tz))`.
   - `crates/sqllogictest/test_files/datetime/months_between.slt` exists but 
contains only commented-out
     queries; enable and extend it. Document the function with `#[user_doc]`.
   
   ---
   _This issue was generated by an LLM (Claude) from the Apache Spark source 
code and tests. Examples come from Spark's `ExpressionDescription` examples, 
test suites and `.sql.out` golden files, but please verify them against Spark 
before relying on them._


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to