You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2023/04/28 06:21:14 UTC
[calcite] 01/03: [CALCITE-5543] Add functions PARSE_DATE, PARSE_DATETIME, PARSE_TIME, PARSE_TIMESTAMP (enabled in BigQuery library)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit bbc2cec5e8a70a09806fc499f750e9d9fa081a9e
Author: TJ Banghart <66...@users.noreply.github.com>
AuthorDate: Fri Mar 24 14:49:45 2023 -0700
[CALCITE-5543] Add functions PARSE_DATE, PARSE_DATETIME, PARSE_TIME, PARSE_TIMESTAMP (enabled in BigQuery library)
Because BigQuery has different names for types, in Calcite's
type system these functions parse values of type DATE,
TIMESTAMP, TIME, and TIMESTAMP WITH LOCAL TIME ZONE,
respectively.
Close apache/calcite#3132
---
babel/src/test/resources/sql/big-query.iq | 163 ++++++++++-------
.../calcite/adapter/enumerable/RexImpTable.java | 16 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 90 ++++++++--
.../calcite/sql/fun/SqlLibraryOperators.java | 43 +++++
.../org/apache/calcite/sql/type/ReturnTypes.java | 7 +
.../apache/calcite/util/format/FormatElement.java | 18 +-
.../calcite/util/format/FormatElementEnum.java | 197 ++++++++++++---------
.../apache/calcite/util/format/FormatModels.java | 28 ++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 32 ++++
.../calcite/util/format/FormatElementEnumTest.java | 5 +-
site/_docs/reference.md | 4 +
.../org/apache/calcite/test/SqlOperatorTest.java | 78 ++++++++
12 files changed, 503 insertions(+), 178 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq
index 0df376e76a..941c30657b 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -2957,29 +2957,37 @@ SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00" /* TODO should
# in format_string must match the location of each element in
# date_string.
-!if (false) {
# This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008");
++------------+
+| EXPR$0 |
++------------+
+| 2008-12-25 |
++------------+
+(1 row)
+
!ok
-!}
-!if (false) {
# This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008");
+Invalid format: '%Y %A %b %e' for datetime string: 'Thursday Dec 25 2008'
!error
-!}
-!if (false) {
# This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008");
+Invalid format: '%A %b %e' for datetime string: 'Thursday Dec 25 2008'
!error
-!}
-!if (false) {
# This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30");
++------------+
+| EXPR$0 |
++------------+
+| 2000-12-30 |
++------------+
+(1 row)
+
!ok
-!}
# When using PARSE_DATE, keep the following in mind:
#
@@ -3000,27 +3008,27 @@ SELECT PARSE_DATE("%F", "2000-12-30");
# This example converts a MM/DD/YY formatted string to a DATE object:
-!if (false) {
SELECT PARSE_DATE("%x", "12/25/08") AS parsed;
+------------+
| parsed |
+------------+
| 2008-12-25 |
+------------+
+(1 row)
+
!ok
-!}
# This example converts a YYYYMMDD formatted string to a DATE object:
-!if (false) {
SELECT PARSE_DATE("%Y%m%d", "20081225") AS parsed;
+------------+
| parsed |
+------------+
| 2008-12-25 |
+------------+
+(1 row)
+
!ok
-!}
#####################################################################
# PARSE_DATETIME
@@ -3036,28 +3044,38 @@ SELECT PARSE_DATE("%Y%m%d", "20081225") AS parsed;
# datetime_string.
# This works because elements on both sides match.
-!if (false) {
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008");
++---------------------+
+| EXPR$0 |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
+
# This doesn't work because the year element is in different locations.
-!if (false) {
SELECT PARSE_DATETIME("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008");
+Invalid format: '%a %b %e %Y %I:%M:%S' for datetime string: 'Thu Dec 25 07:30:00 2008'
!error
-!}
# This doesn't work because one of the year elements is missing.
-!if (false) {
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008");
+Invalid format: '%a %b %e %I:%M:%S' for datetime string: 'Thu Dec 25 07:30:00 2008'
!error
-!}
+
# This works because %c can find all matching elements in datetime_string.
-!if (false) {
SELECT PARSE_DATETIME("%c", "Thu Dec 25 07:30:00 2008");
++---------------------+
+| EXPR$0 |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
# The format string fully supports most format elements, except for %P.
#
@@ -3084,39 +3102,39 @@ SELECT PARSE_DATETIME("%c", "Thu Dec 25 07:30:00 2008");
# The following examples parse a STRING literal as a DATETIME.
-!if (false) {
-SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;
+SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS dt;
+---------------------+
-| datetime |
+| dt |
+---------------------+
-| 1998-10-18T13:45:55 |
+| 1998-10-18 13:45:55 |
+---------------------+
+(1 row)
+
!ok
-!}
-!if (false) {
-SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 pm') AS datetime
+SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 pm') AS dt;
+---------------------+
-| datetime |
+| dt |
+---------------------+
-| 2018-08-30T14:23:38 |
+| 2018-08-30 14:23:38 |
+---------------------+
+(1 row)
+
!ok
-!}
# The following example parses a STRING literal containing a date in a
# natural language format as a DATETIME.
-!if (false) {
SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
- AS datetime;
+ AS dt;
+---------------------+
-| datetime |
+| dt |
+---------------------+
-| 2018-12-19T00:00:00 |
+| 2018-12-19 00:00:00 |
+---------------------+
+(1 row)
+
!ok
-!}
#####################################################################
# PARSE_TIME
@@ -3132,29 +3150,41 @@ SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
# time_string.
#
-!if (false) {
# This works because elements on both sides match.
SELECT PARSE_TIME("%I:%M:%S", "07:30:00");
++----------+
+| EXPR$0 |
++----------+
+| 07:30:00 |
++----------+
+(1 row)
+
!ok
-!}
+# TODO: validate for format element ordering
!if (false) {
# This doesn't work because the seconds element is in different locations.
SELECT PARSE_TIME("%S:%I:%M", "07:30:00");
!error
!}
-!if (false) {
# This doesn't work because one of the seconds elements is missing.
SELECT PARSE_TIME("%I:%M", "07:30:00");
+Invalid format: '%I:%M' for datetime string: '07:30:00'
!error
-!}
-!if (false) {
+
# This works because %T can find all matching elements in time_string.
SELECT PARSE_TIME("%T", "07:30:00");
++----------+
+| EXPR$0 |
++----------+
+| 07:30:00 |
++----------+
+(1 row)
+
!ok
-!}
+
# The format string fully supports most format elements except for %P.
#
@@ -3174,25 +3204,26 @@ SELECT PARSE_TIME("%T", "07:30:00");
#
# Returns TIME
-!if (false) {
SELECT PARSE_TIME("%H", "15") as parsed_time;
+-------------+
| parsed_time |
+-------------+
| 15:00:00 |
+-------------+
+(1 row)
+
!ok
-!}
-!if (false) {
SELECT PARSE_TIME('%I:%M:%S %p', '2:23:38 pm') AS parsed_time;
+-------------+
| parsed_time |
+-------------+
| 14:23:38 |
+-------------+
+(1 row)
+
!ok
-!}
+
#####################################################################
# PARSE_TIMESTAMP
@@ -3209,28 +3240,36 @@ SELECT PARSE_TIME('%I:%M:%S %p', '2:23:38 pm') AS parsed_time;
# timestamp_string.
# This works because elements on both sides match.
-!if (false) {
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008");
++---------------------+
+| EXPR$0 |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
# This doesn't work because the year element is in different locations.
-!if (false) {
SELECT PARSE_TIMESTAMP("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008");
+Invalid format: '%a %b %e %Y %I:%M:%S' for datetime string: 'Thu Dec 25 07:30:00 2008'
!error
-!}
# This doesn't work because one of the year elements is missing.
-!if (false) {
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008");
+Invalid format: '%a %b %e %I:%M:%S' for datetime string: 'Thu Dec 25 07:30:00 2008'
!error
-!}
# This works because %c can find all matching elements in timestamp_string.
-!if (false) {
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008");
++---------------------+
+| EXPR$0 |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
# The format string fully supports most format elements, except for %P.
#
@@ -3256,15 +3295,19 @@ SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008");
# Display of results may differ, depending upon the environment and
# time zone where this query was executed.
-!if (false) {
+#
+# After [CALCITE-5446] is fixed, this and other TIMESTAMP WITH LOCAL TIME
+# values will end in UTC, like this: 2008-12-25 15:30:00 UTC.
+#
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;
-+-------------------------+
-| parsed |
-+-------------------------+
-| 2008-12-25 07:30:00 UTC |
-+-------------------------+
++---------------------+
+| parsed |
++---------------------+
+| 2008-12-25 07:30:00 |
++---------------------+
+(1 row)
+
!ok
-!}
#####################################################################
# STRING and BYTES functions ########################################
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 92b874ee11..9978ea2647 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -161,6 +161,10 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.MAX_BY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MD5;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MIN_BY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.MONTHNAME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_DATE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_DATETIME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_TIME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.PARSE_TIMESTAMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.POW;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REPEAT;
@@ -589,16 +593,22 @@ public class RexImpTable {
// Datetime constructors
defineMethod(DATE, "date", NullPolicy.STRICT);
defineMethod(DATETIME, "datetime", NullPolicy.STRICT);
- defineMethod(TIMESTAMP, "timestamp", NullPolicy.STRICT);
defineMethod(TIME, "time", NullPolicy.STRICT);
+ defineMethod(TIMESTAMP, "timestamp", NullPolicy.STRICT);
+
+ // Datetime parsing methods
+ defineMethod(PARSE_DATE, "parseDate", NullPolicy.STRICT);
+ defineMethod(PARSE_DATETIME, "parseDatetime", NullPolicy.STRICT);
+ defineMethod(PARSE_TIME, "parseTime", NullPolicy.STRICT);
+ defineMethod(PARSE_TIMESTAMP, "parseTimestamp", NullPolicy.STRICT);
// Datetime formatting methods
defineMethod(TO_CHAR, "toChar", NullPolicy.STRICT);
final FormatDatetimeImplementor datetimeFormatImpl = new FormatDatetimeImplementor();
- map.put(FORMAT_TIMESTAMP, datetimeFormatImpl);
map.put(FORMAT_DATE, datetimeFormatImpl);
- map.put(FORMAT_TIME, datetimeFormatImpl);
map.put(FORMAT_DATETIME, datetimeFormatImpl);
+ map.put(FORMAT_TIME, datetimeFormatImpl);
+ map.put(FORMAT_TIMESTAMP, datetimeFormatImpl);
// Boolean operators
map.put(IS_NULL, new IsNullImplementor());
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 8f726822fd..42dccc92e0 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -66,7 +66,10 @@ import java.nio.charset.Charset;
import java.nio.charset.CharsetDecoder;
import java.sql.SQLException;
import java.sql.Timestamp;
+import java.text.DateFormat;
import java.text.DecimalFormat;
+import java.text.ParsePosition;
+import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
@@ -82,9 +85,11 @@ import java.time.temporal.ChronoField;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Base64;
+import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
+import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
@@ -96,7 +101,6 @@ import java.util.TimeZone;
import java.util.concurrent.atomic.AtomicLong;
import java.util.function.BinaryOperator;
import java.util.regex.Pattern;
-import java.util.stream.Collectors;
import static org.apache.calcite.linq4j.Nullness.castNonNull;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -2662,11 +2666,10 @@ public class SqlFunctions {
}
private static String internalFormatDatetime(String fmtString, java.util.Date date) {
- List<FormatElement> elements =
- FormatModels.BIG_QUERY.parse(fmtString);
- return elements.stream()
- .map(ele -> ele.format(date))
- .collect(Collectors.joining());
+ StringBuilder sb = new StringBuilder();
+ List<FormatElement> elements = FormatModels.BIG_QUERY.parse(fmtString);
+ elements.forEach(ele -> ele.format(sb, date));
+ return sb.toString();
}
public static String formatTimestamp(DataContext ctx, String fmtString, long timestamp) {
@@ -2675,11 +2678,9 @@ public class SqlFunctions {
public static String toChar(long timestamp, String pattern) {
List<FormatElement> elements = FormatModels.POSTGRESQL.parse(pattern);
-
- return elements.stream()
- .map(ele -> ele.format(internalToTimestamp(timestamp)))
- .collect(Collectors.joining())
- .trim();
+ StringBuilder sb = new StringBuilder();
+ elements.forEach(ele -> ele.format(sb, internalToTimestamp(timestamp)));
+ return sb.toString().trim();
}
public static String formatDate(DataContext ctx, String fmtString, int date) {
@@ -2690,6 +2691,73 @@ public class SqlFunctions {
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 long internalParseDatetime(String fmtString, String datetime) {
+ return internalParseDatetime(fmtString, datetime,
+ DateTimeUtils.DEFAULT_ZONE);
+ }
+
+ private static long internalParseDatetime(String fmt, String datetime,
+ TimeZone tz) {
+ final String javaFmt = parseDatetimePattern(fmt);
+ // TODO: make Locale configurable. ENGLISH set for weekday parsing (e.g.
+ // Thursday, Friday).
+ final DateFormat parser = new SimpleDateFormat(javaFmt, Locale.ENGLISH);
+ final ParsePosition pos = new ParsePosition(0);
+ parser.setLenient(false);
+ parser.setCalendar(Calendar.getInstance(tz, Locale.ROOT));
+ Date parsed = parser.parse(datetime, pos);
+ // Throw if either the parse was unsuccessful, or the format string did not
+ // contain enough elements to parse the datetime string completely.
+ if (pos.getErrorIndex() >= 0 || pos.getIndex() != datetime.length()) {
+ SQLException e =
+ new SQLException(
+ String.format(Locale.ROOT,
+ "Invalid format: '%s' for datetime string: '%s'.", fmt,
+ datetime));
+ throw Util.toUnchecked(e);
+ }
+ // Suppress the Errorprone warning "[JavaUtilDate] Date has a bad API that
+ // leads to bugs; prefer java.time.Instant or LocalDate" because we know
+ // what we're doing.
+ @SuppressWarnings("JavaUtilDate")
+ final long millisSinceEpoch = parsed.getTime();
+ return millisSinceEpoch;
+ }
+
+ public static int parseDate(String fmtString, String date) {
+ final long millisSinceEpoch = internalParseDatetime(fmtString, date);
+ return toInt(new java.sql.Date(millisSinceEpoch));
+ }
+
+ public static long parseDatetime(String fmtString, String datetime) {
+ final long millisSinceEpoch = internalParseDatetime(fmtString, datetime);
+ return toLong(new java.sql.Timestamp(millisSinceEpoch));
+ }
+
+ public static int parseTime(String fmtString, String time) {
+ final long millisSinceEpoch = internalParseDatetime(fmtString, time);
+ return toInt(new java.sql.Time(millisSinceEpoch));
+ }
+
+ public static long parseTimestamp(String fmtString, String timestamp) {
+ return parseTimestamp(fmtString, timestamp, "UTC");
+ }
+
+ public static long parseTimestamp(String fmtString, String timestamp,
+ String timeZone) {
+ TimeZone tz = TimeZone.getTimeZone(timeZone);
+ final long millisSinceEpoch =
+ internalParseDatetime(fmtString, timestamp, tz);
+ return toLong(new java.sql.Timestamp(millisSinceEpoch), tz);
+ }
+
/**
* Converts a SQL TIMESTAMP value from the internal representation type
* (number of milliseconds since January 1st, 1970) to the Java Type
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index ed3ebd5ff8..75a27f2c53 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -892,6 +892,49 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_STRING,
SqlFunctionCategory.TIMEDATE);
+ /**
+ * The "PARSE_TIME(string, string)" function (BigQuery);
+ * converts a string representation of time to a TIME value.
+ */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction PARSE_TIME =
+ SqlBasicFunction.create("PARSE_TIME", ReturnTypes.TIME_NULLABLE,
+ OperandTypes.STRING_STRING, SqlFunctionCategory.TIMEDATE);
+
+ /**
+ * The "PARSE_DATE(string, string)" function (BigQuery); Converts a string representation of date
+ * to a DATE object.
+ */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction PARSE_DATE =
+ SqlBasicFunction.create("PARSE_DATE",
+ ReturnTypes.DATE_NULLABLE, OperandTypes.STRING_STRING, SqlFunctionCategory.TIMEDATE);
+
+ /**
+ * The "PARSE_TIMESTAMP(string, string [, timezone])" function (BigQuery); Formats a timestamp
+ * object according to the specified string.
+ *
+ * <p>In BigQuery, the "TIMESTAMP" datatype maps to Calcite's
+ * TIMESTAMP_WITH_LOCAL_TIME_ZONE
+ */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction PARSE_TIMESTAMP =
+ SqlBasicFunction.create("PARSE_TIMESTAMP",
+ ReturnTypes.TIMESTAMP_LTZ_NULLABLE, OperandTypes.STRING_STRING_OPTIONAL_STRING,
+ SqlFunctionCategory.TIMEDATE);
+
+ /**
+ * The "PARSE_DATETIME(string, string [, timezone])" function (BigQuery); Formats a timestamp
+ * object according to the specified string.
+ *
+ * <p>Note that the {@code TIMESTAMP} type of Calcite and Standard SQL
+ * is called {@code DATETIME} in BigQuery.
+ */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction PARSE_DATETIME =
+ SqlBasicFunction.create("PARSE_DATETIME", ReturnTypes.TIMESTAMP_NULLABLE,
+ OperandTypes.STRING_STRING, SqlFunctionCategory.TIMEDATE);
+
/** The "FORMAT_TIME(string, time)" function (BigQuery);
* Formats a time object according to the specified string. */
@LibraryOperator(libraries = {BIG_QUERY})
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index ab4aac1784..0b7385438f 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
@@ -335,6 +335,13 @@ public abstract class ReturnTypes {
public static final SqlReturnTypeInference TIMESTAMP_LTZ =
explicit(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE);
+ /**
+ * Type-inference strategy whereby the result type of a call is nullable
+ * TIMESTAMP WITH LOCAL TIME ZONE.
+ */
+ public static final SqlReturnTypeInference TIMESTAMP_LTZ_NULLABLE =
+ TIMESTAMP_LTZ.andThen(SqlTypeTransforms.TO_NULLABLE);
+
/**
* Type-inference strategy whereby the result type of a call is Double.
*/
diff --git a/core/src/main/java/org/apache/calcite/util/format/FormatElement.java b/core/src/main/java/org/apache/calcite/util/format/FormatElement.java
index 6670e23423..ef5c230510 100644
--- a/core/src/main/java/org/apache/calcite/util/format/FormatElement.java
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatElement.java
@@ -28,16 +28,18 @@ public interface FormatElement {
/**
* Formats a date to its appropriate string representation for the element.
+ */
+ void format(StringBuilder sb, java.util.Date date);
+
+ /**
+ * Adds the appropriate {@link java.text.SimpleDateFormat} pattern for the element to
+ * provided StringBuilder.
*
- * <p>This API is subject to change. It might be more efficient if the
- * signature was one of the following:
- *
- * <pre>
- * void format(StringBuilder, java.util.Date)
- * void format(StringBuilder, long)
- * </pre>
+ * <p>Note that certain FormatElements may not have a Java equivalent.
+ * In such cases, calling this method will throw an {@link UnsupportedOperationException}.
+ * See {@link FormatElementEnum#Q} as an example.</p>
*/
- String format(java.util.Date date);
+ void toPattern(StringBuilder sb) throws UnsupportedOperationException;
/**
* Returns the description of an element.
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 2b1ea1c007..8c6ac03607 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
@@ -28,6 +28,8 @@ import java.util.Locale;
import static org.apache.calcite.linq4j.Nullness.castNonNull;
+import static java.util.Objects.requireNonNull;
+
/**
* Implementation of {@link FormatElement} containing the standard format
* elements. These are based on Oracle's format model documentation.
@@ -39,188 +41,207 @@ import static org.apache.calcite.linq4j.Nullness.castNonNull;
* @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));
+ }
+ },
+ // 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);
+ String meridian = calendar.get(Calendar.HOUR_OF_DAY) < 12 ? "AM" : "PM";
+ sb.append(meridian);
}
},
- Q("The quarter as a decimal number (1-4)") {
- @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) throws UnsupportedOperationException {
+ 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, "%d", (calendar.get(Calendar.MONTH) / 3) + 1);
+ sb.append(String.format(Locale.ROOT, "%d", (calendar.get(Calendar.MONTH) / 3) + 1));
}
},
- SS("The second as a decimal number (00-60)") {
- @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, "%02d", calendar.get(Calendar.SECOND));
+ sb.append(String.format(Locale.ROOT, "%03d", calendar.get(Calendar.MILLISECOND)));
}
},
- MS("The millisecond as a decimal number (000-999)") {
- @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);
- return String.format(Locale.ROOT, "%03d", calendar.get(Calendar.MILLISECOND));
+ sb.append(String.format(Locale.ROOT, "%02d", calendar.get(Calendar.SECOND)));
}
},
- TZR("The time zone name") {
- @Override public String format(Date date) {
+ 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;
// TODO: be sure to deal with TZ
- FormatElementEnum(String description) {
- this.description = description;
+ FormatElementEnum(String javaFmt, String description) {
+ this.javaFmt = requireNonNull(javaFmt, "javaFmt");
+ this.description = requireNonNull(description, "description");
}
@Override public String getDescription() {
return description;
}
+ @Override public void toPattern(StringBuilder sb) {
+ sb.append(this.javaFmt);
+ }
+
/** Work space. Provides a value for each mutable data structure that might
* be needed by a format element. Ensures thread-safety. */
static class Work {
@@ -236,16 +257,16 @@ public enum FormatElementEnum implements FormatElement {
Calendar.getInstance(DateTimeUtils.DEFAULT_ZONE, Locale.ROOT);
/** Uses Locale.US instead of Locale.ROOT to fix formatting in Java 11 */
- final DateFormat eeeeFormat = new SimpleDateFormat("EEEE", Locale.US);
- final DateFormat eeeFormat = new SimpleDateFormat("EEE", Locale.ROOT);
- final DateFormat mmmFormat = new SimpleDateFormat("MMM", Locale.ROOT);
- final DateFormat mmmmFormat = new SimpleDateFormat("MMMM", Locale.ROOT);
- final DateFormat sFormat = new SimpleDateFormat("S", Locale.ROOT);
- final DateFormat ssFormat = new SimpleDateFormat("SS", Locale.ROOT);
- final DateFormat sssFormat = new SimpleDateFormat("SSS", Locale.ROOT);
- final DateFormat ssssFormat = new SimpleDateFormat("SSSS", Locale.ROOT);
- final DateFormat sssssFormat = new SimpleDateFormat("SSSSS", Locale.ROOT);
- final DateFormat ssssssFormat = new SimpleDateFormat("SSSSSS", Locale.ROOT);
- final DateFormat yyFormat = new SimpleDateFormat("yy", Locale.ROOT);
+ final DateFormat eeeeFormat = new SimpleDateFormat(DAY.javaFmt, Locale.US);
+ final DateFormat eeeFormat = new SimpleDateFormat(DY.javaFmt, Locale.ROOT);
+ final DateFormat mmmFormat = new SimpleDateFormat(MON.javaFmt, Locale.ROOT);
+ final DateFormat mmmmFormat = new SimpleDateFormat(MONTH.javaFmt, Locale.ROOT);
+ final DateFormat sFormat = new SimpleDateFormat(FF1.javaFmt, Locale.ROOT);
+ final DateFormat ssFormat = new SimpleDateFormat(FF2.javaFmt, Locale.ROOT);
+ final DateFormat sssFormat = new SimpleDateFormat(FF3.javaFmt, Locale.ROOT);
+ final DateFormat ssssFormat = new SimpleDateFormat(FF4.javaFmt, Locale.ROOT);
+ final DateFormat sssssFormat = new SimpleDateFormat(FF5.javaFmt, Locale.ROOT);
+ final DateFormat ssssssFormat = new SimpleDateFormat(FF6.javaFmt, Locale.ROOT);
+ final DateFormat yyFormat = new SimpleDateFormat(YY.javaFmt, 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 a8e5b4b9f2..545e315b58 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
@@ -47,6 +47,7 @@ import static org.apache.calcite.util.format.FormatElementEnum.MM;
import static org.apache.calcite.util.format.FormatElementEnum.MON;
import static org.apache.calcite.util.format.FormatElementEnum.MONTH;
import static org.apache.calcite.util.format.FormatElementEnum.MS;
+import static org.apache.calcite.util.format.FormatElementEnum.PM;
import static org.apache.calcite.util.format.FormatElementEnum.Q;
import static org.apache.calcite.util.format.FormatElementEnum.SS;
import static org.apache.calcite.util.format.FormatElementEnum.TZR;
@@ -111,15 +112,24 @@ public class FormatModels {
map.put("%E4S", FF4);
map.put("%E5S", FF5);
map.put("%E*S", FF6);
+ map.put("%e", DD);
+ map.put("%F",
+ compositeElement("The date in the format %Y-%m-%d.", YYYY, literalElement("-"), MM,
+ literalElement("-"), DD));
map.put("%H", HH24);
+ map.put("%I", HH12);
map.put("%j", DDD);
map.put("%M", MI);
map.put("%m", MM);
+ map.put("%p", PM);
map.put("%Q", Q);
map.put("%R",
compositeElement("The time in the format %H:%M",
HH24, literalElement(":"), MI));
map.put("%S", SS);
+ map.put("%T",
+ compositeElement("The time in the format %H:%M:%S.",
+ HH24, literalElement(":"), MI, literalElement(":"), SS));
map.put("%u", D);
map.put("%V", IW);
map.put("%W", WW);
@@ -267,8 +277,12 @@ public class FormatModels {
this.literal = requireNonNull(literal, "literal");
}
- @Override public String format(Date date) {
- return literal;
+ @Override public void format(StringBuilder sb, Date date) {
+ sb.append(literal);
+ }
+
+ @Override public void toPattern(StringBuilder sb) {
+ sb.append(literal);
}
@Override public String getDescription() {
@@ -293,10 +307,12 @@ public class FormatModels {
this.description = requireNonNull(description, "description");
}
- @Override public String format(Date date) {
- StringBuilder buf = new StringBuilder();
- flatten(ele -> buf.append(ele.format(date)));
- return buf.toString();
+ @Override public void format(StringBuilder sb, Date date) {
+ flatten(ele -> ele.format(sb, date));
+ }
+
+ @Override public void toPattern(StringBuilder sb) {
+ flatten(ele -> ele.toPattern(sb));
}
/**
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 8cae0d8deb..4d146ec5d2 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -1894,6 +1894,38 @@ class RelToSqlConverterTest {
sql(query).withBigQuery().ok(expected);
}
+ @Test void testBigQueryParseDatetimeFunctions() {
+ String parseTime = "select parse_time('%I:%M:%S', '07:30:00')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String expectedTimestampTrunc =
+ "SELECT PARSE_TIME('%I:%M:%S', '07:30:00')\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(parseTime).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedTimestampTrunc);
+
+ String parseDate = "select parse_date('%A %b %e %Y', 'Thursday Dec 25 2008')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String expectedParseDate =
+ "SELECT PARSE_DATE('%A %b %e %Y', 'Thursday Dec 25 2008')\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(parseDate).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedParseDate);
+
+ String parseTimestamp =
+ "select parse_timestamp('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String expectedParseTimestamp =
+ "SELECT PARSE_TIMESTAMP('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(parseTimestamp).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedParseTimestamp);
+
+ String parseDatetime =
+ "select parse_datetime('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String expectedParseDatetime =
+ "SELECT PARSE_DATETIME('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(parseDatetime).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedParseDatetime);
+ }
+
@Test void testBigQueryTimeTruncFunctions() {
String timestampTrunc = "select timestamp_trunc(timestamp '2012-02-03 15:30:00', month)\n"
+ "from \"foodmart\".\"product\"\n";
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 59a268f3a8..6a69bb3aad 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
@@ -28,8 +28,9 @@ import static org.junit.jupiter.api.Assertions.assertEquals;
*/
class FormatElementEnumTest {
@Test void testDay() {
- assertEquals(
- FormatElementEnum.DAY.format(Date.from(Instant.parse("2014-09-30T10:00:00Z"))),
+ StringBuilder ts = new StringBuilder();
+ FormatElementEnum.DAY.format(ts, Date.from(Instant.parse("2014-09-30T10:00:00Z")));
+ assertEquals(ts.toString(),
"Tuesday");
}
}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index d90178f42c..2dbb03e1f1 100644
--- a/site/_docs/reference.md
+++ b/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(format, string) | Uses format specified by *format* to convert *string* representation of date to a DATE value
+| b | PARSE_DATETIME(format, string) | Uses format specified by *format* to convert *string* representation of datetime to a TIMESTAMP value
+| b | PARSE_TIME(format, string) | Uses format specified by *format* to convert *string* representation of time to a TIME value
+| b | PARSE_TIMESTAMP(format, string[, timeZone]) | Uses format specified by *format* to convert *string* representation of timestamp to a TIMESTAMP WITH LOCAL TIME ZONE value in *timeZone*
| b | POW(numeric1, numeric2) | Returns *numeric1* raised to the power *numeric2*
| m o | REGEXP_REPLACE(string, regexp, rep, [, pos [, occurrence [, matchType]]]) | Replaces all substrings of *string* that match *regexp* with *rep* at the starting *pos* in expr (if omitted, the default is 1), *occurrence* means which occurrence of a match to search for (if omitted, the default is 1), *matchType* specifies how to perform matching
| b m p | REPEAT(string, integer) | Returns a string consisting of *string* repeated of *integer* times; returns an empty string if *integer* is less than 1
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 43b322a19f..96285ca7c7 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -9127,6 +9127,84 @@ public class SqlOperatorTest {
"VARCHAR(2000) NOT NULL");
}
+ @Test void testParseDate() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.PARSE_DATE);
+ f.checkScalar("PARSE_DATE('%A %b %e %Y', 'Thursday Dec 25 2008')",
+ "2008-12-25",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%x', '12/25/08')",
+ "2008-12-25",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%F', '2000-12-30')",
+ "2000-12-30",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%x', '12/25/08')",
+ "2008-12-25",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%Y%m%d', '20081225')",
+ "2008-12-25",
+ "DATE NOT NULL");
+ f.checkScalar("PARSE_DATE('%F', '2022-06-01')",
+ "2022-06-01",
+ "DATE NOT NULL");
+ }
+
+ @Test void testParseDatetime() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.PARSE_DATETIME);
+ f.checkScalar("PARSE_DATETIME('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("PARSE_DATETIME('%c', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55')",
+ "1998-10-18 13:45:55",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 pm')",
+ "2018-08-30 14:23:38",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkScalar("PARSE_DATETIME('%A, %B %e, %Y', 'Wednesday, December 19, 2018')",
+ "2018-12-19 00:00:00",
+ "TIMESTAMP(0) NOT NULL");
+ }
+
+ @Test void testParseTime() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.PARSE_TIME);
+ f.checkScalar("PARSE_TIME('%I:%M:%S', '07:30:00')",
+ "07:30:00",
+ "TIME(0) NOT NULL");
+ f.checkScalar("PARSE_TIME('%T', '07:30:00')",
+ "07:30:00",
+ "TIME(0) NOT NULL");
+ f.checkScalar("PARSE_TIME('%H', '15')",
+ "15:00:00",
+ "TIME(0) NOT NULL");
+ f.checkScalar("PARSE_TIME('%I:%M:%S %p', '2:23:38 pm')",
+ "14:23:38",
+ "TIME(0) NOT NULL");
+ }
+
+ @Test void testParseTimestamp() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.PARSE_TIMESTAMP);
+ f.checkScalar("PARSE_TIMESTAMP('%a %b %e %I:%M:%S %Y', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL");
+ f.checkScalar("PARSE_TIMESTAMP('%c', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL");
+ f.checkScalar("PARSE_TIMESTAMP('%c', 'Thu Dec 25 07:30:00 2008')",
+ "2008-12-25 07:30:00",
+ "TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL");
+ }
+
@Test void testDenseRankFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.DENSE_RANK, VM_FENNEL, VM_JAVA);