This is an automated email from the ASF dual-hosted git repository.
rubenql pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 8ffcc2acd6 [CALCITE-6226] Wrong ISOWEEK and no ISOYEAR on BigQuery
FORMAT_DATE
8ffcc2acd6 is described below
commit 8ffcc2acd6bc865fb8a98d5ab1c96e65e5b9f1d3
Author: rrueda <[email protected]>
AuthorDate: Fri Jan 26 09:24:15 2024 -0300
[CALCITE-6226] Wrong ISOWEEK and no ISOYEAR on BigQuery FORMAT_DATE
The ISOWEEK format function was not setting the minimalDaysInFirstWeek
to 4.
To avoid having to set the calendar week definition fields in each
format function, a new calendar instance configured with the iso8601
settings was added.
The format elements for the ISOYEAR with the century (%G) and without
it (%g) were added.
Also, the weekday with Monday as first day of week (%u) was fixed.
---
.../calcite/util/format/FormatElementEnum.java | 62 ++++++++++++++++++----
.../apache/calcite/util/format/FormatModels.java | 7 ++-
.../calcite/util/format/FormatElementEnumTest.java | 36 +++++++++++++
.../org/apache/calcite/test/SqlOperatorTest.java | 30 +++++++++--
4 files changed, 120 insertions(+), 15 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java
b/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java
index b78d88c91e..0e289cf678 100644
--- a/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java
@@ -49,7 +49,10 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%2d", calendar.get(Calendar.YEAR)
/ 100 + 1));
}
},
- D("F", "The weekday (Monday as the first day of the week) as a decimal
number (1-7)") {
+ D("", "The weekday (Sunday as the first day of the week) as a decimal number
(1-7)") {
+ @Override public void toPattern(StringBuilder sb) throws
UnsupportedOperationException {
+ throwToPatternNotImplemented();
+ }
@Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
@@ -207,17 +210,44 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%02d",
calendar.get(Calendar.HOUR_OF_DAY)));
}
},
- // TODO: Ensure ISO 8601 for parsing
- IW("w", "The ISO 8601 week number of the year (Monday as the first day of
the week) "
- + "as a decimal number (01-53)") {
+ ID("u", "The weekday (Monday as the first day of the week) as a decimal
number (1-7)") {
@Override public void format(StringBuilder sb, Date date) {
- // TODO: ensure this is isoweek
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- calendar.setFirstDayOfWeek(Calendar.MONDAY);
+ int weekDay = calendar.get(Calendar.DAY_OF_WEEK);
+ // Converting Sun(1)...Sat(7) to Mon(1)...Sun(7)
+ sb.append(weekDay == 1 ? 7 : weekDay - 1);
+ }
+ },
+ IW("", "The ISO 8601 week number of the year (Monday as the first day of the
week) "
+ + "as a decimal number (01-53). If the week containing January 1 has
four or more days "
+ + "in the new year, then it is week 1; otherwise it is week 53 of the
previous year, "
+ + "and the next week is week 1.") {
+ @Override public void toPattern(StringBuilder sb) throws
UnsupportedOperationException {
+ throwToPatternNotImplemented();
+ }
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().iso8601Calendar;
+ calendar.setTime(date);
sb.append(String.format(Locale.ROOT, "%02d",
calendar.get(Calendar.WEEK_OF_YEAR)));
}
},
+ IYY("YY", "The ISO 8601 year without century as a decimal number. Each ISO
year begins on "
+ + "the Monday before the first Thursday of the Gregorian calendar
year.") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().iso8601Calendar;
+ calendar.setTime(date);
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.getWeekYear() %
100));
+ }
+ },
+ IYYYY("YYYY", "The ISO 8601 year with century as a decimal number. Each ISO
year begins on "
+ + "the Monday before the first Thursday of the Gregorian calendar
year.") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().iso8601Calendar;
+ calendar.setTime(date);
+ sb.append(calendar.getWeekYear());
+ }
+ },
MI("m", "The minute as a decimal number (00-59)") {
@Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
@@ -278,9 +308,8 @@ public enum FormatElementEnum implements FormatElement {
}
},
Q("", "The quarter as a decimal number (1-4)") {
- // TODO: Allow parsing of quarters.
@Override public void toPattern(StringBuilder sb) throws
UnsupportedOperationException {
- throw new UnsupportedOperationException("Cannot convert 'Q'
FormatElement to Java pattern");
+ throwToPatternNotImplemented();
}
@Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
@@ -366,7 +395,6 @@ public enum FormatElementEnum implements FormatElement {
@Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- calendar.setFirstDayOfWeek(Calendar.SUNDAY);
sb.append(String.format(Locale.ROOT, "%02d",
calendar.get(Calendar.WEEK_OF_YEAR)));
}
},
@@ -422,6 +450,11 @@ public enum FormatElementEnum implements FormatElement {
sb.append(this.javaFmt);
}
+ final void throwToPatternNotImplemented() {
+ throw new UnsupportedOperationException("Cannot convert '"
+ + this.name().toUpperCase(Locale.ROOT) + "' FormatElement to Java
pattern");
+ }
+
/** Work space. Provides a value for each mutable data structure that might
* be needed by a format element. Ensures thread-safety. */
static class Work {
@@ -433,8 +466,15 @@ public enum FormatElementEnum implements FormatElement {
return THREAD_WORK.get();
}
- final Calendar calendar =
- Calendar.getInstance(DateTimeUtils.DEFAULT_ZONE, Locale.ROOT);
+ final Calendar calendar = new Calendar.Builder()
+ .setWeekDefinition(Calendar.SUNDAY, 1)
+ .setTimeZone(DateTimeUtils.DEFAULT_ZONE)
+ .setLocale(Locale.ROOT).build();
+
+ final Calendar iso8601Calendar = new Calendar.Builder()
+ .setCalendarType("iso8601")
+ .setTimeZone(DateTimeUtils.DEFAULT_ZONE)
+ .setLocale(Locale.ROOT).build();
final DateFormat mmmFormat = new SimpleDateFormat(MON.javaFmt, Locale.US);
/* Need to sse Locale.US instead of Locale.ROOT, because Locale.ROOT
diff --git
a/core/src/main/java/org/apache/calcite/util/format/FormatModels.java
b/core/src/main/java/org/apache/calcite/util/format/FormatModels.java
index a46b091ab8..d9c68950f7 100644
--- a/core/src/main/java/org/apache/calcite/util/format/FormatModels.java
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatModels.java
@@ -51,7 +51,10 @@ import static
org.apache.calcite.util.format.FormatElementEnum.FF8;
import static org.apache.calcite.util.format.FormatElementEnum.FF9;
import static org.apache.calcite.util.format.FormatElementEnum.HH12;
import static org.apache.calcite.util.format.FormatElementEnum.HH24;
+import static org.apache.calcite.util.format.FormatElementEnum.ID;
import static org.apache.calcite.util.format.FormatElementEnum.IW;
+import static org.apache.calcite.util.format.FormatElementEnum.IYY;
+import static org.apache.calcite.util.format.FormatElementEnum.IYYYY;
import static org.apache.calcite.util.format.FormatElementEnum.MI;
import static org.apache.calcite.util.format.FormatElementEnum.MM;
import static org.apache.calcite.util.format.FormatElementEnum.MON;
@@ -139,6 +142,8 @@ public class FormatModels {
map.put("%F",
compositeElement("The date in the format %Y-%m-%d.", YYYY,
literalElement("-"), MM,
literalElement("-"), DD));
+ map.put("%G", IYYYY);
+ map.put("%g", IYY);
map.put("%H", HH24);
map.put("%I", HH12);
map.put("%j", DDD);
@@ -153,7 +158,7 @@ public class FormatModels {
map.put("%T",
compositeElement("The time in the format %H:%M:%S.",
HH24, literalElement(":"), MI, literalElement(":"), SS));
- map.put("%u", D);
+ map.put("%u", ID);
map.put("%V", IW);
map.put("%W", WW);
map.put("%x",
diff --git
a/core/src/test/java/org/apache/calcite/util/format/FormatElementEnumTest.java
b/core/src/test/java/org/apache/calcite/util/format/FormatElementEnumTest.java
index c77baaeaaf..066bcafaad 100644
---
a/core/src/test/java/org/apache/calcite/util/format/FormatElementEnumTest.java
+++
b/core/src/test/java/org/apache/calcite/util/format/FormatElementEnumTest.java
@@ -98,8 +98,44 @@ class FormatElementEnumTest {
assertFormatElement(FormatElementEnum.FF9, "2014-09-30T10:00:00.123456Z",
"123000000");
}
+ @Test void testID() {
+ assertFormatElement(FormatElementEnum.ID, "2014-09-30T10:00:00Z", "2");
+ }
+
@Test void testIW() {
assertFormatElement(FormatElementEnum.IW, "2014-09-30T10:00:00Z", "40");
+ // Test case for [CALCITE-6226]
https://issues.apache.org/jira/browse/CALCITE-6226
+ // Edge case where ISO WEEK != WEEK
+ assertFormatElement(FormatElementEnum.IW, "2023-01-01T10:00:00Z", "52");
+ assertFormatElement(FormatElementEnum.IW, "2023-01-02T10:00:00Z", "01");
+ // Edge case where ISO WEEK != WEEK for Julian dates - motivated by
[CALCITE-6252]
+ assertFormatElement(FormatElementEnum.IW, "0001-01-01T10:00:00Z", "01");
+ assertFormatElement(FormatElementEnum.IW, "0005-01-01T10:00:00Z", "53");
+ assertFormatElement(FormatElementEnum.IW, "0005-01-03T10:00:00Z", "01");
+ }
+
+ @Test void testIYY() {
+ assertFormatElement(FormatElementEnum.IYY, "2014-09-30T10:00:00Z", "14");
+ // Test case for [CALCITE-6226]
https://issues.apache.org/jira/browse/CALCITE-6226
+ // Edge case where ISO WEEK YEAR != YEAR
+ assertFormatElement(FormatElementEnum.IYY, "2023-01-01T10:00:00Z", "22");
+ assertFormatElement(FormatElementEnum.IYY, "2023-01-02T10:00:00Z", "23");
+ // Edge case where ISO WEEK YEAR != YEAR for Julian dates - motivated by
[CALCITE-6252]
+ assertFormatElement(FormatElementEnum.IYY, "0001-01-01T10:00:00Z", "01");
+ assertFormatElement(FormatElementEnum.IYY, "0005-01-01T10:00:00Z", "04");
+ assertFormatElement(FormatElementEnum.IYY, "0005-01-03T10:00:00Z", "05");
+ }
+
+ @Test void testIYYYY() {
+ assertFormatElement(FormatElementEnum.IYYYY, "2014-09-30T10:00:00Z",
"2014");
+ // Test case for [CALCITE-6226]
https://issues.apache.org/jira/browse/CALCITE-6226
+ // Edge case where ISO WEEK YEAR != YEAR
+ assertFormatElement(FormatElementEnum.IYYYY, "2023-01-01T10:00:00Z",
"2022");
+ assertFormatElement(FormatElementEnum.IYYYY, "2023-01-02T10:00:00Z",
"2023");
+ // Edge case where ISO WEEK YEAR != YEAR for Julian dates - motivated by
[CALCITE-6252]
+ assertFormatElement(FormatElementEnum.IYYYY, "0001-01-01T10:00:00Z", "1");
+ assertFormatElement(FormatElementEnum.IYYYY, "0005-01-01T10:00:00Z", "4");
+ assertFormatElement(FormatElementEnum.IYYYY, "0005-01-03T10:00:00Z", "5");
}
@Test void testMM() {
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index c5ad9a4b64..1b3f720d9a 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -4859,7 +4859,7 @@ public class SqlOperatorTest {
"2",
"VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'IW')",
- "23",
+ "22",
"VARCHAR NOT NULL");
f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'YYYY')",
"2022",
@@ -5195,7 +5195,7 @@ public class SqlOperatorTest {
f.checkFails("to_date('ABCD', 'YYYY-MM-DD')",
"java.sql.SQLException: Invalid format: 'YYYY-MM-DD' for datetime
string: 'ABCD'.",
true);
- f.checkFails("to_date('2022-06-03', 'Invalid')",
+ f.checkFails("to_date('2022-06-03', 'I')",
"Illegal pattern character 'I'",
true);
f.checkNull("to_date(NULL, 'YYYY-MM-DD')");
@@ -5265,7 +5265,7 @@ public class SqlOperatorTest {
f.checkFails("to_timestamp('ABCD', 'YYYY-MM-DD HH24:MI:SS')",
"java.sql.SQLException: Invalid format: 'YYYY-MM-DD HH24:MI:SS' for
datetime string: 'ABCD'.",
true);
- f.checkFails("to_timestamp('2022-06-03 18:34:56', 'Invalid')",
+ f.checkFails("to_timestamp('2022-06-03 18:34:56', 'I')",
"Illegal pattern character 'I'",
true);
f.checkNull("to_timestamp(NULL, 'YYYY-MM-DD HH24:MI:SS')");
@@ -14441,6 +14441,30 @@ public class SqlOperatorTest {
f.checkScalar("FORMAT_DATE('%x', DATE '2008-12-25')",
"12/25/08",
"VARCHAR NOT NULL");
+ f.checkScalar("FORMAT_DATE('%g-%V', DATE '2001-01-01')",
+ "01-01",
+ "VARCHAR NOT NULL");
+ // Test case for [CALCITE-6226]
https://issues.apache.org/jira/browse/CALCITE-6226
+ f.checkScalar("FORMAT_DATE('%G-%V', DATE '2023-01-01')",
+ "2022-52",
+ "VARCHAR NOT NULL");
+ f.checkScalar("FORMAT_DATE('%g-%V', DATE '2023-01-01')",
+ "22-52",
+ "VARCHAR NOT NULL");
+ // For Julian dates - motivated by [CALCITE-6252]
+ f.checkScalar("FORMAT_DATE('%G-%V', DATE '0005-01-01')",
+ "4-53",
+ "VARCHAR NOT NULL");
+ f.checkScalar("FORMAT_DATE('%g-%V', DATE '0005-01-01')",
+ "04-53",
+ "VARCHAR NOT NULL");
+ // End Test case for
+ f.checkScalar("FORMAT_DATE('%u', DATE '2024-01-01')",
+ "1",
+ "VARCHAR NOT NULL");
+ f.checkScalar("FORMAT_DATE('%u', DATE '2024-01-07')",
+ "7",
+ "VARCHAR NOT NULL");
f.checkNull("FORMAT_DATE('%x', CAST(NULL AS DATE))");
f.checkNull("FORMAT_DATE('%b-%d-%Y', CAST(NULL AS DATE))");
f.checkNull("FORMAT_DATE('%b %Y', CAST(NULL AS DATE))");
