You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by "julianhyde (via GitHub)" <gi...@apache.org> on 2023/04/21 17:00:35 UTC
[GitHub] [calcite] julianhyde commented on a diff in pull request #3132: [CALCITE-5543] Implement BigQuery functions for parsing DATE, TIME, TIMESTAMP, DATETIME
julianhyde commented on code in PR #3132:
URL: https://github.com/apache/calcite/pull/3132#discussion_r1173991294
##########
site/_docs/reference.md:
##########
@@ -2716,6 +2716,10 @@ BigQuery's type system uses confusingly different names for types and functions:
| b m p | MD5(string) | Calculates an MD5 128-bit checksum of *string* and returns it as a hex string
| m | MONTHNAME(date) | Returns the name, in the connection's locale, of the month in *datetime*; for example, it returns '二月' for both DATE '2020-02-10' and TIMESTAMP '2020-02-10 10:10:10'
| o | NVL(value1, value2) | Returns *value1* if *value1* is not null, otherwise *value2*
+| b | PARSE_DATE(string1, string2) | Uses format specified by *string1* to convert *string2* representation of date to a DATE object
+| b | PARSE_DATETIME(string1, string2) | Uses format specified by *string1* to convert *string2* representation of datetime to a TIMESTAMP object
+| b | PARSE_TIME(string1, string2) | Uses format specified by *string1* to convert *string2* representation of time to a TIME object
+| b | PARSE_TIMESTAMP(string1, string2[, timeZone]) | Uses format specified by *string1* to convert *string2* representation of timestamp to a TIMESTAMP WITH LOCAL TIME ZONE object in *timeZone*
Review Comment:
change 'DATE object' to 'DATE value' or just 'DATE'
change 'string1' to 'format'; makes description more concise
##########
core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java:
##########
@@ -39,188 +39,207 @@
* @see FormatModels#DEFAULT
*/
public enum FormatElementEnum implements FormatElement {
- D("The weekday (Monday as the first day of the week) as a decimal number (1-7)") {
- @Override public String format(Date date) {
+ D("F", "The weekday (Monday as the first day of the week) as a decimal number (1-7)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%d", calendar.get(Calendar.DAY_OF_WEEK));
+ sb.append(String.format(Locale.ROOT, "%d", calendar.get(Calendar.DAY_OF_WEEK)));
}
},
- DAY("The full weekday name") {
- @Override public String format(Date date) {
+ DAY("EEEE", "The full weekday name") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.eeeeFormat.format(date);
+ sb.append(work.eeeeFormat.format(date));
}
},
- DD("The day of the month as a decimal number (01-31)") {
- @Override public String format(Date date) {
+ DD("dd", "The day of the month as a decimal number (01-31)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.DAY_OF_MONTH));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.DAY_OF_MONTH)));
}
},
- DDD("The day of the year as a decimal number (001-366)") {
- @Override public String format(Date date) {
+ DDD("D", "The day of the year as a decimal number (001-366)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%03d", calendar.get(Calendar.DAY_OF_YEAR));
+ sb.append(String.format(Locale.ROOT, "%03d", calendar.get(Calendar.DAY_OF_YEAR)));
}
},
- DY("The abbreviated weekday name") {
- @Override public String format(Date date) {
+ DY("EEE", "The abbreviated weekday name") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.eeeFormat.format(date);
+ sb.append(work.eeeFormat.format(date));
}
},
- FF1("Fractional seconds to 1 digit") {
- @Override public String format(Date date) {
+ FF1("S", "Fractional seconds to 1 digit") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.sFormat.format(date);
+ sb.append(work.sFormat.format(date));
}
},
- FF2("Fractional seconds to 2 digits") {
- @Override public String format(Date date) {
+ FF2("SS", "Fractional seconds to 2 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.ssFormat.format(date);
+ sb.append(work.ssFormat.format(date));
}
},
- FF3("Fractional seconds to 3 digits") {
- @Override public String format(Date date) {
+ FF3("SSS", "Fractional seconds to 3 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.sssFormat.format(date);
+ sb.append(work.sssFormat.format(date));
}
},
- FF4("Fractional seconds to 4 digits") {
- @Override public String format(Date date) {
+ FF4("SSSS", "Fractional seconds to 4 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.ssssFormat.format(date);
+ sb.append(work.ssssFormat.format(date));
}
},
- FF5("Fractional seconds to 5 digits") {
- @Override public String format(Date date) {
+ FF5("SSSSS", "Fractional seconds to 5 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.sssssFormat.format(date);
+ sb.append(work.sssssFormat.format(date));
}
},
- FF6("Fractional seconds to 6 digits") {
- @Override public String format(Date date) {
+ FF6("SSSSSS", "Fractional seconds to 6 digits") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.ssssssFormat.format(date);
+ sb.append(work.ssssssFormat.format(date));
}
},
- HH12("The hour (12-hour clock) as a decimal number (01-12)") {
- @Override public String format(Date date) {
+ HH12("h", "The hour (12-hour clock) as a decimal number (01-12)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
int hour = calendar.get(Calendar.HOUR);
- return String.format(Locale.ROOT, "%02d", hour == 0 ? 12 : hour);
+ sb.append(String.format(Locale.ROOT, "%02d", hour == 0 ? 12 : hour));
}
},
- HH24("The hour (24-hour clock) as a decimal number (00-23)") {
- @Override public String format(Date date) {
+ HH24("H", "The hour (24-hour clock) as a decimal number (00-23)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.HOUR_OF_DAY));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.HOUR_OF_DAY)));
}
},
- IW("The ISO 8601 week number of the year (Monday as the first day of the week) "
+ // 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)") {
- @Override public String format(Date date) {
+ @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);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR)));
}
},
- MI("The minute as a decimal number (00-59)") {
- @Override public String format(Date date) {
+ MI("m", "The minute as a decimal number (00-59)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MINUTE));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MINUTE)));
}
},
- MM("The month as a decimal number (01-12)") {
- @Override public String format(Date date) {
+ MM("MM", "The month as a decimal number (01-12)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MONTH) + 1);
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MONTH) + 1));
}
},
- MON("The abbreviated month name") {
- @Override public String format(Date date) {
+ MON("MMM", "The abbreviated month name") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.mmmFormat.format(date);
+ sb.append(work.mmmFormat.format(date));
}
},
- MONTH("The full month name (English)") {
- @Override public String format(Date date) {
+ MONTH("MMMM", "The full month name (English)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.mmmmFormat.format(date);
+ sb.append(work.mmmmFormat.format(date));
}
},
- Q("The quarter as a decimal number (1-4)") {
- @Override public String format(Date date) {
+ // PM can represent both AM and PM
+ PM("a", "Meridian indicator without periods") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%d", (calendar.get(Calendar.MONTH) / 3) + 1);
+ String meridian = calendar.get(Calendar.HOUR_OF_DAY) < 12 ? "AM" : "PM";
+ sb.append(meridian);
}
},
- SS("The second as a decimal number (00-60)") {
- @Override public String format(Date date) {
+ Q("", "The quarter as a decimal number (1-4)") {
+ // TODO: Allow parsing of quarters.
+ @Override public void toPattern(StringBuilder sb) {
+ throw new UnsupportedOperationException("Cannot convert 'Q' FormatElement to Java pattern");
+ }
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.SECOND));
+ sb.append(String.format(Locale.ROOT, "%d", (calendar.get(Calendar.MONTH) / 3) + 1));
}
},
- MS("The millisecond as a decimal number (000-999)") {
- @Override public String format(Date date) {
+ MS("SSS", "The millisecond as a decimal number (000-999)") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%03d", calendar.get(Calendar.MILLISECOND));
+ sb.append(String.format(Locale.ROOT, "%03d", calendar.get(Calendar.MILLISECOND)));
}
},
- TZR("The time zone name") {
- @Override public String format(Date date) {
+ SS("s", "The second as a decimal number (00-60)") {
+ @Override public void format(StringBuilder sb, Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.SECOND)));
+ }
+ },
+ TZR("z", "The time zone name") {
+ @Override public void format(StringBuilder sb, Date date) {
// TODO: how to support timezones?
- return "";
}
},
- WW("The week number of the year (Sunday as the first day of the week) as a decimal "
+ WW("w", "The week number of the year (Sunday as the first day of the week) as a decimal "
+ "number (00-53)") {
- @Override public String format(Date date) {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
calendar.setFirstDayOfWeek(Calendar.SUNDAY);
- return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.WEEK_OF_YEAR)));
}
},
- YY("Last 2 digits of year") {
- @Override public String format(Date date) {
+ YY("yy", "Last 2 digits of year") {
+ @Override public void format(StringBuilder sb, Date date) {
final Work work = Work.get();
- return work.yyFormat.format(date);
+ sb.append(work.yyFormat.format(date));
}
},
- YYYY("The year with century as a decimal number") {
- @Override public String format(Date date) {
+ YYYY("yyyy", "The year with century as a decimal number") {
+ @Override public void format(StringBuilder sb, Date date) {
final Calendar calendar = Work.get().calendar;
calendar.setTime(date);
- return String.format(Locale.ROOT, "%d", calendar.get(Calendar.YEAR));
+ sb.append(String.format(Locale.ROOT, "%d", calendar.get(Calendar.YEAR)));
}
};
private final String description;
+ final String javaFmt;
Review Comment:
Conceivably there might in future be a SQL format element that does not have a corresponding Java format element. Should we just document the fact that, for now, we're ok? Add two `requireNotNull` calls in the constructor.
##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -892,6 +892,49 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
OperandTypes.STRING_STRING,
SqlFunctionCategory.TIMEDATE);
+ /**
+ * The "PARSE_TIME(string, string)" function (BigQuery); Converts a string representation of time
Review Comment:
'C' after semicolon should be 'c'
Also, I would break the line after ';'; short lines are easier to read and maintain
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -2650,6 +2651,63 @@ public static String formatTime(DataContext ctx, String fmtString, int time) {
return internalFormatDatetime(fmtString, internalToTime(time));
}
+ private static String parseDatetimePattern(String fmtString) {
+ StringBuilder sb = new StringBuilder();
+ List<FormatElement> elements = FormatModels.BIG_QUERY.parse(fmtString);
+ elements.forEach(ele -> ele.toPattern(sb));
+ return sb.toString();
+ }
+
+ private static Date interalParseDatetime(String fmtString, String datetime) {
+ return interalParseDatetime(fmtString, datetime, DateTimeUtils.DEFAULT_ZONE);
+ }
+
Review Comment:
in several function names, 'interal' should be 'internal'
--
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: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org