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/02/26 02:51:47 UTC
[calcite] 02/04: [CALCITE-5357] Add FORMAT_TIME, FORMAT_DATE, FORMAT_DATETIME, FORMAT_TIMESTAMP functions (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 49a0d5e9c1ef65618425189b22f2a2b5ed51f6d8
Author: TJ Banghart <tj...@gmail.com>
AuthorDate: Wed Jan 18 10:10:51 2023 -0800
[CALCITE-5357] Add FORMAT_TIME, FORMAT_DATE, FORMAT_DATETIME, FORMAT_TIMESTAMP functions (enabled in BigQuery library)
Add interface FormatModel, class FormatModels, interface
FormatElement to support other formatting and parsing
functions in future.
Co-authored-by: TJ Banghart <tj...@gmail.com>
Co-authored-by: Julian Hyde <jh...@apache.org>
Close apache/calcite#3034
---
babel/src/test/resources/sql/big-query.iq | 86 +++----
.../calcite/adapter/enumerable/RexImpTable.java | 42 ++++
.../org/apache/calcite/runtime/SqlFunctions.java | 23 ++
.../java/org/apache/calcite/sql/SqlDialect.java | 14 ++
.../calcite/sql/dialect/BigQuerySqlDialect.java | 11 +
.../calcite/sql/fun/SqlLibraryOperators.java | 48 ++++
.../org/apache/calcite/sql/type/OperandTypes.java | 6 +
.../org/apache/calcite/util/BuiltInMethod.java | 6 +
.../apache/calcite/util/format/FormatElement.java | 58 +++++
.../calcite/util/format/FormatElementEnum.java | 234 +++++++++++++++++
.../apache/calcite/util/format/FormatModel.java | 37 +++
.../apache/calcite/util/format/FormatModels.java | 278 +++++++++++++++++++++
.../apache/calcite/util/format/package-info.java | 21 ++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 35 +++
.../org/apache/calcite/util/FormatModelTest.java | 67 +++++
site/_docs/reference.md | 4 +
.../org/apache/calcite/test/SqlOperatorTest.java | 89 +++++++
17 files changed, 1016 insertions(+), 43 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq b/babel/src/test/resources/sql/big-query.iq
index c31141d2db..528ac8797b 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -2445,35 +2445,35 @@ SELECT * FROM items;
#
# Returns STRING
-!if (false) {
SELECT FORMAT_DATE("%x", DATE "2008-12-25") AS US_format;
-+------------+
-| US_format |
-+------------+
-| 12/25/08 |
-+------------+
++-----------+
+| US_format |
++-----------+
+| 12/25/08 |
++-----------+
+(1 row)
+
!ok
-!}
-!if (false) {
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted |
+-------------+
| Dec-25-2008 |
+-------------+
+(1 row)
+
!ok
-!}
-!if (false) {
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;
-+-------------+
-| formatted |
-+-------------+
-| Dec 2008 |
-+-------------+
++-----------+
+| formatted |
++-----------+
+| Dec 2008 |
++-----------+
+(1 row)
+
!ok
-!}
#####################################################################
# FORMAT_DATETIME
@@ -2485,7 +2485,6 @@ SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;
#
# Returns STRING
-!if (false) {
SELECT
FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
AS formatted;
@@ -2494,10 +2493,10 @@ SELECT
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
+(1 row)
+
!ok
-!}
-!if (false) {
SELECT
FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00")
AS formatted;
@@ -2506,20 +2505,21 @@ SELECT
+-------------+
| Dec-25-2008 |
+-------------+
+(1 row)
+
!ok
-!}
-!if (false) {
SELECT
FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00")
AS formatted;
-+-------------+
-| formatted |
-+-------------+
-| Dec 2008 |
-+-------------+
++-----------+
+| formatted |
++-----------+
+| Dec 2008 |
++-----------+
+(1 row)
+
!ok
-!}
#####################################################################
# FORMAT_TIME
@@ -2532,15 +2532,15 @@ SELECT
#
# Returns STRING
-!if (false) {
SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time;
+----------------+
| formatted_time |
+----------------+
| 15:30 |
+----------------+
+(1 row)
+
!ok
-!}
#####################################################################
# FORMAT_TIMESTAMP
@@ -2553,36 +2553,36 @@ SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time;
# See Supported Format Elements For TIMESTAMP for a list of format
# elements that this function supports.
-!if (false) {
-SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS formatted;
+SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00" /* TODO should be "2008-12-25 15:30:00+00" */, "UTC") AS formatted;
+--------------------------+
| formatted |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
+(1 row)
+
!ok
-!}
-!if (false) {
-SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00+00") AS formatted;
+SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00" /* TODO should be "2008-12-25 15:30:00+00" */) AS formatted;
+-------------+
| formatted |
+-------------+
| Dec-25-2008 |
+-------------+
+(1 row)
+
!ok
-!}
-!if (false) {
-SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00+00")
+SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00" /* TODO should be "2008-12-25 15:30:00+00" */)
AS formatted;
-+-------------+
-| formatted |
-+-------------+
-| Dec 2008 |
-+-------------+
++-----------+
+| formatted |
++-----------+
+| Dec 2008 |
++-----------+
+(1 row)
+
!ok
-!}
#####################################################################
# PARSE_DATE
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 3e3eeb621f..013f748b61 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
@@ -133,6 +133,10 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.ENDS_WITH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXISTS_NODE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXTRACT_VALUE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXTRACT_XML;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_DATE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_DATETIME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_TIME;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.FORMAT_TIMESTAMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ILIKE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_DEPTH;
@@ -553,11 +557,20 @@ public class RexImpTable {
defineMethod(DATE_FROM_UNIX_DATE, "dateFromUnixDate", NullPolicy.STRICT);
defineMethod(UNIX_DATE, "unixDate", NullPolicy.STRICT);
+ // Datetime constructors
defineMethod(DATE, "date", NullPolicy.STRICT);
defineMethod(DATETIME, "datetime", NullPolicy.STRICT);
defineMethod(TIMESTAMP, "timestamp", NullPolicy.STRICT);
defineMethod(TIME, "time", NullPolicy.STRICT);
+ // Datetime formatting methods
+ 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);
+
+ // Boolean operators
map.put(IS_NULL, new IsNullImplementor());
map.put(IS_NOT_NULL, new IsNotNullImplementor());
map.put(IS_TRUE, new IsTrueImplementor());
@@ -2349,6 +2362,35 @@ public class RexImpTable {
}
}
+ /**
+ * Implementor for the {@code FORMAT_TIMESTAMP, FORMAT_DATE, FORMAT_TIME} and
+ * {@code FORMAT_DATETIME} functions.
+ */
+ private static class FormatDatetimeImplementor extends MethodNameImplementor {
+
+ FormatDatetimeImplementor() {
+ super("formatDatetime", NullPolicy.STRICT, false);
+ }
+
+ @Override Expression implementSafe(final RexToLixTranslator translator,
+ final RexCall call, final List<Expression> argValueList) {
+ final Expression operand0 = argValueList.get(0);
+ final Expression operand1 = argValueList.get(1);
+ Method method;
+ switch (call.operands.get(1).getType().getSqlTypeName()) {
+ case TIME:
+ method = BuiltInMethod.FORMAT_TIME.method;
+ break;
+ case DATE:
+ method = BuiltInMethod.FORMAT_DATE.method;
+ break;
+ default:
+ method = BuiltInMethod.FORMAT_TIMESTAMP.method;
+ }
+ return Expressions.call(method, translator.getRoot(), operand0, operand1);
+ }
+ }
+
/** Implementor for a function that generates calls to a given method. */
private static class MethodImplementor extends AbstractRexCallImplementor {
protected final Method method;
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 a44c6390f0..49592abdad 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -41,6 +41,8 @@ import org.apache.calcite.util.TimeWithTimeZoneString;
import org.apache.calcite.util.TimestampWithTimeZoneString;
import org.apache.calcite.util.Unsafe;
import org.apache.calcite.util.Util;
+import org.apache.calcite.util.format.FormatElement;
+import org.apache.calcite.util.format.FormatModels;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.codec.language.Soundex;
@@ -88,6 +90,7 @@ 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;
@@ -2571,6 +2574,26 @@ public class SqlFunctions {
.toString();
}
+ 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());
+ }
+
+ public static String formatTimestamp(DataContext ctx, String fmtString, long timestamp) {
+ return internalFormatDatetime(fmtString, internalToTimestamp(timestamp));
+ }
+
+ public static String formatDate(DataContext ctx, String fmtString, int date) {
+ return internalFormatDatetime(fmtString, internalToDate(date));
+ }
+
+ public static String formatTime(DataContext ctx, String fmtString, int time) {
+ return internalFormatDatetime(fmtString, internalToTime(time));
+ }
+
/**
* 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/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
index 4654f8fea1..ffb12c3629 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -38,6 +38,8 @@ import org.apache.calcite.sql.type.AbstractSqlType;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
+import org.apache.calcite.util.format.FormatModel;
+import org.apache.calcite.util.format.FormatModels;
import com.google.common.base.Preconditions;
import com.google.common.base.Suppliers;
@@ -1002,6 +1004,18 @@ public class SqlDialect {
}
}
+ /**
+ * Returns a description of the format string used by functions in this
+ * dialect.
+ *
+ * <p>Dialects may need to override this element mapping if they differ from
+ * <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html">
+ * Oracle's format elements</a>. By default, this returns {@link FormatModels#DEFAULT}.
+ */
+ public FormatModel getFormatModel() {
+ return FormatModels.DEFAULT;
+ }
+
/**
* Returns whether the dialect supports nested aggregations, for instance
* {@code SELECT SUM(SUM(1)) }.
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
index df38d9c3a9..4fd8b70a32 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
@@ -43,6 +43,8 @@ import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.BasicSqlType;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.util.format.FormatModel;
+import org.apache.calcite.util.format.FormatModels;
import com.google.common.collect.ImmutableList;
@@ -271,6 +273,15 @@ public class BigQuerySqlDialect extends SqlDialect {
}
}
+ /**
+ * {@inheritDoc}
+ *
+ * @see FormatModels#BIG_QUERY
+ */
+ @Override public FormatModel getFormatModel() {
+ return FormatModels.BIG_QUERY;
+ }
+
/** {@inheritDoc}
*
* <p>BigQuery data type reference:
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 6b91ffa1c0..936194d4e1 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
@@ -789,6 +789,54 @@ public abstract class SqlLibraryOperators {
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})
+ public static final SqlFunction FORMAT_TIME =
+ SqlBasicFunction.create("FORMAT_TIME", ReturnTypes.VARCHAR_2000_NULLABLE,
+ OperandTypes.CHARACTER_TIME, SqlFunctionCategory.STRING);
+
+ /** The "FORMAT_DATE(string, date)" function (BigQuery);
+ * Formats a date object according to the specified string. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction FORMAT_DATE =
+ SqlBasicFunction.create("FORMAT_DATE", ReturnTypes.VARCHAR_2000_NULLABLE,
+ OperandTypes.CHARACTER_DATE, SqlFunctionCategory.STRING);
+
+ /** The "FORMAT_TIMESTAMP(string, timestamp)" 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 FORMAT_TIMESTAMP =
+ SqlBasicFunction.create("FORMAT_TIMESTAMP",
+ ReturnTypes.VARCHAR_2000_NULLABLE,
+ OperandTypes.sequence("FORMAT_TIMESTAMP(<CHARACTER>, "
+ + "<TIMESTAMP WITH LOCAL TIME ZONE>)",
+ OperandTypes.CHARACTER, OperandTypes.TIMESTAMP_LTZ)
+ .or(
+ OperandTypes.sequence("FORMAT_TIMESTAMP(<CHARACTER>, "
+ + "<TIMESTAMP WITH LOCAL TIME ZONE>, <CHARACTER>)",
+ OperandTypes.CHARACTER, OperandTypes.TIMESTAMP_LTZ,
+ OperandTypes.CHARACTER)),
+ SqlFunctionCategory.STRING);
+
+ /** The "FORMAT_DATETIME(string, timestamp)" function (BigQuery);
+ * Formats a timestamp object according to the specified string. */
+ @LibraryOperator(libraries = {BIG_QUERY})
+ public static final SqlFunction FORMAT_DATETIME =
+ SqlBasicFunction.create("FORMAT_DATETIME",
+ ReturnTypes.VARCHAR_2000_NULLABLE,
+ OperandTypes.sequence("FORMAT_DATETIME(<CHARACTER>, <TIMESTAMP>)",
+ OperandTypes.CHARACTER, OperandTypes.TIMESTAMP_NTZ)
+ .or(
+ OperandTypes.sequence("FORMAT_DATETIME(<CHARACTER>, "
+ + "<TIMESTAMP>, <CHARACTER>)",
+ OperandTypes.CHARACTER, OperandTypes.TIMESTAMP_NTZ,
+ OperandTypes.CHARACTER)),
+ SqlFunctionCategory.STRING);
+
/** The "TIMESTAMP_ADD(timestamp, interval)" function (BigQuery), the
* two-argument variant of the built-in
* {@link SqlStdOperatorTable#TIMESTAMP_ADD TIMESTAMPADD} function, which has
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index cd927392aa..1f23a81707 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -468,6 +468,12 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker CHARACTER_CHARACTER_DATETIME =
family(SqlTypeFamily.CHARACTER, SqlTypeFamily.CHARACTER, SqlTypeFamily.DATETIME);
+ public static final SqlSingleOperandTypeChecker CHARACTER_DATE =
+ family(SqlTypeFamily.CHARACTER, SqlTypeFamily.DATE);
+
+ public static final SqlSingleOperandTypeChecker CHARACTER_TIME =
+ family(SqlTypeFamily.CHARACTER, SqlTypeFamily.TIME);
+
public static final SqlSingleOperandTypeChecker PERIOD =
new PeriodOperandTypeChecker();
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 52ff2bf1ec..2b3eaaf085 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -500,6 +500,12 @@ public enum BuiltInMethod {
"timestampWithLocalTimeZoneToTimestamp", long.class, TimeZone.class),
TIMESTAMP_WITH_LOCAL_TIME_ZONE_TO_STRING(SqlFunctions.class,
"timestampWithLocalTimeZoneToString", long.class, TimeZone.class),
+ FORMAT_TIMESTAMP(SqlFunctions.class, "formatTimestamp", DataContext.class,
+ String.class, long.class),
+ FORMAT_DATE(SqlFunctions.class, "formatDate", DataContext.class, String.class,
+ int.class),
+ FORMAT_TIME(SqlFunctions.class, "formatTime", DataContext.class, String.class,
+ int.class),
UNIX_DATE_TO_STRING(DateTimeUtils.class, "unixDateToString", int.class),
UNIX_TIME_TO_STRING(DateTimeUtils.class, "unixTimeToString", int.class),
UNIX_TIMESTAMP_TO_STRING(DateTimeUtils.class, "unixTimestampToString",
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
new file mode 100644
index 0000000000..6670e23423
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatElement.java
@@ -0,0 +1,58 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import org.apache.calcite.linq4j.function.Experimental;
+
+import java.util.function.Consumer;
+
+/**
+ * A format element in a format string. Knows how to parse and unparse itself.
+ */
+@Experimental
+public interface FormatElement {
+
+ /**
+ * Formats a date to its appropriate string representation for the element.
+ *
+ * <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>
+ */
+ String format(java.util.Date date);
+
+ /**
+ * Returns the description of an element.
+ *
+ * <p>For example, {@code %H} in MySQL represents the hour in 24-hour format
+ * (e.g., 00..23). This method returns the string "The hour (24-hour clock) as
+ * a decimal number (00-23)", which is the description of
+ * {@link FormatElementEnum#HH24}.
+ */
+ String getDescription();
+
+ /**
+ * Applies a consumer to a format element.
+ */
+ default void flatten(Consumer<FormatElement> consumer) {
+ consumer.accept(this);
+ }
+}
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
new file mode 100644
index 0000000000..7aed1fc486
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatElementEnum.java
@@ -0,0 +1,234 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import org.apache.calcite.avatica.util.DateTimeUtils;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.text.DateFormat;
+import java.text.SimpleDateFormat;
+import java.util.Calendar;
+import java.util.Date;
+import java.util.Locale;
+
+import static org.apache.calcite.linq4j.Nullness.castNonNull;
+
+/**
+ * Implementation of {@link FormatElement} containing the standard format
+ * elements. These are based on Oracle's format model documentation.
+ *
+ * <p>See
+ * <a href="https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlqr/Format-Models.html">
+ * Oracle format model reference.</a>
+ *
+ * @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) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return String.format(Locale.ROOT, "%d", calendar.get(Calendar.DAY_OF_WEEK));
+ }
+ },
+ DAY("The full weekday name") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.eeeeFormat.format(date);
+ }
+ },
+ DD("The day of the month as a decimal number (01-31)") {
+ @Override public String format(Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return 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) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return String.format(Locale.ROOT, "%03d", calendar.get(Calendar.DAY_OF_YEAR));
+ }
+ },
+ DY("The abbreviated weekday name") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.eeeFormat.format(date);
+ }
+ },
+ FF1("Fractional seconds to 1 digit") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.sFormat.format(date);
+ }
+ },
+ FF2("Fractional seconds to 2 digits") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.ssFormat.format(date);
+ }
+ },
+ FF3("Fractional seconds to 3 digits") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.sssFormat.format(date);
+ }
+ },
+ FF4("Fractional seconds to 4 digits") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.ssssFormat.format(date);
+ }
+ },
+ FF5("Fractional seconds to 5 digits") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.sssssFormat.format(date);
+ }
+ },
+ FF6("Fractional seconds to 6 digits") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.ssssssFormat.format(date);
+ }
+ },
+ HH24("The hour (24-hour clock) as a decimal number (00-23)") {
+ @Override public String format(Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return 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) "
+ + "as a decimal number (01-53)") {
+ @Override public String format(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));
+ }
+ },
+ MI("The minute as a decimal number (00-59)") {
+ @Override public String format(Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MINUTE));
+ }
+ },
+ MM("The month as a decimal number (01-12)") {
+ @Override public String format(Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.MONTH) + 1);
+ }
+ },
+ MON("The abbreviated month name") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.mmmFormat.format(date);
+ }
+ },
+ MONTH("The full month name (English)") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.mmmmFormat.format(date);
+ }
+ },
+ Q("The quarter as a decimal number (1-4)") {
+ @Override public String format(Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return 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) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.SECOND));
+ }
+ },
+ TZR("The time zone name") {
+ @Override public String format(Date date) {
+ // TODO: how to support timezones?
+ throw new UnsupportedOperationException();
+ }
+ },
+ WW("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) {
+ 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));
+ }
+ },
+ YY("Last 2 digits of year") {
+ @Override public String format(Date date) {
+ final Work work = Work.get();
+ return work.yyFormat.format(date);
+ }
+ },
+ YYYY("The year with century as a decimal number") {
+ @Override public String format(Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return String.format(Locale.ROOT, "%d", calendar.get(Calendar.YEAR));
+ }
+ };
+
+ private final String description;
+
+ // TODO: be sure to deal with TZ
+
+ FormatElementEnum(String description) {
+ this.description = description;
+ }
+
+ @Override public String getDescription() {
+ return description;
+ }
+
+ /** Work space. Provides a value for each mutable data structure that might
+ * be needed by a format element. Ensures thread-safety. */
+ static class Work {
+ private static final ThreadLocal<@Nullable Work> THREAD_WORK =
+ ThreadLocal.withInitial(Work::new);
+
+ /** Returns an instance of Work for this thread. */
+ static Work get() {
+ return castNonNull(THREAD_WORK.get());
+ }
+
+ final Calendar calendar =
+ Calendar.getInstance(DateTimeUtils.DEFAULT_ZONE, Locale.ROOT);
+ final DateFormat eeeeFormat = new SimpleDateFormat("EEEE", Locale.ROOT);
+ 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);
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/util/format/FormatModel.java b/core/src/main/java/org/apache/calcite/util/format/FormatModel.java
new file mode 100644
index 0000000000..39bac2f053
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatModel.java
@@ -0,0 +1,37 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import java.util.List;
+import java.util.Map;
+
+/**
+ * Describes the format strings used by a formatting function such as
+ * {@code FORMAT_TIMESTAMP} or {@code CAST(string AS DATE FORMAT formatString)}.
+ *
+ * <p>Can parse a format string to a list of format elements.
+ *
+ * @see FormatModels
+ */
+public interface FormatModel {
+ /** Returns the map used to create the {@link FormatModel} instance. */
+ Map<String, FormatElement> getElementMap();
+
+ /** Parses a format string using element identifiers supplied by
+ * {@code format}. */
+ List<FormatElement> parse(String format);
+}
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
new file mode 100644
index 0000000000..be7a144b3a
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/util/format/FormatModels.java
@@ -0,0 +1,278 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util.format;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
+
+import java.util.Date;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.concurrent.ConcurrentHashMap;
+import java.util.function.Consumer;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import static org.apache.calcite.util.format.FormatElementEnum.D;
+import static org.apache.calcite.util.format.FormatElementEnum.DAY;
+import static org.apache.calcite.util.format.FormatElementEnum.DD;
+import static org.apache.calcite.util.format.FormatElementEnum.DDD;
+import static org.apache.calcite.util.format.FormatElementEnum.DY;
+import static org.apache.calcite.util.format.FormatElementEnum.FF1;
+import static org.apache.calcite.util.format.FormatElementEnum.FF2;
+import static org.apache.calcite.util.format.FormatElementEnum.FF3;
+import static org.apache.calcite.util.format.FormatElementEnum.FF4;
+import static org.apache.calcite.util.format.FormatElementEnum.FF5;
+import static org.apache.calcite.util.format.FormatElementEnum.FF6;
+import static org.apache.calcite.util.format.FormatElementEnum.HH24;
+import static org.apache.calcite.util.format.FormatElementEnum.IW;
+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;
+import static org.apache.calcite.util.format.FormatElementEnum.MONTH;
+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;
+import static org.apache.calcite.util.format.FormatElementEnum.WW;
+import static org.apache.calcite.util.format.FormatElementEnum.YY;
+import static org.apache.calcite.util.format.FormatElementEnum.YYYY;
+
+import static java.util.Objects.requireNonNull;
+
+/**
+ * Utilities for {@link FormatModel}.
+ */
+public class FormatModels {
+ private FormatModels() {
+ }
+
+ /** The format model consisting of built-in format elements.
+ *
+ * <p>Due to the design of {@link FormatElementEnum}, it is similar to
+ * Oracle's format model.
+ */
+ public static final FormatModel DEFAULT;
+
+ /** Format model for BigQuery.
+ *
+ * <p>BigQuery format element reference:
+ * <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements">
+ * BigQuery Standard SQL Format Elements</a>.
+ */
+ public static final FormatModel BIG_QUERY;
+
+ static {
+ final Map<String, FormatElement> map = new HashMap<>();
+ for (FormatElementEnum fe : FormatElementEnum.values()) {
+ map.put(fe.toString(), fe);
+ }
+ DEFAULT = create(map);
+
+ map.clear();
+ map.put("%A", DAY);
+ map.put("%a", DY);
+ map.put("%B", MONTH);
+ map.put("%b", MON);
+ map.put("%c",
+ compositeElement("The date and time representation (English);",
+ DY, literalElement(" "), MON, literalElement(" "),
+ DD, literalElement(" "), HH24, literalElement(":"),
+ MI, literalElement(":"), SS, literalElement(" "),
+ YYYY));
+ map.put("%d", DD);
+ map.put("%E1S", FF1);
+ map.put("%E2S", FF2);
+ map.put("%E3S", FF3);
+ map.put("%E4S", FF4);
+ map.put("%E5S", FF5);
+ map.put("%E*S", FF6);
+ map.put("%H", HH24);
+ map.put("%j", DDD);
+ map.put("%M", MI);
+ map.put("%m", MM);
+ map.put("%Q", Q);
+ map.put("%R",
+ compositeElement("The time in the format %H:%M",
+ HH24, literalElement(":"), MI));
+ map.put("%S", SS);
+ map.put("%u", D);
+ map.put("%V", IW);
+ map.put("%W", WW);
+ map.put("%x",
+ compositeElement("The date representation in MM/DD/YY format",
+ MM, literalElement("/"), DD, literalElement("/"), YY));
+ map.put("%Y", YYYY);
+ map.put("%y", YY);
+ map.put("%Z", TZR);
+ BIG_QUERY = create(map);
+ }
+
+ /**
+ * Generates a {@link Pattern} using the keys of a {@link FormatModel} element
+ * map. This pattern is used in {@link FormatModel#parse(String)} to help
+ * locate known format elements in a string.
+ */
+ private static Pattern regexFromMap(Map<String, FormatElement> elementMap) {
+ StringBuilder regex = new StringBuilder();
+ for (String key : elementMap.keySet()) {
+ regex.append("(").append(Pattern.quote(key)).append(")|");
+ }
+ // remove the last '|'
+ regex.setLength(regex.length() - 1);
+ return Pattern.compile(regex.toString());
+ }
+
+ /**
+ * Creates a {@link FormatModel} that uses the provided map to identify
+ * {@link FormatElement}s while parsing a format string.
+ */
+ public static FormatModel create(Map<String, FormatElement> elementMap) {
+ final Pattern pattern = regexFromMap(elementMap);
+ return new FormatModelImpl(pattern, elementMap);
+ }
+
+ /**
+ * Creates a literal format element.
+ */
+ public static FormatElement literalElement(String literal) {
+ return new FormatModelElementLiteral(literal);
+ }
+
+ /**
+ * Creates a composite format element from the provided list of elements
+ * and description.
+ */
+ public static FormatElement compositeElement(String description,
+ FormatElement... fmtElements) {
+ return new CompositeFormatElement(ImmutableList.copyOf(fmtElements),
+ description);
+ }
+
+
+ /** Implementation of {@link FormatModel} based on a list of format
+ * elements. */
+ private static class FormatModelImpl implements FormatModel {
+ final Pattern pattern;
+ final Map<String, FormatElement> elementMap;
+
+ /** Cache of parsed format strings.
+ *
+ * <p>NOTE: The current implementation could grow without bounds.
+ * A per-thread cache would be better, or a cache tied to a statement
+ * execution (e.g. in DataContext). Also limit to a say 100 entries. */
+ final Map<String, List<FormatElement>> memoizedElements =
+ new ConcurrentHashMap<>();
+
+ FormatModelImpl(Pattern pattern, Map<String, FormatElement> elementMap) {
+ this.pattern = requireNonNull(pattern, "pattern");
+ this.elementMap = ImmutableMap.copyOf(elementMap);
+ }
+
+ @Override public Map<String, FormatElement> getElementMap() {
+ return elementMap;
+ }
+
+ private List<FormatElement> internalParse(String format) {
+ final ImmutableList.Builder<FormatElement> elements =
+ ImmutableList.builder();
+ final Matcher matcher = pattern.matcher(format);
+ int i = 0;
+ String literal;
+ while (matcher.find()) {
+ // Add any leading literal text before next element match
+ literal = format.substring(i, matcher.start());
+ if (!literal.isEmpty()) {
+ elements.add(literalElement(literal));
+ }
+ // add the element match - use literal as default to be safe.
+ String key = matcher.group();
+ elements.add(getElementMap().getOrDefault(key, literalElement(key)));
+ i = matcher.end();
+ }
+ // add any remaining literal text after last element match
+ literal = format.substring(i);
+ if (!literal.isEmpty()) {
+ elements.add(literalElement(literal));
+ }
+ return elements.build();
+ }
+
+ @Override public List<FormatElement> parse(String format) {
+ return memoizedElements.computeIfAbsent(format, this::internalParse);
+ }
+ }
+
+ /**
+ * A format element that is literal text.
+ */
+ private static class FormatModelElementLiteral implements FormatElement {
+ private final String literal;
+
+ FormatModelElementLiteral(String literal) {
+ this.literal = requireNonNull(literal, "literal");
+ }
+
+ @Override public String format(Date date) {
+ return literal;
+ }
+
+ @Override public String getDescription() {
+ return "Represents literal text in a format string";
+ }
+
+ @Override public String toString() {
+ return this.literal;
+ }
+ }
+
+ /**
+ * A format element comprised of one or more {@link FormatElement} entries.
+ */
+ private static class CompositeFormatElement implements FormatElement {
+ private final String description;
+ private final List<FormatElement> formatElements;
+
+ CompositeFormatElement(List<FormatElement> formatElements,
+ String description) {
+ this.formatElements = ImmutableList.copyOf(formatElements);
+ 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();
+ }
+
+ /**
+ * Applies a consumer to each format element that make up the composite
+ * element.
+ *
+ * <p>For example, {@code %R} in Google SQL represents the hour in 24-hour
+ * format (e.g., 00..23) followed by the minute as a decimal number.
+ * {@code flatten(i -> println(i.toString())); } would print "HH24:MI".
+ */
+ @Override public void flatten(Consumer<FormatElement> consumer) {
+ formatElements.forEach(consumer);
+ }
+
+ @Override public String getDescription() {
+ return this.description;
+ }
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/util/format/package-info.java b/core/src/main/java/org/apache/calcite/util/format/package-info.java
new file mode 100644
index 0000000000..88ed6b551c
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/util/format/package-info.java
@@ -0,0 +1,21 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+/**
+ * Utility classes for handling format strings.
+ */
+package org.apache.calcite.util.format;
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 827592089a..83f1fd13d1 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
@@ -1891,6 +1891,41 @@ class RelToSqlConverterTest {
sql(timeTrunc).withLibrary(SqlLibrary.BIG_QUERY).ok(expectedTimeTrunc);
}
+ @Test void testBigQueryDatetimeFormatFunctions() {
+ final String formatTime = "select format_time('%H', time '12:45:30')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String formatDate = "select format_date('%b-%d-%Y', date '2012-02-03')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String formatTimestamp = "select format_timestamp('%b-%d-%Y',\n"
+ + " timestamp with local time zone '2012-02-03 12:30:40')\n"
+ + "from \"foodmart\".\"product\"\n";
+ final String formatDatetime = "select format_datetime('%R',\n"
+ + " timestamp '2012-02-03 12:34:34')\n"
+ + "from \"foodmart\".\"product\"\n";
+
+ final String expectedBqFormatTime =
+ "SELECT FORMAT_TIME('%H', TIME '12:45:30')\n"
+ + "FROM foodmart.product";
+ final String expectedBqFormatDate =
+ "SELECT FORMAT_DATE('%b-%d-%Y', DATE '2012-02-03')\n"
+ + "FROM foodmart.product";
+ final String expectedBqFormatTimestamp =
+ "SELECT FORMAT_TIMESTAMP('%b-%d-%Y', TIMESTAMP_WITH_LOCAL_TIME_ZONE '2012-02-03 12:30:40')\n"
+ + "FROM foodmart.product";
+ final String expectedBqFormatDatetime =
+ "SELECT FORMAT_DATETIME('%R', TIMESTAMP '2012-02-03 12:34:34')\n"
+ + "FROM foodmart.product";
+ final Sql sql = fixture().withBigQuery().withLibrary(SqlLibrary.BIG_QUERY);
+ sql.withSql(formatTime)
+ .ok(expectedBqFormatTime);
+ sql.withSql(formatDate)
+ .ok(expectedBqFormatDate);
+ sql.withSql(formatTimestamp)
+ .ok(expectedBqFormatTimestamp);
+ sql.withSql(formatDatetime)
+ .ok(expectedBqFormatDatetime);
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3220">[CALCITE-3220]
* HiveSqlDialect should transform the SQL-standard TRIM function to TRIM,
diff --git a/core/src/test/java/org/apache/calcite/util/FormatModelTest.java b/core/src/test/java/org/apache/calcite/util/FormatModelTest.java
new file mode 100644
index 0000000000..b313b652e0
--- /dev/null
+++ b/core/src/test/java/org/apache/calcite/util/FormatModelTest.java
@@ -0,0 +1,67 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.util;
+
+import org.apache.calcite.util.format.FormatElement;
+import org.apache.calcite.util.format.FormatModel;
+import org.apache.calcite.util.format.FormatModels;
+
+import org.hamcrest.Matcher;
+import org.junit.jupiter.api.Test;
+
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.List;
+
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.is;
+
+/**
+ * Unit test for {@link FormatModel}.
+ */
+public class FormatModelTest {
+
+ private void assertThatFormatElementParse(String formatString,
+ Matcher<List<String>> matcher) {
+ List<FormatElement> elements = FormatModels.BIG_QUERY.parse(formatString);
+ List<String> stringResults = new ArrayList<>();
+ for (FormatElement element : elements) {
+ element.flatten(i -> stringResults.add(i.toString()));
+ }
+ assertThat(stringResults, matcher);
+ }
+
+ @Test void testSingleElement() {
+ assertThatFormatElementParse("%j", is(Collections.singletonList("DDD")));
+ }
+
+ @Test void testMultipleElements() {
+ assertThatFormatElementParse("%b-%d-%Y",
+ is(Arrays.asList("MON", "-", "DD", "-", "YYYY")));
+ }
+
+ @Test void testArbitraryText() {
+ assertThatFormatElementParse("%jtext%b",
+ is(Arrays.asList("DDD", "text", "MON")));
+ }
+
+ @Test void testAliasText() {
+ assertThatFormatElementParse("%R",
+ is(Arrays.asList("HH24", ":", "MI")));
+ }
+}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index cf30819fc2..9fef9858f1 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2659,6 +2659,10 @@ BigQuery's type system uses confusingly different names for types and functions:
| o | EXTRACT(xml, xpath, [, namespaces ]) | Returns the xml fragment of the element or elements matched by the XPath expression. The optional namespace value that specifies a default mapping or namespace mapping for prefixes, which is used when evaluating the XPath expression
| o | EXISTSNODE(xml, xpath, [, namespaces ]) | Determines whether traversal of a XML document using a specified xpath results in any nodes. Returns 0 if no nodes remain after applying the XPath traversal on the document fragment of the element or elements matched by the XPath expression. Returns 1 if any nodes remain. The optional namespace value that specifies a default mapping or namespace mapping for prefixes, which is used when evaluating the XPath expression.
| m | EXTRACTVALUE(xml, xpathExpr)) | Returns the text of the first text node which is a child of the element or elements matched by the XPath expression.
+| b | FORMAT_DATE(string, date) | Formats *date* according to the specified format *string*
+| b | FORMAT_DATETIME(string, timestamp) | Formats *timestamp* according to the specified format *string*
+| b | FORMAT_TIME(string, time) | Formats *time* according to the specified format *string*
+| b | FORMAT_TIMESTAMP(string timestamp) | Formats *timestamp* according to the specified format *string*
| b o | GREATEST(expr [, expr ]*) | Returns the greatest of the expressions
| b h s | IF(condition, value1, value2) | Returns *value1* if *condition* is TRUE, *value2* otherwise
| b | IFNULL(value1, value2) | Equivalent to `NVL(value1, value2)`
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 45e2a65916..a51531284e 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -8568,6 +8568,95 @@ public class SqlOperatorTest {
"2014-12-29", "DATE NOT NULL");
}
+ @Test void testFormatTime() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.FORMAT_TIME);
+ f.checkFails("^FORMAT_TIME('%x', timestamp '2008-12-25 15:30:00')^",
+ "Cannot apply 'FORMAT_TIME' to arguments of type "
+ + "'FORMAT_TIME\\(<CHAR\\(2\\)>, <TIMESTAMP\\(0\\)>\\)'\\. "
+ + "Supported form\\(s\\): "
+ + "'FORMAT_TIME\\(<CHARACTER>, <TIME>\\)'",
+ false);
+ f.checkScalar("FORMAT_TIME('%H', TIME '12:34:33')",
+ "12",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_TIME('%R', TIME '12:34:33')",
+ "12:34",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_TIME('The time is %M-%S', TIME '12:34:33')",
+ "The time is 34-33",
+ "VARCHAR(2000) NOT NULL");
+ }
+
+ @Test void testFormatDate() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.FORMAT_DATE);
+ f.checkFails("^FORMAT_DATE('%x', 123)^",
+ "Cannot apply 'FORMAT_DATE' to arguments of type "
+ + "'FORMAT_DATE\\(<CHAR\\(2\\)>, <INTEGER>\\)'\\. "
+ + "Supported form\\(s\\): "
+ + "'FORMAT_DATE\\(<CHARACTER>, <DATE>\\)'",
+ false);
+ // Can implicitly cast TIMESTAMP to DATE
+ f.checkScalar("FORMAT_DATE('%x', timestamp '2008-12-25 15:30:00')",
+ "12/25/08",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_DATE('%b-%d-%Y', DATE '2008-12-25')",
+ "Dec-25-2008",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_DATE('%b %Y', DATE '2008-12-25')",
+ "Dec 2008",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_DATE('%x', DATE '2008-12-25')",
+ "12/25/08",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_DATE('The date is: %x', DATE '2008-12-25')",
+ "The date is: 12/25/08",
+ "VARCHAR(2000) NOT NULL");
+ }
+
+ @Test void testFormatTimestamp() {
+ final SqlOperatorFixture f = fixture()
+ .withLibrary(SqlLibrary.BIG_QUERY)
+ .setFor(SqlLibraryOperators.FORMAT_TIMESTAMP);
+ f.checkFails("^FORMAT_TIMESTAMP('%x', 123)^",
+ "Cannot apply 'FORMAT_TIMESTAMP' to arguments of type "
+ + "'FORMAT_TIMESTAMP\\(<CHAR\\(2\\)>, <INTEGER>\\)'\\. "
+ + "Supported form\\(s\\): "
+ + "FORMAT_TIMESTAMP\\(<CHARACTER>, "
+ + "<TIMESTAMP WITH LOCAL TIME ZONE>\\)\n"
+ + "FORMAT_TIMESTAMP\\(<CHARACTER>, "
+ + "<TIMESTAMP WITH LOCAL TIME ZONE>, <CHARACTER>\\)",
+ false);
+ f.checkScalar("FORMAT_TIMESTAMP('%c',"
+ + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')",
+ "Thu Dec 25 15:30:00 2008",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_TIMESTAMP('%b-%d-%Y',"
+ + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')",
+ "Dec-25-2008",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_TIMESTAMP('%b %Y',"
+ + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')",
+ "Dec 2008",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_TIMESTAMP('%x',"
+ + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')",
+ "12/25/08",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_TIMESTAMP('The time is: %R',"
+ + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00')",
+ "The time is: 15:30",
+ "VARCHAR(2000) NOT NULL");
+ f.checkScalar("FORMAT_TIMESTAMP('The time is: %R.%E2S',"
+ + " TIMESTAMP WITH LOCAL TIME ZONE '2008-12-25 15:30:00.1235456')",
+ "The time is: 15:30.123",
+ "VARCHAR(2000) NOT NULL");
+ }
+
+
@Test void testDenseRankFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.DENSE_RANK, VM_FENNEL, VM_JAVA);