[CALCITE-6315] Support PostgreSQL TO_CHAR, TO_DATE, TO_TIMESTAMP
* Both functions use PostgreSQL format patterns
* Added tests for format patterns supported by PostgreSQL but missing from Calcite
* If the data or timestamp cannot be parsed using format string, then an exception
is thrown.
diff --git a/babel/src/test/resources/sql/postgresql.iq b/babel/src/test/resources/sql/postgresql.iq
index 9dcb67d..c23cf59 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -63,9 +63,19 @@
2022-06-03 12:15:48.678
!ok
-select to_char(timestamp '2022-06-03 12:15:48.678', 'CC');
+select to_date('2022-06-03', 'YYYY-MM-DD');
EXPR$0
-21
+2022-06-03
+!ok
+
+select to_timestamp('18:46:32 2022-06-03', 'HH24:MI:SS YYYY-MM-DD');
+EXPR$0
+2022-06-03 18:46:32
+!ok
+
+select to_timestamp('18:46:32 Jun 03, 2022', 'HH24:MI:SS Mon DD, YYYY');
+EXPR$0
+2022-06-03 18:46:32
!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 c622af2..7a323ee 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
@@ -279,7 +279,9 @@
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.TO_CODE_POINTS;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_DATE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_HEX;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_TIMESTAMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRUNC_BIG_QUERY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRY_CAST;
@@ -783,6 +785,8 @@
// Datetime formatting methods
defineReflective(TO_CHAR, BuiltInMethod.TO_CHAR.method);
+ defineReflective(TO_DATE, BuiltInMethod.TO_DATE.method);
+ defineReflective(TO_TIMESTAMP, BuiltInMethod.TO_TIMESTAMP.method);
final FormatDatetimeImplementor datetimeFormatImpl =
new FormatDatetimeImplementor();
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 41d948d..d3d865d 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -4043,6 +4043,40 @@
return sb.toString().trim();
}
+ public int toDate(String dateString, String fmtString) {
+ return toInt(
+ new java.sql.Date(internalToDateTime(dateString, fmtString)));
+ }
+
+ public long toTimestamp(String timestampString, String fmtString) {
+ return toLong(
+ new java.sql.Timestamp(internalToDateTime(timestampString, fmtString)));
+ }
+
+ private long internalToDateTime(String dateString, String fmtString) {
+ final ParsePosition pos = new ParsePosition(0);
+
+ sb.setLength(0);
+ withElements(FormatModels.POSTGRESQL, fmtString, elements ->
+ elements.forEach(element -> element.toPattern(sb)));
+ final String dateFormatString = sb.toString().trim();
+
+ final SimpleDateFormat sdf = new SimpleDateFormat(dateFormatString, Locale.ENGLISH);
+ final Date date = sdf.parse(dateString, pos);
+ if (pos.getErrorIndex() >= 0 || pos.getIndex() != dateString.length()) {
+ SQLException e =
+ new SQLException(
+ String.format(Locale.ROOT,
+ "Invalid format: '%s' for datetime string: '%s'.", fmtString,
+ dateString));
+ throw Util.toUnchecked(e);
+ }
+
+ @SuppressWarnings("JavaUtilDate")
+ final long millisSinceEpoch = date.getTime();
+ return millisSinceEpoch;
+ }
+
public String formatDate(DataContext ctx, String fmtString, int date) {
return internalFormatDatetime(fmtString, internalToDate(date));
}
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 f1ea5a4..6996ecb 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
@@ -1672,7 +1672,7 @@
@LibraryOperator(libraries = {POSTGRESQL, ORACLE})
public static final SqlFunction TO_TIMESTAMP =
SqlBasicFunction.create("TO_TIMESTAMP",
- ReturnTypes.DATE_NULLABLE,
+ ReturnTypes.TIMESTAMP_NULLABLE,
OperandTypes.STRING_STRING,
SqlFunctionCategory.TIMEDATE);
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 53d4d33..6b4e948 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -648,6 +648,10 @@
DataContext.class, String.class, long.class),
TO_CHAR(SqlFunctions.DateFormatFunction.class, "toChar", long.class,
String.class),
+ TO_DATE(SqlFunctions.DateFormatFunction.class, "toDate", String.class,
+ String.class),
+ TO_TIMESTAMP(SqlFunctions.DateFormatFunction.class, "toTimestamp", String.class,
+ String.class),
FORMAT_DATE(SqlFunctions.DateFormatFunction.class, "formatDate",
DataContext.class, String.class, int.class),
FORMAT_TIME(SqlFunctions.DateFormatFunction.class, "formatTime",
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 904521d..59813d5 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
@@ -167,8 +167,8 @@
map.put("DDD", DDD);
map.put("DD", DD);
map.put("D", D);
- map.put("W", W);
map.put("WW", WW);
+ map.put("W", W);
map.put("IW", IW);
map.put("Q", Q);
// Our implementation of TO_CHAR does not support TIMESTAMPTZ
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 a15f70c..5543032 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -1759,6 +1759,24 @@
is("1500-04-30 12:00:00.123"));
}
+ @Test void testToDate() {
+ String pattern1 = "YYYY-MM-DD";
+
+ final SqlFunctions.DateFormatFunction f =
+ new SqlFunctions.DateFormatFunction();
+
+ assertThat(f.toDate("2001-10-06", pattern1), is(11601));
+ }
+
+ @Test void testToTimestamp() {
+ String pattern1 = "HH24:MI:SS YYYY-MM-DD";
+
+ final SqlFunctions.DateFormatFunction f =
+ new SqlFunctions.DateFormatFunction();
+
+ assertThat(f.toTimestamp("18:43:36 2001-10-06", pattern1), is(1002393816000L));
+ }
+
/**
* 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 192b65f..e68d94c 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -1615,14 +1615,14 @@
final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.POSTGRESQL);
expr("TO_TIMESTAMP('2000-01-01 01:00:00', 'YYYY-MM-DD HH:MM:SS')")
.withOperatorTable(opTable)
- .columnType("DATE NOT NULL");
+ .columnType("TIMESTAMP(0) NOT NULL");
wholeExpr("TO_TIMESTAMP('2000-01-01 01:00:00')")
.withOperatorTable(opTable)
.fails("Invalid number of arguments to function 'TO_TIMESTAMP'. "
+ "Was expecting 2 arguments");
expr("TO_TIMESTAMP(2000, 'YYYY')")
.withOperatorTable(opTable)
- .columnType("DATE NOT NULL");
+ .columnType("TIMESTAMP(0) NOT NULL");
wholeExpr("TO_TIMESTAMP(2000, 'YYYY')")
.withOperatorTable(opTable)
.withTypeCoercion(false)
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 489c964..e3a4c95 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -4523,11 +4523,113 @@
f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'IW')",
"23",
"VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'YYYY')",
+ "2022",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'YY')",
+ "22",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'Month')",
+ "June",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'Mon')",
+ "Jun",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'MM')",
+ "06",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'CC')",
+ "21",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'DDD')",
+ "154",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'DD')",
+ "03",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'D')",
+ "6",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'W')",
+ "1",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'WW')",
+ "23",
+ "VARCHAR NOT NULL");
+ f.checkString("to_char(timestamp '2022-06-03 13:15:48.678', 'gggggg')",
+ "gggggg",
+ "VARCHAR NOT NULL");
f.checkNull("to_char(timestamp '2022-06-03 12:15:48.678', NULL)");
f.checkNull("to_char(cast(NULL as timestamp), NULL)");
f.checkNull("to_char(cast(NULL as timestamp), 'Day')");
}
+ @Test void testToDate() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL);
+ f.setFor(SqlLibraryOperators.TO_DATE);
+
+ f.checkString("to_date('2022-06-03', 'YYYY-MM-DD')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkString("to_date('0001-01-01', 'YYYY-MM-DD')",
+ "0001-01-01",
+ "DATE NOT NULL");
+ f.checkString("to_date('Jun 03, 2022', 'Mon DD, YYYY')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkString("to_date('2022-June-03', 'YYYY-Month-DD')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkString("to_date('2022-Jun-03', 'YYYY-Mon-DD')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkString("to_date('2022-154', 'YYYY-DDD')",
+ "2022-06-03",
+ "DATE NOT NULL");
+ f.checkFails("to_date('ABCD', 'YYYY-MM-DD')",
+ "java.sql.SQLException: Invalid format: 'YYYY-MM-DD' for datetime string: 'ABCD'.",
+ true);
+ f.checkFails("to_date('2022-06-03', 'Invalid')",
+ "Illegal pattern character 'I'",
+ true);
+ f.checkNull("to_date(NULL, 'YYYY-MM-DD')");
+ f.checkNull("to_date('2022-06-03', NULL)");
+ f.checkNull("to_date(NULL, NULL)");
+ }
+
+ @Test void testToTimestamp() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL);
+ f.setFor(SqlLibraryOperators.TO_TIMESTAMP);
+
+ f.checkString("to_timestamp('2022-06-03 18:34:56', 'YYYY-MM-DD HH24:MI:SS')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('0001-01-01 18:43:56', 'YYYY-MM-DD HH24:MI:SS')",
+ "0001-01-01 18:43:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('18:34:56 Jun 03, 2022', 'HH24:MI:SS Mon DD, YYYY')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('18:34:56 2022-June-03', 'HH24:MI:SS YYYY-Month-DD')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('18:34:56 2022-Jun-03', 'HH24:MI:SS YYYY-Mon-DD')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkString("to_timestamp('18:34:56 2022-154', 'HH24:MI:SS YYYY-DDD')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP(0) NOT NULL");
+ f.checkFails("to_timestamp('ABCD', 'YYYY-MM-DD HH24:MI:SS')",
+ "java.sql.SQLException: Invalid format: 'YYYY-MM-DD HH24:MI:SS' for datetime string: 'ABCD'.",
+ true);
+ f.checkFails("to_timestamp('2022-06-03 18:34:56', 'Invalid')",
+ "Illegal pattern character 'I'",
+ true);
+ f.checkNull("to_timestamp(NULL, 'YYYY-MM-DD HH24:MI:SS')");
+ f.checkNull("to_timestamp('2022-06-03 18:34:56', NULL)");
+ f.checkNull("to_timestamp(NULL, NULL)");
+ }
+
@Test void testFromBase64() {
final SqlOperatorFixture f0 = fixture()
.setFor(SqlLibraryOperators.FROM_BASE64);
