mihaibudiu commented on code in PR #3936:
URL: https://github.com/apache/calcite/pull/3936#discussion_r1736831132
##########
core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java:
##########
@@ -221,6 +245,33 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%02d",
calendar.get(Calendar.WEEK_OF_YEAR)));
}
},
+ V("", "The number of the year (Monday as the first day of the week),"
Review Comment:
the comment seems wrong, it is about the week number
##########
core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java:
##########
@@ -326,7 +377,16 @@ public enum FormatElementEnum implements FormatElement {
sb.append(String.format(Locale.ROOT, "%03d",
calendar.get(Calendar.MILLISECOND)));
}
},
- SS("s", "The second as a decimal number (00-60)") {
+ MCS("", "The millisecond as a decimal number (000000-999999)") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ // It exceeds the precision of Calendar, we fill it with 0.
Review Comment:
Milliseconds are usually between 0 and 999.
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -5185,6 +5187,61 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
}
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6551";>[CALCITE-6551]
+ * Add DATE_FORMAT function (enabled in MySQL library)</a>. */
+ @Test void testDateFormat() {
+ final SqlOperatorFixture f = fixture()
+ .withTester(t -> TESTER).withLibrary(SqlLibrary.MYSQL);
+ f.setFor(SqlLibraryOperators.DATE_FORMAT);
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+ f.checkString("date_format(timestamp '2009-10-04 22:23:00', '%W %M %Y')",
+ "Sunday October 2009",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '2009-10-04 22:23:00', '%H:%i:%s')",
+ "22:23:00",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1900-10-04 22:23:00', '%D %y %a %d
%m %b %j')",
+ "4th 00 Thu 04 10 Oct 277",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-10-04 22:23:00', '%H %k %I %r
%T %S %w')",
+ "22 22 10 10:23:00 PM 22:23:00 00 6",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1999-01-01', '%X %V')",
+ "1998 52",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-10-04 22:23:00.000000', '%c
%D %e %f %h')",
+ "10 4th 4 000000 10",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-01-04 22:23:00', '%w %U %u %V
%v %X %x')",
+ "6 00 01 52 01 1996 1997",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-01-05 22:23:00', '%w %U %u %V
%v %X %x')",
+ "0 01 01 01 01 1997 1997",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-01-06 22:23:00', '%w %U %u %V
%v %X %x')",
+ "1 01 02 01 02 1997 1997",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-10-04 22:23:00', '%V %v %X %x
%%')",
+ "39 40 1997 1997 %",
+ "VARCHAR NOT NULL");
Review Comment:
can you add some tests with dates before the Gregorian calendar?
I found that some of these fail.
Also, can you say how you validated the outputs?
Are these tested in MySQL?
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -5185,6 +5187,61 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
}
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6551";>[CALCITE-6551]
+ * Add DATE_FORMAT function (enabled in MySQL library)</a>. */
+ @Test void testDateFormat() {
+ final SqlOperatorFixture f = fixture()
+ .withTester(t -> TESTER).withLibrary(SqlLibrary.MYSQL);
+ f.setFor(SqlLibraryOperators.DATE_FORMAT);
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+ f.checkString("date_format(timestamp '2009-10-04 22:23:00', '%W %M %Y')",
+ "Sunday October 2009",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '2009-10-04 22:23:00', '%H:%i:%s')",
+ "22:23:00",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1900-10-04 22:23:00', '%D %y %a %d
%m %b %j')",
+ "4th 00 Thu 04 10 Oct 277",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-10-04 22:23:00', '%H %k %I %r
%T %S %w')",
+ "22 22 10 10:23:00 PM 22:23:00 00 6",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1999-01-01', '%X %V')",
+ "1998 52",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-10-04 22:23:00.000000', '%c
%D %e %f %h')",
+ "10 4th 4 000000 10",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-01-04 22:23:00', '%w %U %u %V
%v %X %x')",
+ "6 00 01 52 01 1996 1997",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-01-05 22:23:00', '%w %U %u %V
%v %X %x')",
+ "0 01 01 01 01 1997 1997",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-01-06 22:23:00', '%w %U %u %V
%v %X %x')",
+ "1 01 02 01 02 1997 1997",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-10-04 22:23:00', '%V %v %X %x
%%')",
+ "39 40 1997 1997 %",
+ "VARCHAR NOT NULL");
+ f.checkString("date_format(timestamp '1997-10-04 22:23:00', '%l %p %U
%u')",
+ "10 PM 39 40",
+ "VARCHAR NOT NULL");
+ f.checkQueryFails(StringAndPos.of("date_format(^timestamp '2006-06-00'^,
'%d')"),
+ "Non-query expression encountered in illegal context");
+ f.checkNull("to_char(timestamp '2022-06-03 12:15:48.678', NULL)");
+// f.checkString("date_format('1999-13-01', '%X %V')",
+// null,
Review Comment:
what you need is to use an implicit cast to timestamp if the input is a CHAR
data type.
you should be able to support that.
##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -5185,6 +5187,61 @@ void testBitGetFunc(SqlOperatorFixture f, String
functionName) {
}
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6551";>[CALCITE-6551]
+ * Add DATE_FORMAT function (enabled in MySQL library)</a>. */
+ @Test void testDateFormat() {
+ final SqlOperatorFixture f = fixture()
+ .withTester(t -> TESTER).withLibrary(SqlLibrary.MYSQL);
+ f.setFor(SqlLibraryOperators.DATE_FORMAT);
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
Review Comment:
You should not need to set the locale in the test if these functions are
supposed to be deterministic.
Perhaps the function implementation should set the locale.
##########
core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java:
##########
@@ -399,6 +480,27 @@ public enum FormatElementEnum implements FormatElement {
sb.append(work.yyyyFormat.format(date));
}
},
+ WFY("", "The year for the week where Sunday is the first day of the week") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ calendar.setFirstDayOfWeek(Calendar.SUNDAY);
+ // Day of year of the first Sunday of the year
+ int minimalDaysInFirstWeek =
+ getFirstWeekdayOfYear(DayOfWeek.MONDAY, calendar.get(Calendar.YEAR));
+ calendar.setMinimalDaysInFirstWeek(minimalDaysInFirstWeek);
+ sb.append(String.format(Locale.ROOT, "%04d", calendar.getWeekYear()));
+ }
+ },
+ WFY0("", "The year for the week where Monday is the first day of the week") {
Review Comment:
same problem
##########
core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java:
##########
@@ -399,6 +480,27 @@ public enum FormatElementEnum implements FormatElement {
sb.append(work.yyyyFormat.format(date));
}
},
+ WFY("", "The year for the week where Sunday is the first day of the week") {
Review Comment:
I think this should say "the week of the year"
--
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]
