You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by dm...@apache.org on 2023/04/14 05:51:23 UTC
[calcite] branch main updated: [CALCITE-5619] Add TO_CHAR(, ) function (enabled in MySQL,Oracle,PostgreSQL libraries)
This is an automated email from the ASF dual-hosted git repository.
dmsysolyatin pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 8a4f8d525b [CALCITE-5619] Add TO_CHAR(<TIMESTAMP>, <STRING>) function (enabled in MySQL,Oracle,PostgreSQL libraries)
8a4f8d525b is described below
commit 8a4f8d525b87c17f5aff5e86f1161b005f9d2997
Author: Tim Nieradzik <ti...@sparse.tech>
AuthorDate: Wed Mar 29 12:14:06 2023 +0300
[CALCITE-5619] Add TO_CHAR(<TIMESTAMP>, <STRING>) function (enabled in MySQL,Oracle,PostgreSQL libraries)
Close apache/calcite#3134
---
babel/src/test/resources/sql/postgresql.iq | 5 +++
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../org/apache/calcite/runtime/SqlFunctions.java | 9 +++++
.../apache/calcite/sql/SqlJdbcFunctionCall.java | 1 +
.../calcite/sql/fun/SqlLibraryOperators.java | 10 +++++
.../org/apache/calcite/sql/type/OperandTypes.java | 3 ++
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/util/format/FormatElementEnum.java | 21 ++++++++++-
.../apache/calcite/util/format/FormatModels.java | 44 +++++++++++++++++++++-
.../org/apache/calcite/test/SqlFunctionsTest.java | 29 ++++++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 12 ++++++
.../calcite/util/format/FormatElementEnumTest.java | 35 +++++++++++++++++
site/_docs/reference.md | 1 +
.../org/apache/calcite/test/SqlOperatorTest.java | 8 ++++
14 files changed, 177 insertions(+), 4 deletions(-)
diff --git a/babel/src/test/resources/sql/postgresql.iq b/babel/src/test/resources/sql/postgresql.iq
index f82b2a91bb..b69f8b0fe1 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -57,3 +57,8 @@ SELECT * FROM sal_emp;
NAME, PAY_BY_QUARTER, SCHEDULE
Bill, [10000, 10000, 10000, 10000], [[meeting, lunch], [training, presentation]]
!ok
+
+select to_char(timestamp '2022-06-03 12:15:48.678', 'YYYY-MM-DD HH24:MI:SS.MS TZ');
+EXPR$0
+2022-06-03 12:15:48.678
+!ok
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 b683216972..c197f4c001 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
@@ -185,6 +185,7 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_SECONDS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIMESTAMP_TRUNC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIME_TRUNC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_CHAR;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRUNC;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.UNIX_DATE;
@@ -586,6 +587,7 @@ public class RexImpTable {
defineMethod(TIME, "time", 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);
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 d2939dc314..a3c2ee8c04 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -2633,6 +2633,15 @@ public class SqlFunctions {
return internalFormatDatetime(fmtString, internalToTimestamp(timestamp));
}
+ 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();
+ }
+
public static String formatDate(DataContext ctx, String fmtString, int date) {
return internalFormatDatetime(fmtString, internalToDate(date));
}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
index c07e8b5c1e..17691a3a95 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
@@ -747,6 +747,7 @@ public class SqlJdbcFunctionCall extends SqlFunction {
map.put("NOW", simple(SqlStdOperatorTable.CURRENT_TIMESTAMP));
map.put("TIMESTAMPADD", simple(SqlStdOperatorTable.TIMESTAMP_ADD));
map.put("TIMESTAMPDIFF", simple(SqlStdOperatorTable.TIMESTAMP_DIFF));
+ map.put("TO_CHAR", simple(SqlLibraryOperators.TO_CHAR));
map.put("TO_DATE", simple(SqlLibraryOperators.TO_DATE));
map.put("TO_TIMESTAMP", simple(SqlLibraryOperators.TO_TIMESTAMP));
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 ae45e899e7..e53ef0cfa7 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
@@ -863,6 +863,16 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING.or(OperandTypes.BINARY),
SqlFunctionCategory.STRING);
+ /** The "TO_CHAR(timestamp, format)" function;
+ * converts {@code timestamp} to string according to the given {@code format}.
+ */
+ @LibraryOperator(libraries = {MYSQL, ORACLE, POSTGRESQL})
+ public static final SqlFunction TO_CHAR =
+ SqlBasicFunction.create("TO_CHAR",
+ ReturnTypes.VARCHAR_2000,
+ OperandTypes.TIMESTAMP_STRING,
+ SqlFunctionCategory.TIMEDATE);
+
/** The "TO_DATE(string1, string2)" function; casts string1
* to a DATE using the format specified in string2. */
@LibraryOperator(libraries = {POSTGRESQL, ORACLE})
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 b891ba7699..e8029903b7 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
@@ -818,6 +818,9 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker DATETIME_INTERVAL =
family(SqlTypeFamily.DATETIME, SqlTypeFamily.DATETIME_INTERVAL);
+ public static final SqlSingleOperandTypeChecker TIMESTAMP_STRING =
+ family(SqlTypeFamily.TIMESTAMP, SqlTypeFamily.STRING);
+
public static final SqlSingleOperandTypeChecker DATETIME_INTERVAL_INTERVAL =
family(SqlTypeFamily.DATETIME, SqlTypeFamily.DATETIME_INTERVAL,
SqlTypeFamily.DATETIME_INTERVAL);
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 dae7fdc0c0..bab8c1255f 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -509,6 +509,7 @@ public enum BuiltInMethod {
"timestampWithLocalTimeZoneToString", long.class, TimeZone.class),
FORMAT_TIMESTAMP(SqlFunctions.class, "formatTimestamp", DataContext.class,
String.class, long.class),
+ TO_CHAR(SqlFunctions.class, "toChar", long.class, String.class),
FORMAT_DATE(SqlFunctions.class, "formatDate", DataContext.class, String.class,
int.class),
FORMAT_TIME(SqlFunctions.class, "formatTime", DataContext.class, String.class,
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 7aed1fc486..2b1ea1c007 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
@@ -108,6 +108,14 @@ public enum FormatElementEnum implements FormatElement {
return work.ssssssFormat.format(date);
}
},
+ HH12("The hour (12-hour clock) as a decimal number (01-12)") {
+ @Override public String format(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);
+ }
+ },
HH24("The hour (24-hour clock) as a decimal number (00-23)") {
@Override public String format(Date date) {
final Calendar calendar = Work.get().calendar;
@@ -165,10 +173,17 @@ public enum FormatElementEnum implements FormatElement {
return String.format(Locale.ROOT, "%02d", calendar.get(Calendar.SECOND));
}
},
+ MS("The millisecond as a decimal number (000-999)") {
+ @Override public String format(Date date) {
+ final Calendar calendar = Work.get().calendar;
+ calendar.setTime(date);
+ return String.format(Locale.ROOT, "%03d", calendar.get(Calendar.MILLISECOND));
+ }
+ },
TZR("The time zone name") {
@Override public String format(Date date) {
// TODO: how to support timezones?
- throw new UnsupportedOperationException();
+ return "";
}
},
WW("The week number of the year (Sunday as the first day of the week) as a decimal "
@@ -219,7 +234,9 @@ public enum FormatElementEnum implements FormatElement {
final Calendar calendar =
Calendar.getInstance(DateTimeUtils.DEFAULT_ZONE, Locale.ROOT);
- final DateFormat eeeeFormat = new SimpleDateFormat("EEEE", 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);
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 be7a144b3a..a8e5b4b9f2 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
@@ -20,7 +20,7 @@ import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import java.util.Date;
-import java.util.HashMap;
+import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
@@ -39,12 +39,14 @@ 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.HH12;
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.MS;
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;
@@ -76,8 +78,16 @@ public class FormatModels {
*/
public static final FormatModel BIG_QUERY;
+ /** Format model for PostgreSQL.
+ *
+ * <p>PostgreSQL format element reference:
+ * <a href="https://www.postgresql.org/docs/current/functions-formatting.html">
+ * PostgreSQL Standard SQL Format Elements</a>.
+ */
+ public static final FormatModel POSTGRESQL;
+
static {
- final Map<String, FormatElement> map = new HashMap<>();
+ final Map<String, FormatElement> map = new LinkedHashMap<>();
for (FormatElementEnum fe : FormatElementEnum.values()) {
map.put(fe.toString(), fe);
}
@@ -120,6 +130,36 @@ public class FormatModels {
map.put("%y", YY);
map.put("%Z", TZR);
BIG_QUERY = create(map);
+
+ map.clear();
+ map.put("HH12", HH12);
+ map.put("HH24", HH24);
+ map.put("MI", MI);
+ map.put("SS", SS);
+ map.put("MS", MS);
+ map.put("FF1", FF1);
+ map.put("FF2", FF2);
+ map.put("FF3", FF3);
+ map.put("FF4", FF4);
+ map.put("FF5", FF5);
+ map.put("FF6", FF6);
+ map.put("YYYY", YYYY);
+ map.put("YY", YY);
+ map.put("Day", DAY);
+ map.put("DY", DY);
+ map.put("Month", MONTH);
+ map.put("Mon", MON);
+ map.put("MM", MM);
+ map.put("DDD", DDD);
+ map.put("DD", DD);
+ map.put("D", D);
+ map.put("WW", WW);
+ map.put("IW", IW);
+ map.put("Q", Q);
+ // Our implementation of TO_CHAR does not support TIMESTAMPTZ
+ // As PostgreSQL, we will skip the timezone when formatting TIMESTAMP values
+ map.put("TZ", TZR);
+ POSTGRESQL = create(map);
}
/**
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index 704b020871..700b2a5944 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -58,6 +58,7 @@ import static org.apache.calcite.runtime.SqlFunctions.regexpReplace;
import static org.apache.calcite.runtime.SqlFunctions.rtrim;
import static org.apache.calcite.runtime.SqlFunctions.sha1;
import static org.apache.calcite.runtime.SqlFunctions.toBase64;
+import static org.apache.calcite.runtime.SqlFunctions.toChar;
import static org.apache.calcite.runtime.SqlFunctions.toInt;
import static org.apache.calcite.runtime.SqlFunctions.toIntOptional;
import static org.apache.calcite.runtime.SqlFunctions.toLong;
@@ -1283,6 +1284,34 @@ class SqlFunctionsTest {
assertThat(internalToTime(86399000), is(Time.valueOf("23:59:59")));
}
+ /**
+ * Tests that timestamp can be converted to a string given a custom pattern.
+ */
+ @Test void testToChar() {
+ String pattern1 = "YYYY-MM-DD HH24:MI:SS.MS";
+ String pattern2 = "Day, DD HH12:MI:SS";
+
+ assertThat(
+ toChar(0, pattern1),
+ is("1970-01-01 00:00:00.000"));
+
+ assertThat(
+ toChar(0, pattern2),
+ is("Thursday, 01 12:00:00"));
+
+ assertThat(
+ toChar(timestampStringToUnixDate("2014-09-30 15:28:27.356"), pattern1),
+ is("2014-09-30 15:28:27.356"));
+
+ assertThat(
+ toChar(timestampStringToUnixDate("2014-09-30 15:28:27.356"), pattern2),
+ is("Tuesday, 30 03:28:27"));
+
+ assertThat(
+ toChar(timestampStringToUnixDate("1500-04-30 12:00:00.123"), pattern1),
+ is("1500-04-30 12:00:00.123"));
+ }
+
/**
* Tests that a Unix timestamp converts to a SQL timestamp in the local time
* zone.
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 2c7f67b4c9..c5f5794e79 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -1517,6 +1517,18 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ "Was expecting 3 arguments");
}
+ @Test void testToCharFunction() {
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.POSTGRESQL);
+ expr("TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.MS TZ')")
+ .withOperatorTable(opTable)
+ .ok();
+ expr("^TO_CHAR(1680080352, 'YYYY-MM-DD HH24:MI:SS.MS TZ')^")
+ .withOperatorTable(opTable)
+ .fails("Cannot apply 'TO_CHAR' to arguments of type "
+ + "'TO_CHAR\\(<INTEGER>, <CHAR\\(27\\)>\\)'\\. Supported form\\(s\\): "
+ + "'TO_CHAR\\(<TIMESTAMP>, <STRING>\\)'");
+ }
+
@Test void testToDateFunction() {
wholeExpr("TO_DATE('2000-01-01', 'YYYY-MM-DD')")
.fails("No match found for function signature "
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
new file mode 100644
index 0000000000..59a268f3a8
--- /dev/null
+++ b/core/src/test/java/org/apache/calcite/util/format/FormatElementEnumTest.java
@@ -0,0 +1,35 @@
+/*
+ * 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.junit.jupiter.api.Test;
+
+import java.time.Instant;
+import java.util.Date;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+/**
+ * Unit test for {@link FormatElementEnum}.
+ */
+class FormatElementEnumTest {
+ @Test void testDay() {
+ assertEquals(
+ FormatElementEnum.DAY.format(Date.from(Instant.parse("2014-09-30T10:00:00Z"))),
+ "Tuesday");
+ }
+}
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index f9ea1d9d60..fa55adc763 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2755,6 +2755,7 @@ BigQuery's type system uses confusingly different names for types and functions:
| b | TIME_DIFF(time, time2, timeUnit) | Returns the whole number of *timeUnit* between *time* and *time2*
| b | TIME_SUB(time, interval) | Returns the TIME value that is *interval* before *time*
| b | TIME_TRUNC(time, timeUnit) | Truncates *time* to the granularity of *timeUnit*, rounding to the beginning of the unit
+| m o p | TO_CHAR(timestamp, format) | Converts *timestamp* to a string using the format *format*
| o p | TO_DATE(string, format) | Converts *string* to a date using the format *format*
| o p | TO_TIMESTAMP(string, format) | Converts *string* to a timestamp using the format *format*
| b o p | TRANSLATE(expr, fromString, toString) | Returns *expr* with all occurrences of each character in *fromString* replaced by its corresponding character in *toString*. Characters in *expr* that are not in *fromString* are not replaced
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 a586c264f7..6538ce665b 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -3795,6 +3795,14 @@ public class SqlOperatorTest {
f.checkString("to_base64(x'61')", "YQ==", "VARCHAR NOT NULL");
}
+ @Test void testToChar() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL);
+ f.setFor(SqlLibraryOperators.TO_CHAR);
+ f.checkString("to_char(timestamp '2022-06-03 12:15:48.678', 'YYYY-MM-DD HH24:MI:SS.MS TZ')",
+ "2022-06-03 12:15:48.678",
+ "VARCHAR(2000) NOT NULL");
+ }
+
@Test void testFromBase64() {
final SqlOperatorFixture f0 = fixture()
.setFor(SqlLibraryOperators.FROM_BASE64);