This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 889f416158 [CALCITE-6315] Support PostgreSQL TO_CHAR, TO_DATE,
TO_TIMESTAMP
889f416158 is described below
commit 889f41615834aeec0d0d4a725c6d7ad3c355273d
Author: Norman Jordan <[email protected]>
AuthorDate: Tue Apr 2 15:20:20 2024 -0700
[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.
---
babel/src/test/resources/sql/postgresql.iq | 14 ++-
.../calcite/adapter/enumerable/RexImpTable.java | 4 +
.../org/apache/calcite/runtime/SqlFunctions.java | 34 +++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 2 +-
.../org/apache/calcite/util/BuiltInMethod.java | 4 +
.../apache/calcite/util/format/FormatModels.java | 2 +-
.../org/apache/calcite/test/SqlFunctionsTest.java | 18 ++++
.../org/apache/calcite/test/SqlValidatorTest.java | 4 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 102 +++++++++++++++++++++
9 files changed, 178 insertions(+), 6 deletions(-)
diff --git a/babel/src/test/resources/sql/postgresql.iq
b/babel/src/test/resources/sql/postgresql.iq
index 9dcb67d22f..c23cf59f47 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -63,9 +63,19 @@ EXPR$0
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 c622af252e..7a323ee9ee 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_BASE32;
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 @@ public class RexImpTable {
// 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 41d948d7cb..d3d865dfaa 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 @@ public class SqlFunctions {
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 f1ea5a4ab8..6996ecb316 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 @@ public abstract class SqlLibraryOperators {
@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 53d4d33c16..6b4e94889a 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 @@ public enum BuiltInMethod {
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 904521d946..59813d5f8b 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 @@ public class FormatModels {
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 a15f70cd41..5543032c0e 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 @@ class SqlFunctionsTest {
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 192b65fef5..e68d94c869 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 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
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 489c964c0a..e3a4c95f7b 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 @@ public class SqlOperatorTest {
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);
