This is an automated email from the ASF dual-hosted git repository.
asolimando pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite-avatica.git
The following commit(s) were added to refs/heads/main by this push:
new 247fecdab [CALCITE-5981] TIMESTAMPDIFF function returns incorrect
result
247fecdab is described below
commit 247fecdab7d2e949995210da13bb042fbdc8fb34
Author: Mihai Budiu <[email protected]>
AuthorDate: Thu Sep 7 15:02:45 2023 -0700
[CALCITE-5981] TIMESTAMPDIFF function returns incorrect result
Signed-off-by: Mihai Budiu <[email protected]>
---
.../apache/calcite/avatica/util/DateTimeUtils.java | 39 ++++++++++-----
.../calcite/avatica/util/DateTimeUtilsTest.java | 58 +++++++++++++++++++++-
2 files changed, 82 insertions(+), 15 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
b/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
index 3c988a140..bb9f233a7 100644
--- a/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
+++ b/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
@@ -1198,20 +1198,33 @@ public class DateTimeUtils {
if (date0 < date1) {
return -subtractMonths(date1, date0);
}
- // Start with an estimate.
- // Since no month has more than 31 days, the estimate is <= the true value.
- int m = (date0 - date1) / 31;
- for (;;) {
- int date2 = addMonths(date1, m);
- if (date2 >= date0) {
- return m;
- }
- int date3 = addMonths(date1, m + 1);
- if (date3 > date0) {
- return m;
- }
- ++m;
+
+ int y0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.YEAR, date0);
+ int m0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.MONTH, date0);
+ int d0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.DAY, date0);
+
+ int y1 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.YEAR, date1);
+ int m1 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.MONTH, date1);
+ int d1 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.DAY, date1);
+
+ int years = y0 - y1;
+ boolean adjust = m0 < m1 || m0 == m1 && d0 < d1;
+ if (adjust) {
+ years--;
}
+
+ int months = 12 * years;
+ if (adjust) {
+ months += 12 - (m1 - m0);
+ } else {
+ months += m0 - m1;
+ }
+
+ if (d0 < d1) {
+ months--;
+ }
+
+ return months;
}
public static int subtractMonths(long t0, long t1) {
diff --git
a/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
b/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
index 65b77e00d..2cae4a743 100644
--- a/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
+++ b/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
@@ -883,13 +883,13 @@ public class DateTimeUtilsTest {
assertThat((int) date, is(date1));
assertThat(subtractMonths(date1, date0),
- anyOf(is(months), is(months + 1)));
+ anyOf(is(months - 1), is(months), is(months + 1)));
assertThat(subtractMonths(date1 + 1, date0),
anyOf(is(months), is(months + 1)));
assertThat(subtractMonths(date1, date0 + 1),
anyOf(is(months), is(months - 1)));
assertThat(subtractMonths(d2ts(date1, 1), d2ts(date0, 0)),
- anyOf(is(months), is(months + 1)));
+ anyOf(is(months - 1), is(months), is(months + 1)));
assertThat(subtractMonths(d2ts(date1, 0), d2ts(date0, 1)),
anyOf(is(months - 1), is(months), is(months + 1)));
}
@@ -900,6 +900,60 @@ public class DateTimeUtilsTest {
return date * DateTimeUtils.MILLIS_PER_DAY + millis;
}
+ @Test public void testSubtractMonths() {
+ assertThat(subtractMonths(
+ ymdToUnixDate(2004, 9, 11),
+ ymdToUnixDate(2004, 9, 11)), is(0));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2005, 9, 11),
+ ymdToUnixDate(2004, 9, 11)), is(12));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2006, 9, 11),
+ ymdToUnixDate(2004, 9, 11)), is(24));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2007, 9, 11),
+ ymdToUnixDate(2004, 9, 11)), is(36));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2004, 9, 11),
+ ymdToUnixDate(2005, 9, 11)), is(-12));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2003, 9, 11),
+ ymdToUnixDate(2005, 9, 11)), is(-24));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2005, 2, 28),
+ ymdToUnixDate(2004, 2, 28)), is(12));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2005, 2, 28),
+ ymdToUnixDate(2004, 2, 29)), is(11));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2005, 2, 28),
+ ymdToUnixDate(2004, 2, 28)), is(12));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2005, 3, 28),
+ ymdToUnixDate(2004, 3, 29)), is(11));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2004, 2, 29),
+ ymdToUnixDate(2003, 2, 28)), is(12));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2005, 2, 28),
+ ymdToUnixDate(2003, 2, 28)), is(24));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2001, 10, 10),
+ ymdToUnixDate(1999, 9, 11)), is(24));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2001, 9, 11),
+ ymdToUnixDate(1999, 9, 11)), is(24));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2001, 5, 1),
+ ymdToUnixDate(2001, 2, 1)), is(3));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2000, 2, 29),
+ ymdToUnixDate(2000, 3, 28)), is(0));
+ assertThat(subtractMonths(
+ ymdToUnixDate(2000, 2, 29),
+ ymdToUnixDate(1991, 3, 28)), is(107));
+ }
+
@Test public void testUnixTimestamp() {
assertThat(unixTimestamp(1970, 1, 1, 0, 0, 0), is(0L));
final long day = 86400000L;
