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]