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 374091b0a7 [CALCITE-6449] Enable PostgreSQL implementations of
to_date/to_timestamp
374091b0a7 is described below
commit 374091b0a7c24e61a5850e763c20587ff79e04ac
Author: Norman Jordan <[email protected]>
AuthorDate: Wed May 15 11:34:58 2024 -0700
[CALCITE-6449] Enable PostgreSQL implementations of to_date/to_timestamp
* Supports all date/time format patterns of PostreSQL 14
* Oracle will use the existing implementations
* Added to_date/to_timestamp tests to PostgreSQL iq file
* PostgreSQL to_timestamp function returns TIMESTAMP_TZ that is nullable
---
babel/src/test/resources/sql/postgresql.iq | 710 ++++++++++++++++
.../calcite/adapter/enumerable/RexImpTable.java | 4 +
.../org/apache/calcite/runtime/SqlFunctions.java | 43 +
.../calcite/sql/fun/SqlLibraryOperators.java | 22 +-
.../org/apache/calcite/sql/type/ReturnTypes.java | 7 +
.../org/apache/calcite/util/BuiltInMethod.java | 4 +
.../util/format/postgresql/ChronoUnitEnum.java | 242 ++++++
...ursFormatPattern.java => DateCalendarEnum.java} | 37 +-
.../format/postgresql/DateStringFormatPattern.java | 96 ++-
.../format/postgresql/EnumStringFormatPattern.java | 28 +-
.../util/format/postgresql/FormatPattern.java | 173 +++-
.../format/postgresql/NumberFormatPattern.java | 101 ++-
.../postgresql/PostgresqlDateTimeFormatter.java | 601 ++++++++++++-
.../postgresql/RomanNumeralMonthFormatPattern.java | 51 +-
.../format/postgresql/StringFormatPattern.java | 38 +-
.../format/postgresql/TimeZoneFormatPattern.java | 46 -
.../postgresql/TimeZoneHoursFormatPattern.java | 50 +-
.../postgresql/TimeZoneMinutesFormatPattern.java | 36 +-
.../postgresql/YearWithCommasFormatPattern.java | 106 +++
.../org/apache/calcite/test/SqlValidatorTest.java | 4 +-
.../PostgresqlDateTimeFormatterTest.java | 937 ++++++++++++++++++++-
.../org/apache/calcite/test/SqlOperatorTest.java | 72 +-
22 files changed, 3276 insertions(+), 132 deletions(-)
diff --git a/babel/src/test/resources/sql/postgresql.iq
b/babel/src/test/resources/sql/postgresql.iq
index 492c6a4d51..d2e0b53224 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -408,11 +408,721 @@ EXPR$0
null
!ok
+select to_timestamp('01', 'HH');
+EXPR$0
+0001-01-01 01:00:00
+!ok
+
+select to_timestamp('1', 'HH');
+EXPR$0
+0001-01-01 01:00:00
+!ok
+
+select to_timestamp('11', 'HH');
+EXPR$0
+0001-01-01 11:00:00
+!ok
+
+select to_timestamp('01', 'HH12');
+EXPR$0
+0001-01-01 01:00:00
+!ok
+
+select to_timestamp('1', 'HH12');
+EXPR$0
+0001-01-01 01:00:00
+!ok
+
+select to_timestamp('11', 'HH12');
+EXPR$0
+0001-01-01 11:00:00
+!ok
+
+select to_timestamp('01', 'HH24');
+EXPR$0
+0001-01-01 01:00:00
+!ok
+
+select to_timestamp('1', 'HH24');
+EXPR$0
+0001-01-01 01:00:00
+!ok
+
+select to_timestamp('18', 'HH24');
+EXPR$0
+0001-01-01 18:00:00
+!ok
+
+select to_timestamp('01', 'MI');
+EXPR$0
+0001-01-01 00:01:00
+!ok
+
+select to_timestamp('1', 'MI');
+EXPR$0
+0001-01-01 00:01:00
+!ok
+
+select to_timestamp('57', 'MI');
+EXPR$0
+0001-01-01 00:57:00
+!ok
+
+select to_timestamp('01', 'SS');
+EXPR$0
+0001-01-01 00:00:01
+!ok
+
+select to_timestamp('1', 'SS');
+EXPR$0
+0001-01-01 00:00:01
+!ok
+
+select to_timestamp('57', 'SS');
+EXPR$0
+0001-01-01 00:00:57
+!ok
+
+select to_timestamp('03AM', 'HH12AM');
+EXPR$0
+0001-01-01 03:00:00
+!ok
+
+select to_timestamp('03AM', 'HH12PM');
+EXPR$0
+0001-01-01 03:00:00
+!ok
+
+select to_timestamp('03PM', 'HH12AM');
+EXPR$0
+0001-01-01 15:00:00
+!ok
+
+select to_timestamp('03PM', 'HH12PM');
+EXPR$0
+0001-01-01 15:00:00
+!ok
+
+select to_timestamp('03A.M.', 'HH12A.M.');
+EXPR$0
+0001-01-01 03:00:00
+!ok
+
+select to_timestamp('03A.M.', 'HH12P.M.');
+EXPR$0
+0001-01-01 03:00:00
+!ok
+
+select to_timestamp('03P.M.', 'HH12A.M.');
+EXPR$0
+0001-01-01 15:00:00
+!ok
+
+select to_timestamp('03P.M.', 'HH12P.M.');
+EXPR$0
+0001-01-01 15:00:00
+!ok
+
+select to_timestamp('03am', 'HH12am');
+EXPR$0
+0001-01-01 03:00:00
+!ok
+
+select to_timestamp('03am', 'HH12pm');
+EXPR$0
+0001-01-01 03:00:00
+!ok
+
+select to_timestamp('03pm', 'HH12am');
+EXPR$0
+0001-01-01 15:00:00
+!ok
+
+select to_timestamp('03pm', 'HH12pm');
+EXPR$0
+0001-01-01 15:00:00
+!ok
+
+select to_timestamp('03a.m.', 'HH12a.m.');
+EXPR$0
+0001-01-01 03:00:00
+!ok
+
+select to_timestamp('03a.m.', 'HH12p.m.');
+EXPR$0
+0001-01-01 03:00:00
+!ok
+
+select to_timestamp('03p.m.', 'HH12a.m.');
+EXPR$0
+0001-01-01 15:00:00
+!ok
+
+select to_timestamp('03p.m.', 'HH12p.m.');
+EXPR$0
+0001-01-01 15:00:00
+!ok
+
+select to_timestamp('0,001', 'Y,YYY');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('2,024', 'Y,YYY');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('0001', 'YYYY');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'YYYY');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('2024', 'YYYY');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('001', 'YYY');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'YYY');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('987', 'YYY');
+EXPR$0
+1987-01-01 00:00:00
+!ok
+
+select to_timestamp('01', 'YY');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'YY');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('24', 'YY');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'Y');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('4', 'YY');
+EXPR$0
+2004-01-01 00:00:00
+!ok
+
+select to_timestamp('0001', 'IYYY');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'IYYY');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('2024', 'IYYY');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('001', 'IYY');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'IYY');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('987', 'IYY');
+EXPR$0
+1987-01-01 00:00:00
+!ok
+
+select to_timestamp('01', 'IY');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'IY');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('24', 'IY');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'I');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('4', 'I');
+EXPR$0
+2004-01-01 00:00:00
+!ok
+
+select to_timestamp('JANUARY', 'MONTH');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('MARCH', 'MONTH');
+EXPR$0
+0001-03-01 00:00:00
+!ok
+
+select to_timestamp('NOVEMBER', 'MONTH');
+EXPR$0
+0001-11-01 00:00:00
+!ok
+
+select to_timestamp('January', 'Month');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('March', 'Month');
+EXPR$0
+0001-03-01 00:00:00
+!ok
+
+select to_timestamp('November', 'Month');
+EXPR$0
+0001-11-01 00:00:00
+!ok
+
+select to_timestamp('january', 'month');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('march', 'month');
+EXPR$0
+0001-03-01 00:00:00
+!ok
+
+select to_timestamp('november', 'month');
+EXPR$0
+0001-11-01 00:00:00
+!ok
+
+select to_timestamp('JAN', 'MON');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('MAR', 'MON');
+EXPR$0
+0001-03-01 00:00:00
+!ok
+
+select to_timestamp('NOV', 'MON');
+EXPR$0
+0001-11-01 00:00:00
+!ok
+
+select to_timestamp('Jan', 'Mon');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('Mar', 'Mon');
+EXPR$0
+0001-03-01 00:00:00
+!ok
+
+select to_timestamp('Nov', 'Mon');
+EXPR$0
+0001-11-01 00:00:00
+!ok
+
+select to_timestamp('jan', 'mon');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('mar', 'mon');
+EXPR$0
+0001-03-01 00:00:00
+!ok
+
+select to_timestamp('nov', 'mon');
+EXPR$0
+0001-11-01 00:00:00
+!ok
+
+select to_timestamp('01', 'MM');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'MM');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('11', 'MM');
+EXPR$0
+0001-11-01 00:00:00
+!ok
+
+select to_timestamp('1982 23 MONDAY', 'IYYY IW DAY');
+EXPR$0
+1982-06-07 00:00:00
+!ok
+
+select to_timestamp('1982 23 THURSDAY', 'IYYY IW DAY');
+EXPR$0
+1982-06-10 00:00:00
+!ok
+
+select to_timestamp('1982 23 FRIDAY', 'IYYY IW DAY');
+EXPR$0
+1982-06-11 00:00:00
+!ok
+
+select to_timestamp('1982 23 Monday', 'IYYY IW Day');
+EXPR$0
+1982-06-07 00:00:00
+!ok
+
+select to_timestamp('1982 23 Thursday', 'IYYY IW Day');
+EXPR$0
+1982-06-10 00:00:00
+!ok
+
+select to_timestamp('1982 23 Friday', 'IYYY IW Day');
+EXPR$0
+1982-06-11 00:00:00
+!ok
+
+select to_timestamp('1982 23 monday', 'IYYY IW day');
+EXPR$0
+1982-06-07 00:00:00
+!ok
+
+select to_timestamp('1982 23 thursday', 'IYYY IW day');
+EXPR$0
+1982-06-10 00:00:00
+!ok
+
+select to_timestamp('1982 23 friday', 'IYYY IW day');
+EXPR$0
+1982-06-11 00:00:00
+!ok
+
+select to_timestamp('1982 23 MON', 'IYYY IW DY');
+EXPR$0
+1982-06-07 00:00:00
+!ok
+
+select to_timestamp('1982 23 THU', 'IYYY IW DY');
+EXPR$0
+1982-06-10 00:00:00
+!ok
+
+select to_timestamp('1982 23 FRI', 'IYYY IW DY');
+EXPR$0
+1982-06-11 00:00:00
+!ok
+
+select to_timestamp('1982 23 Mon', 'IYYY IW Dy');
+EXPR$0
+1982-06-07 00:00:00
+!ok
+
+select to_timestamp('1982 23 Thu', 'IYYY IW Dy');
+EXPR$0
+1982-06-10 00:00:00
+!ok
+
+select to_timestamp('1982 23 Fri', 'IYYY IW Dy');
+EXPR$0
+1982-06-11 00:00:00
+!ok
+
+select to_timestamp('1982 23 mon', 'IYYY IW dy');
+EXPR$0
+1982-06-07 00:00:00
+!ok
+
+select to_timestamp('1982 23 thu', 'IYYY IW dy');
+EXPR$0
+1982-06-10 00:00:00
+!ok
+
+select to_timestamp('1982 23 fri', 'IYYY IW dy');
+EXPR$0
+1982-06-11 00:00:00
+!ok
+
+select to_timestamp('2024 001', 'YYYY DDD');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('2024 1', 'YYYY DDD');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('2024 137', 'YYYY DDD');
+EXPR$0
+2024-05-16 00:00:00
+!ok
+
+select to_timestamp('01', 'DD');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'DD');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('23', 'DD');
+EXPR$0
+0001-01-23 00:00:00
+!ok
+
+select to_timestamp('2020 001', 'IYYY IDDD');
+EXPR$0
+2019-12-30 00:00:00
+!ok
+
+select to_timestamp('2020 1', 'IYYY IDDD');
+EXPR$0
+2019-12-30 00:00:00
+!ok
+
+select to_timestamp('2020 137', 'IYYY IDDD');
+EXPR$0
+2020-05-14 00:00:00
+!ok
+
+select to_timestamp('1982 23 1', 'IYYY IW ID');
+EXPR$0
+1982-06-07 00:00:00
+!ok
+
+select to_timestamp('1982 23 4', 'IYYY IW ID');
+EXPR$0
+1982-06-10 00:00:00
+!ok
+
+select to_timestamp('1982 23 5', 'IYYY IW ID');
+EXPR$0
+1982-06-11 00:00:00
+!ok
+
+select to_timestamp('2024 1 1', 'YYYY MM W');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('2024 4 2', 'YYYY MM W');
+EXPR$0
+2024-04-08 00:00:00
+!ok
+
+select to_timestamp('2024 11 4', 'YYYY MM W');
+EXPR$0
+2024-11-22 00:00:00
+!ok
+
+select to_timestamp('2024 01', 'YYYY WW');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('2024 1', 'YYYY WW');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('2024 51', 'YYYY WW');
+EXPR$0
+2024-12-16 00:00:00
+!ok
+
+select to_timestamp('2020 01', 'IYYY IW');
+EXPR$0
+2019-12-30 00:00:00
+!ok
+
+select to_timestamp('2020 1', 'IYYY IW');
+EXPR$0
+2019-12-30 00:00:00
+!ok
+
+select to_timestamp('2020 51', 'IYYY IW');
+EXPR$0
+2020-12-14 00:00:00
+!ok
+
+select to_timestamp('21', 'CC');
+EXPR$0
+2001-01-01 00:00:00
+!ok
+
+select to_timestamp('16', 'CC');
+EXPR$0
+1501-01-01 00:00:00
+!ok
+
+select to_timestamp('1', 'CC');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('2460311', 'J');
+EXPR$0
+2024-01-01 00:00:00
+!ok
+
+select to_timestamp('2445897', 'J');
+EXPR$0
+1984-07-15 00:00:00
+!ok
+
+select to_timestamp('1806606', 'J');
+EXPR$0
+0234-03-21 00:00:00
+!ok
+
+select to_timestamp('I', 'RM');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('IV', 'RM');
+EXPR$0
+0001-04-01 00:00:00
+!ok
+
+select to_timestamp('IX', 'RM');
+EXPR$0
+0001-09-01 00:00:00
+!ok
+
+select to_timestamp('i', 'rm');
+EXPR$0
+0001-01-01 00:00:00
+!ok
+
+select to_timestamp('iv', 'rm');
+EXPR$0
+0001-04-01 00:00:00
+!ok
+
+select to_timestamp('ix', 'rm');
+EXPR$0
+0001-09-01 00:00:00
+!ok
+
select to_date('2022-06-03', 'YYYY-MM-DD');
EXPR$0
2022-06-03
!ok
+select to_date('2,024-04-17', 'Y,YYY-MM-DD');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('24-04-17', 'YYY-MM-DD');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('24-04-17', 'YY-MM-DD');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('2124-04-17', 'CCYY-MM-DD');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('20240417', 'YYYYMMDD');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('2,0240417', 'Y,YYYMMDD');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('2024-16-3', 'IYYY-IW-ID');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('2024-16 Wednesday', 'IYYY-IW Day');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('2024-108', 'IYYY-IDDD');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('April 17, 2024', 'Month DD, YYYY');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('IV 17, 2024', 'RM DD, YYYY');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('APR 17, 2024', 'MON DD, YYYY');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('2024-16', 'YYYY-WW');
+EXPR$0
+2024-04-15
+!ok
+
+select to_date('2024-108', 'YYYY-DDD');
+EXPR$0
+2024-04-17
+!ok
+
+select to_date('0000-01-01', 'YYYY-MM-DD');
+EXPR$0
+0001-01-01
+!ok
+
select to_timestamp('18:46:32 2022-06-03', 'HH24:MI:SS YYYY-MM-DD');
EXPR$0
2022-06-03 18:46:32
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 ce70c6cebe..3e6088d626 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
@@ -288,8 +288,10 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.TO_CHAR;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_CHAR_PG;
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_DATE_PG;
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.TO_TIMESTAMP_PG;
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;
@@ -801,7 +803,9 @@ public class RexImpTable {
defineReflective(TO_CHAR, BuiltInMethod.TO_CHAR.method);
defineReflective(TO_CHAR_PG, BuiltInMethod.TO_CHAR_PG.method);
defineReflective(TO_DATE, BuiltInMethod.TO_DATE.method);
+ defineReflective(TO_DATE_PG, BuiltInMethod.TO_DATE_PG.method);
defineReflective(TO_TIMESTAMP, BuiltInMethod.TO_TIMESTAMP.method);
+ defineReflective(TO_TIMESTAMP_PG, BuiltInMethod.TO_TIMESTAMP_PG.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 30fb4d54ee..6625e14a02 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -4033,6 +4033,20 @@ public class SqlFunctions {
* {@code FORMAT_DATETIME}, {@code FORMAT_TIME}, {@code TO_CHAR} functions.
*/
@Deterministic
public static class DateFormatFunction {
+ // Timezone to use for PostgreSQL parsing of timestamps
+ private static final ZoneId LOCAL_ZONE;
+ static {
+ ZoneId zoneId;
+ try {
+ // Currently the parsed timestamps are expected to be the number of
+ // milliseconds since the epoch in UTC, with no timezone information
+ zoneId = ZoneId.of("UTC");
+ } catch (Exception e) {
+ zoneId = ZoneId.systemDefault();
+ }
+ LOCAL_ZONE = zoneId;
+ }
+
/** Work space for various functions. Clear it before you use it. */
final StringBuilder sb = new StringBuilder();
@@ -4090,11 +4104,40 @@ public class SqlFunctions {
new java.sql.Date(internalToDateTime(dateString, fmtString)));
}
+ public int toDatePg(String dateString, String fmtString) {
+ try {
+ return (int) PostgresqlDateTimeFormatter.toTimestamp(dateString,
fmtString,
+ LOCAL_ZONE)
+ .getLong(ChronoField.EPOCH_DAY);
+ } catch (Exception e) {
+ SQLException sqlEx =
+ new SQLException(
+ String.format(Locale.ROOT,
+ "Invalid format: '%s' for datetime string: '%s'.",
fmtString,
+ dateString));
+ throw Util.toUnchecked(sqlEx);
+ }
+ }
+
public long toTimestamp(String timestampString, String fmtString) {
return toLong(
new java.sql.Timestamp(internalToDateTime(timestampString,
fmtString)));
}
+ public long toTimestampPg(String timestampString, String fmtString) {
+ try {
+ return PostgresqlDateTimeFormatter.toTimestamp(timestampString,
fmtString, LOCAL_ZONE)
+ .toInstant().toEpochMilli();
+ } catch (Exception e) {
+ SQLException sqlEx =
+ new SQLException(
+ String.format(Locale.ROOT,
+ "Invalid format: '%s' for timestamp string: '%s'.",
fmtString,
+ timestampString));
+ throw Util.toUnchecked(sqlEx);
+ }
+ }
+
private long internalToDateTime(String dateString, String fmtString) {
final ParsePosition pos = new ParsePosition(0);
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 642b74690a..5c876e0614 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
@@ -1726,22 +1726,40 @@ public abstract class SqlLibraryOperators {
/** The "TO_DATE(string1, string2)" function; casts string1
* to a DATE using the format specified in string2. */
- @LibraryOperator(libraries = {ORACLE, POSTGRESQL})
+ @LibraryOperator(libraries = {ORACLE, REDSHIFT})
public static final SqlFunction TO_DATE =
SqlBasicFunction.create("TO_DATE",
ReturnTypes.DATE_NULLABLE,
OperandTypes.STRING_STRING,
SqlFunctionCategory.TIMEDATE);
+ /** The "TO_DATE(string1, string2)" function for PostgreSQL; casts string1
+ * to a DATE using the format specified in string2. */
+ @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = {REDSHIFT})
+ public static final SqlFunction TO_DATE_PG =
+ new SqlBasicFunction("TO_DATE", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.DATE_NULLABLE, null,
+ OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+ SqlFunctionCategory.TIMEDATE, call -> SqlMonotonicity.NOT_MONOTONIC,
false) { };
+
/** The "TO_TIMESTAMP(string1, string2)" function; casts string1
* to a TIMESTAMP using the format specified in string2. */
- @LibraryOperator(libraries = {ORACLE, POSTGRESQL})
+ @LibraryOperator(libraries = {ORACLE, REDSHIFT})
public static final SqlFunction TO_TIMESTAMP =
SqlBasicFunction.create("TO_TIMESTAMP",
ReturnTypes.TIMESTAMP_NULLABLE,
OperandTypes.STRING_STRING,
SqlFunctionCategory.TIMEDATE);
+ /** The "TO_TIMESTAMP(string1, string2)" function for PostgreSQL; casts
string1
+ * to a TIMESTAMP using the format specified in string2. */
+ @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = {REDSHIFT})
+ public static final SqlFunction TO_TIMESTAMP_PG =
+ new SqlBasicFunction("TO_TIMESTAMP", SqlKind.OTHER_FUNCTION,
+ SqlSyntax.FUNCTION, true, ReturnTypes.TIMESTAMP_TZ_NULLABLE, null,
+ OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+ SqlFunctionCategory.TIMEDATE, call -> SqlMonotonicity.NOT_MONOTONIC,
false) { };
+
/**
* The "PARSE_TIME(string, string)" function (BigQuery);
* converts a string representation of time to a TIME value.
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index 5153a18fb4..40b5353514 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
@@ -391,6 +391,13 @@ public abstract class ReturnTypes {
public static final SqlReturnTypeInference TIMESTAMP_LTZ_NULLABLE =
TIMESTAMP_LTZ.andThen(SqlTypeTransforms.TO_NULLABLE);
+ /**
+ * Type-inference strategy whereby the result type of a call is nullable
+ * TIMESTAMP WITH TIME ZONE.
+ */
+ public static final SqlReturnTypeInference TIMESTAMP_TZ_NULLABLE =
+ TIMESTAMP_TZ.andThen(SqlTypeTransforms.TO_NULLABLE);
+
/**
* Type-inference strategy whereby the result type of a call is Double.
*/
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index ff4a57b909..7b144167da 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -662,8 +662,12 @@ public enum BuiltInMethod {
String.class),
TO_DATE(SqlFunctions.DateFormatFunction.class, "toDate", String.class,
String.class),
+ TO_DATE_PG(SqlFunctions.DateFormatFunction.class, "toDatePg", String.class,
+ String.class),
TO_TIMESTAMP(SqlFunctions.DateFormatFunction.class, "toTimestamp",
String.class,
String.class),
+ TO_TIMESTAMP_PG(SqlFunctions.DateFormatFunction.class, "toTimestampPg",
String.class,
+ String.class),
FORMAT_DATE(SqlFunctions.DateFormatFunction.class, "formatDate",
String.class, int.class),
FORMAT_TIME(SqlFunctions.DateFormatFunction.class, "formatTime",
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/ChronoUnitEnum.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/ChronoUnitEnum.java
new file mode 100644
index 0000000000..b1e52cfebf
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/ChronoUnitEnum.java
@@ -0,0 +1,242 @@
+/*
+ * 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.postgresql;
+
+import com.google.common.collect.ImmutableSet;
+
+import java.time.temporal.ChronoUnit;
+import java.util.Set;
+
+import static
org.apache.calcite.util.format.postgresql.DateCalendarEnum.GREGORIAN;
+import static
org.apache.calcite.util.format.postgresql.DateCalendarEnum.ISO_8601;
+import static
org.apache.calcite.util.format.postgresql.DateCalendarEnum.JULIAN;
+import static org.apache.calcite.util.format.postgresql.DateCalendarEnum.NONE;
+
+/**
+ * A component of a datetime. May belong to a type of calendar. Also contains
+ * a list of parent values. For example months are in a year. A datetime can be
+ * reconstructed from one or more <code>ChronUnitEnum</code> items along with
+ * their associated values.
+ *
+ * <p>Some <code>ChronoUnitEnum</code> items conflict with others.
+ */
+public enum ChronoUnitEnum {
+ ERAS(ChronoUnit.ERAS, NONE),
+ CENTURIES(
+ ChronoUnit.CENTURIES,
+ ImmutableSet.of(ISO_8601, GREGORIAN),
+ ERAS),
+ YEARS_ISO_8601(
+ ChronoUnit.YEARS,
+ ISO_8601,
+ CENTURIES),
+ YEARS_IN_MILLENIA_ISO_8601(
+ ChronoUnit.YEARS,
+ ISO_8601,
+ CENTURIES),
+ YEARS_IN_CENTURY_ISO_8601(
+ ChronoUnit.YEARS,
+ ISO_8601,
+ CENTURIES),
+ DAYS_IN_YEAR_ISO_8601(
+ ChronoUnit.DAYS,
+ ISO_8601,
+ YEARS_ISO_8601),
+ WEEKS_IN_YEAR_ISO_8601(
+ ChronoUnit.WEEKS,
+ ISO_8601,
+ YEARS_ISO_8601),
+ DAYS_JULIAN(
+ ChronoUnit.DAYS,
+ JULIAN),
+ YEARS(
+ ChronoUnit.YEARS,
+ GREGORIAN,
+ CENTURIES),
+ YEARS_IN_MILLENIA(
+ ChronoUnit.YEARS,
+ GREGORIAN,
+ CENTURIES),
+ YEARS_IN_CENTURY(
+ ChronoUnit.YEARS,
+ GREGORIAN,
+ CENTURIES),
+ MONTHS_IN_YEAR(
+ ChronoUnit.MONTHS,
+ GREGORIAN,
+ YEARS, YEARS_IN_CENTURY),
+ DAYS_IN_YEAR(
+ ChronoUnit.DAYS,
+ GREGORIAN,
+ YEARS, YEARS_IN_CENTURY),
+ DAYS_IN_MONTH(
+ ChronoUnit.DAYS,
+ GREGORIAN,
+ MONTHS_IN_YEAR),
+ WEEKS_IN_YEAR(
+ ChronoUnit.WEEKS,
+ GREGORIAN,
+ YEARS, YEARS_IN_CENTURY),
+ WEEKS_IN_MONTH(
+ ChronoUnit.WEEKS,
+ GREGORIAN,
+ MONTHS_IN_YEAR),
+ DAYS_IN_WEEK(
+ ChronoUnit.DAYS,
+ NONE,
+ YEARS_ISO_8601, WEEKS_IN_MONTH, WEEKS_IN_YEAR),
+ HOURS_IN_DAY(
+ ChronoUnit.HOURS,
+ NONE,
+ DAYS_IN_YEAR, DAYS_IN_MONTH, DAYS_IN_WEEK),
+ HALF_DAYS(
+ ChronoUnit.HALF_DAYS,
+ NONE,
+ DAYS_IN_YEAR, DAYS_IN_MONTH, DAYS_IN_WEEK),
+ HOURS_IN_HALF_DAY(
+ ChronoUnit.HOURS,
+ NONE,
+ HALF_DAYS),
+ MINUTES_IN_HOUR(
+ ChronoUnit.MINUTES,
+ NONE,
+ HOURS_IN_DAY, HOURS_IN_HALF_DAY),
+ SECONDS_IN_DAY(
+ ChronoUnit.SECONDS,
+ NONE,
+ DAYS_IN_YEAR, DAYS_IN_MONTH, DAYS_IN_WEEK),
+ SECONDS_IN_MINUTE(
+ ChronoUnit.SECONDS,
+ NONE,
+ MINUTES_IN_HOUR),
+ MILLIS(
+ ChronoUnit.MILLIS,
+ NONE,
+ SECONDS_IN_DAY, SECONDS_IN_MINUTE),
+ MICROS(
+ ChronoUnit.MICROS,
+ NONE,
+ MILLIS),
+ TENTHS_OF_SECOND(
+ ChronoUnit.MILLIS,
+ NONE,
+ SECONDS_IN_DAY, SECONDS_IN_MINUTE),
+ HUNDREDTHS_OF_SECOND(
+ ChronoUnit.MILLIS,
+ NONE,
+ SECONDS_IN_DAY, SECONDS_IN_MINUTE),
+ THOUSANDTHS_OF_SECOND(
+ ChronoUnit.MILLIS,
+ NONE,
+ SECONDS_IN_DAY, SECONDS_IN_MINUTE),
+ TENTHS_OF_MS(
+ ChronoUnit.MICROS,
+ NONE,
+ SECONDS_IN_DAY, SECONDS_IN_MINUTE),
+ HUNDREDTHS_OF_MS(
+ ChronoUnit.MICROS,
+ NONE,
+ SECONDS_IN_DAY, SECONDS_IN_MINUTE),
+ THOUSANDTHS_OF_MS(
+ ChronoUnit.MICROS,
+ NONE,
+ SECONDS_IN_DAY, SECONDS_IN_MINUTE),
+ TIMEZONE_HOURS(
+ ChronoUnit.HOURS,
+ NONE),
+ TIMEZONE_MINUTES(
+ ChronoUnit.MINUTES,
+ NONE,
+ TIMEZONE_HOURS);
+
+ private final ChronoUnit chronoUnit;
+ private final ImmutableSet<ChronoUnitEnum> parentUnits;
+ private final ImmutableSet<DateCalendarEnum> calendars;
+
+ ChronoUnitEnum(ChronoUnit chronoUnit, DateCalendarEnum calendar,
+ ChronoUnitEnum... parentUnits) {
+ this.chronoUnit = chronoUnit;
+ this.parentUnits = ImmutableSet.copyOf(parentUnits);
+ this.calendars = ImmutableSet.of(calendar);
+ }
+
+ ChronoUnitEnum(ChronoUnit chronoUnit, Set<DateCalendarEnum> calendars,
+ ChronoUnitEnum... parentUnits) {
+ this.chronoUnit = chronoUnit;
+ this.parentUnits = ImmutableSet.copyOf(parentUnits);
+ this.calendars =
ImmutableSet.<DateCalendarEnum>builder().addAll(calendars).build();
+ }
+
+ /**
+ * Get the ChronoUnit value that corresponds to this value.
+ *
+ * @return a ChronoUnit value
+ */
+ public ChronoUnit getChronoUnit() {
+ return chronoUnit;
+ }
+
+ /**
+ * Get the set of calendars that this value can be in.
+ *
+ * @return set of calendars that this value can be in
+ */
+ public Set<DateCalendarEnum> getCalendars() {
+ return calendars;
+ }
+
+ /**
+ * Checks if the current item can be added to <code>units</code> without
causing
+ * any conflicts.
+ *
+ * @param units a <code>Set</code> of items to test against
+ * @return <code>true</code> if this item does not conflict with
<code>units</code>
+ */
+ public boolean isCompatible(Set<ChronoUnitEnum> units) {
+ if (!calendars.isEmpty()) {
+ for (ChronoUnitEnum unit : units) {
+ boolean haveCompatibleCalendar = false;
+
+ for (DateCalendarEnum unitCalendar : unit.getCalendars()) {
+ for (DateCalendarEnum calendar : calendars) {
+ if (unitCalendar == NONE || calendar == NONE
+ || unitCalendar.isCalendarCompatible(calendar)) {
+ haveCompatibleCalendar = true;
+ break;
+ }
+ }
+
+ if (haveCompatibleCalendar) {
+ break;
+ }
+ }
+
+ if (!haveCompatibleCalendar) {
+ return false;
+ }
+ }
+ }
+
+ for (ChronoUnitEnum unit : units) {
+ if (parentUnits.equals(unit.parentUnits)) {
+ return false;
+ }
+ }
+
+ return true;
+ }
+}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneHoursFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/DateCalendarEnum.java
similarity index 54%
copy from
core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneHoursFormatPattern.java
copy to
core/src/main/java/org/apache/calcite/util/format/postgresql/DateCalendarEnum.java
index 5c07559730..9b3d46dba7 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneHoursFormatPattern.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/DateCalendarEnum.java
@@ -16,27 +16,26 @@
*/
package org.apache.calcite.util.format.postgresql;
-import org.checkerframework.checker.nullness.qual.Nullable;
-
-import java.time.ZonedDateTime;
-import java.time.temporal.ChronoField;
-import java.util.Locale;
-
/**
- * Able to parse timezone hours from string and to generate a string of the
timezone
- * hours from a datetime. Timezone hours always have a sign (+/-) and are
between
- * -15 and +15.
+ * Calendar types that can be used in PostgreSQL datetime formats.
*/
-public class TimeZoneHoursFormatPattern extends StringFormatPattern {
- public TimeZoneHoursFormatPattern() {
- super("TZH");
- }
+public enum DateCalendarEnum {
+ GREGORIAN,
+ JULIAN,
+ ISO_8601,
+ NONE;
+
+ /**
+ * Tests if the provided calendar is compatible with this calendar.
+ *
+ * @param otherCalendar calendar to test with this
+ * @return whether the provided calendar is compatible with this
+ */
+ public boolean isCalendarCompatible(DateCalendarEnum otherCalendar) {
+ if (this == NONE || otherCalendar == NONE) {
+ return true;
+ }
- @Override String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
- @Nullable String suffix, Locale locale) {
- return String.format(
- Locale.ROOT,
- "%+02d",
- dateTime.getOffset().get(ChronoField.OFFSET_SECONDS) / 3600);
+ return this == otherCalendar;
}
}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/DateStringFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/DateStringFormatPattern.java
index 896fdcaca9..7055cc00d0 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/DateStringFormatPattern.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/DateStringFormatPattern.java
@@ -18,6 +18,8 @@ package org.apache.calcite.util.format.postgresql;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.text.ParseException;
+import java.text.ParsePosition;
import java.time.DayOfWeek;
import java.time.Month;
import java.time.ZonedDateTime;
@@ -40,19 +42,65 @@ public class DateStringFormatPattern<T> extends
StringFormatPattern {
* that has a string representation
*/
private interface DateStringConverter<T> {
+ /**
+ * Get the ChronoUnitEnum value that this converter handles.
+ *
+ * @return a ChronoUnitEnum value
+ */
+ ChronoUnitEnum getChronoUnit();
+
+ /**
+ * Extract the value of this datetime component from the provided value.
+ *
+ * @param dateTime where to extract the value from
+ * @return the extracted value
+ */
T getValueFromDateTime(ZonedDateTime dateTime);
+ /**
+ * An array of the possible string values.
+ *
+ * @return array of possible string values
+ */
+ T[] values();
+
+ /**
+ * Generate the string representation of a value. This may involve
formatting as well
+ * as translating to the provided locale.
+ *
+ * @param value value to convert
+ * @param textStyle how to format the value
+ * @param haveFillMode if false add padding spaces to the correct length
+ * @param locale locale to translate to
+ * @return converted string value
+ */
String getDisplayName(T value, TextStyle textStyle, boolean haveFillMode,
Locale locale);
+
+ /**
+ * Get the int value for the provided value (such as a month).
+ *
+ * @param value value to convert to an int
+ * @return result of converting the value to an int
+ */
+ int getValue(T value);
}
/**
* Can convert between a day of week name and the corresponding datetime
component value.
*/
private static class DayOfWeekConverter implements
DateStringConverter<DayOfWeek> {
+ @Override public ChronoUnitEnum getChronoUnit() {
+ return ChronoUnitEnum.DAYS_IN_WEEK;
+ }
+
@Override public DayOfWeek getValueFromDateTime(ZonedDateTime dateTime) {
return dateTime.getDayOfWeek();
}
+ @Override public DayOfWeek[] values() {
+ return DayOfWeek.values();
+ }
+
@Override public String getDisplayName(DayOfWeek value, TextStyle
textStyle,
boolean haveFillMode, Locale locale) {
final String formattedValue = value.getDisplayName(textStyle, locale);
@@ -65,16 +113,28 @@ public class DateStringFormatPattern<T> extends
StringFormatPattern {
return formattedValue;
}
}
+
+ @Override public int getValue(DayOfWeek value) {
+ return value.getValue();
+ }
}
/**
* Can convert between a month name and the corresponding datetime component
value.
*/
private static class MonthConverter implements DateStringConverter<Month> {
+ @Override public ChronoUnitEnum getChronoUnit() {
+ return ChronoUnitEnum.MONTHS_IN_YEAR;
+ }
+
@Override public Month getValueFromDateTime(ZonedDateTime dateTime) {
return dateTime.getMonth();
}
+ @Override public Month[] values() {
+ return Month.values();
+ }
+
@Override public String getDisplayName(Month value, TextStyle textStyle,
boolean haveFillMode,
Locale locale) {
final String formattedValue = value.getDisplayName(textStyle, locale);
@@ -88,19 +148,24 @@ public class DateStringFormatPattern<T> extends
StringFormatPattern {
return formattedValue;
}
}
+
+ @Override public int getValue(Month value) {
+ return value.getValue();
+ }
}
private static final DateStringConverter<DayOfWeek> DAY_OF_WEEK = new
DayOfWeekConverter();
private static final DateStringConverter<Month> MONTH = new MonthConverter();
- private final DateStringConverter<T> dateStringConverter;
+ private final DateStringConverter<T> dateStringEnum;
private final CapitalizationEnum capitalization;
private final TextStyle textStyle;
- private DateStringFormatPattern(DateStringConverter<T> dateStringConverter,
+ private DateStringFormatPattern(
+ ChronoUnitEnum chronoUnit, DateStringConverter<T> dateStringEnum,
TextStyle textStyle, CapitalizationEnum capitalization, String...
patterns) {
- super(patterns);
- this.dateStringConverter = dateStringConverter;
+ super(chronoUnit, patterns);
+ this.dateStringEnum = dateStringEnum;
this.capitalization = capitalization;
this.textStyle = textStyle;
}
@@ -108,6 +173,7 @@ public class DateStringFormatPattern<T> extends
StringFormatPattern {
public static DateStringFormatPattern<DayOfWeek> forDayOfWeek(TextStyle
textStyle,
CapitalizationEnum capitalization, String... patterns) {
return new DateStringFormatPattern<>(
+ DAY_OF_WEEK.getChronoUnit(),
DAY_OF_WEEK,
textStyle,
capitalization,
@@ -117,17 +183,35 @@ public class DateStringFormatPattern<T> extends
StringFormatPattern {
public static DateStringFormatPattern<Month> forMonth(TextStyle textStyle,
CapitalizationEnum capitalization, String... patterns) {
return new DateStringFormatPattern<>(
+ MONTH.getChronoUnit(),
MONTH,
textStyle,
capitalization,
patterns);
}
+ @Override protected int parseValue(ParsePosition inputPosition, String input,
+ Locale locale, boolean haveFillMode, boolean enforceLength) throws
ParseException {
+ final String inputTrimmed = input.substring(inputPosition.getIndex());
+
+ for (T value : dateStringEnum.values()) {
+ final String formattedValue =
+ capitalization.apply(dateStringEnum.getDisplayName(value, textStyle,
false, locale),
+ locale);
+ if (inputTrimmed.startsWith(formattedValue.trim())) {
+ inputPosition.setIndex(inputPosition.getIndex() +
formattedValue.trim().length());
+ return dateStringEnum.getValue(value);
+ }
+ }
+
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
@Override public String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
@Nullable String suffix, Locale locale) {
return capitalization.apply(
- dateStringConverter.getDisplayName(
- dateStringConverter.getValueFromDateTime(dateTime),
+ dateStringEnum.getDisplayName(
+ dateStringEnum.getValueFromDateTime(dateTime),
textStyle,
haveFillMode,
locale), locale);
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/EnumStringFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/EnumStringFormatPattern.java
index 29a643b784..26f91d2ac3 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/EnumStringFormatPattern.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/EnumStringFormatPattern.java
@@ -18,6 +18,8 @@ package org.apache.calcite.util.format.postgresql;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.text.ParseException;
+import java.text.ParsePosition;
import java.time.ZonedDateTime;
import java.time.temporal.ChronoField;
import java.util.Locale;
@@ -31,12 +33,34 @@ public class EnumStringFormatPattern extends
StringFormatPattern {
private final ChronoField chronoField;
private final String[] enumValues;
- public EnumStringFormatPattern(ChronoField chronoField, String... patterns) {
- super(patterns);
+ /**
+ * Constructs a new EnumStringFormatPattern for the provide list of pattern
strings and
+ * ChronoUnitEnum value.
+ *
+ * @param chronoUnit ChronoUnitEnum value that this pattern parses
+ * @param patterns array of pattern strings
+ */
+ public EnumStringFormatPattern(ChronoUnitEnum chronoUnit, ChronoField
chronoField,
+ String... patterns) {
+ super(chronoUnit, patterns);
this.chronoField = chronoField;
this.enumValues = patterns;
}
+ @Override protected int parseValue(ParsePosition inputPosition, String
input, Locale locale,
+ boolean haveFillMode, boolean enforceLength) throws ParseException {
+ final String inputTrimmed = input.substring(inputPosition.getIndex());
+
+ for (int i = 0; i < enumValues.length; i++) {
+ if (inputTrimmed.startsWith(enumValues[i])) {
+ inputPosition.setIndex(inputPosition.getIndex() +
enumValues[i].length());
+ return i;
+ }
+ }
+
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
@Override public String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
@Nullable String suffix, Locale locale) {
final int value = dateTime.get(chronoField);
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/FormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/FormatPattern.java
index dbeb7ac6fe..f0194dd5b0 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/FormatPattern.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/FormatPattern.java
@@ -18,13 +18,36 @@ package org.apache.calcite.util.format.postgresql;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.text.ParseException;
import java.text.ParsePosition;
import java.time.ZonedDateTime;
+import java.util.Locale;
/**
* A format element that is able to produce a string from a date.
*/
-public interface FormatPattern {
+public abstract class FormatPattern {
+ private String[] patterns;
+
+ /**
+ * Creates a new FormatPattern with the provided pattern strings. Child
classes
+ * must call this constructor.
+ *
+ * @param patterns array of patterns
+ */
+ protected FormatPattern(String[] patterns) {
+ this.patterns = patterns;
+ }
+
+ /**
+ * Get the array of pattern strings.
+ *
+ * @return array of pattern strings
+ */
+ public String[] getPatterns() {
+ return patterns;
+ }
+
/**
* Checks if this pattern matches the substring starting at the
<code>parsePosition</code>
* in the <code>formatString</code>. If it matches, then the
<code>dateTime</code> is
@@ -36,6 +59,152 @@ public interface FormatPattern {
* @param dateTime datetime to convert
* @return the string representation of the datetime based on the format
pattern
*/
- @Nullable String convert(ParsePosition parsePosition, String formatString,
+ public abstract @Nullable String convert(ParsePosition parsePosition, String
formatString,
ZonedDateTime dateTime);
+
+ /**
+ * Get the ChronoUnitEnum value that this format pattern represents. For
example, the
+ * pattern YYYY is for YEAR.
+ *
+ * @return a ChronoUnitEnum value
+ */
+ protected abstract ChronoUnitEnum getChronoUnit();
+
+ /**
+ * Attempts to parse a single value from the input for this pattern. It will
start parsing
+ * from inputPosition. The format string and position are also provided in
case
+ * they have any flags applied such as FM or TM.
+ *
+ * @param inputPosition where to start parsing the input from
+ * @param input string that is getting parsed
+ * @param formatPosition where this format pattern starts in the format
string
+ * @param formatString full format string that the user provided
+ * @param enforceLength should parsing stop once a fixed number of
characters have been
+ * parsed. Some patterns like YYYY can match more than
4 digits, while
+ * others like HH24 must match exactly two digits.
+ * @return the long value of the datetime component that was parsed
+ * @throws ParseException if the pattern could not be applied to the input
+ */
+ public long parse(ParsePosition inputPosition, String input, ParsePosition
formatPosition,
+ String formatString, boolean enforceLength) throws ParseException {
+
+ boolean haveFillMode = false;
+ boolean haveTranslateMode = false;
+
+ String formatTrimmed = formatString.substring(formatPosition.getIndex());
+ if (formatTrimmed.startsWith("FMTM") || formatTrimmed.startsWith("TMFM")) {
+ haveFillMode = true;
+ haveTranslateMode = true;
+ formatTrimmed = formatTrimmed.substring(4);
+ } else if (formatTrimmed.startsWith("FM")) {
+ haveFillMode = true;
+ formatTrimmed = formatTrimmed.substring(2);
+ } else if (formatTrimmed.startsWith("TM")) {
+ haveTranslateMode = true;
+ formatTrimmed = formatTrimmed.substring(2);
+ }
+
+ for (String pattern : patterns) {
+ if (formatTrimmed.startsWith(pattern)) {
+ formatTrimmed = formatTrimmed.substring(pattern.length());
+ break;
+ }
+ }
+
+ try {
+ final Locale locale = haveTranslateMode ? Locale.getDefault() :
Locale.US;
+ long parsedValue = parseValue(inputPosition, input, locale,
haveFillMode, enforceLength);
+ formatPosition.setIndex(formatString.length() - formatTrimmed.length());
+
+ return parsedValue;
+ } catch (ParseException e) {
+ inputPosition.setErrorIndex(inputPosition.getIndex());
+ throw e;
+ }
+ }
+
+ /**
+ * Attempts to parse a single value from the input for this pattern. It will
start parsing
+ * from inputPosition.
+ *
+ * @param inputPosition where to start parsing the input from
+ * @param input string that is getting parsed
+ * @param locale Locale to use when parsing text values, such as month names
+ * @param haveFillMode is fill mode enabled
+ * @param enforceLength should parsing stop once a fixed number of
characters have been
+ * parsed. Some patterns like YYYY can match more than
4 digits, while
+ * others like HH24 must match exactly two digits.
+ * @return the int value of the datetime component that was parsed
+ * @throws ParseException if the pattern could not be applied to the input
+ */
+ protected abstract int parseValue(ParsePosition inputPosition, String input,
Locale locale,
+ boolean haveFillMode, boolean enforceLength) throws ParseException;
+
+ /**
+ * Get the length of this format pattern from the full pattern. This will
include any
+ * modifiers on the pattern.
+ *
+ * @param formatString the full format pattern from the user with all
characters before this
+ * pattern removed
+ * @return length of this format pattern
+ */
+ int getFormatLength(final String formatString) {
+ int length = 0;
+
+ for (String prefix : new String[] {"FM", "TM"}) {
+ if (formatString.substring(length).startsWith(prefix)) {
+ length += 2;
+ }
+ }
+
+ String formatTrimmed = formatString.substring(length);
+ for (String pattern : patterns) {
+ if (formatTrimmed.startsWith(pattern)) {
+ length += pattern.length();
+ break;
+ }
+ }
+
+ formatTrimmed = formatString.substring(length);
+ if (formatTrimmed.startsWith("TH") || formatTrimmed.startsWith("th")) {
+ length += 2;
+ }
+
+ return length;
+ }
+
+ /**
+ * Get the length of this format pattern from the full pattern. This will
include any
+ * prefix modifiers on the pattern.
+ *
+ * @param formatString the full format pattern from the user
+ * @param formatParsePosition where to start reading in the format string
+ * @return length of this format pattern with any prefixes
+ */
+ int matchedPatternLength(final String formatString, final ParsePosition
formatParsePosition) {
+ String formatTrimmed =
formatString.substring(formatParsePosition.getIndex());
+
+ int prefixLength = 0;
+ for (String prefix : new String[] {"FM", "TM"}) {
+ if (formatTrimmed.startsWith(prefix)) {
+ formatTrimmed = formatTrimmed.substring(prefix.length());
+ prefixLength += prefix.length();
+ }
+ }
+
+ for (String pattern : patterns) {
+ if (formatTrimmed.startsWith(pattern)) {
+ return prefixLength + pattern.length();
+ }
+ }
+
+ return -1;
+ }
+
+ /**
+ * Checks if the format pattern is for a numeric value.
+ *
+ * @return true if the format pattern is for a numeric value
+ */
+ protected abstract boolean isNumeric();
}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/NumberFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/NumberFormatPattern.java
index 57b4e8a428..776ed52098 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/NumberFormatPattern.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/NumberFormatPattern.java
@@ -18,6 +18,7 @@ package org.apache.calcite.util.format.postgresql;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.text.ParseException;
import java.text.ParsePosition;
import java.time.ZonedDateTime;
import java.util.Locale;
@@ -27,13 +28,61 @@ import java.util.function.Function;
* A format element that will produce a number. Numbers can have leading zeroes
* removed and can have ordinal suffixes.
*/
-public class NumberFormatPattern implements FormatPattern {
- private final String[] patterns;
+public class NumberFormatPattern extends FormatPattern {
+ private final ChronoUnitEnum chronoUnit;
+ private final long minValue;
+ private final long maxValue;
+ private final int preferredLength;
private final Function<ZonedDateTime, String> converter;
+ private final @Nullable Function<Integer, Integer> valueAdjuster;
+
+ /**
+ * Constructs a new NumberFormatPattern for the provided values.
+ *
+ * @param chronoUnit ChronoUnitEnum value that this pattern parses
+ * @param minValue minimum allowed value
+ * @param maxValue maximum allowed value
+ * @param preferredLength the number input characters that would normally be
consumed by this
+ * pattern. For example YYYY would normally consume 4
characters, but
+ * can actually consume more or less.
+ * @param converter a Function that will extract the value from a datetime
and format it
+ * @param patterns array of pattern strings
+ */
+ public NumberFormatPattern(ChronoUnitEnum chronoUnit, long minValue, long
maxValue,
+ int preferredLength, Function<ZonedDateTime, String> converter,
String... patterns) {
+ super(patterns);
+ this.chronoUnit = chronoUnit;
+ this.converter = converter;
+ this.valueAdjuster = null;
+ this.minValue = minValue;
+ this.maxValue = maxValue;
+ this.preferredLength = preferredLength;
+ }
- protected NumberFormatPattern(Function<ZonedDateTime, String> converter,
String... patterns) {
+ /**
+ * Constructs a new NumberFormatPattern for the provided values.
+ *
+ * @param chronoUnit ChronoUnitEnum value that this pattern parses
+ * @param minValue minimum allowed value
+ * @param maxValue maximum allowed value
+ * @param preferredLength the number input characters that would normally be
consumed by this
+ * pattern. For example YYYY would normally consume 4
characters, but
+ * can actually consume more or less.
+ * @param converter a Function that will extract the value from a datetime
and format it
+ * @param valueAdjuster a Function that can convert the extracted value to
the expected
+ * datetime value.
+ * @param patterns array of pattern strings
+ */
+ protected NumberFormatPattern(ChronoUnitEnum chronoUnit, int minValue,
+ int maxValue, int preferredLength, Function<ZonedDateTime, String>
converter,
+ Function<Integer, Integer> valueAdjuster, String... patterns) {
+ super(patterns);
+ this.chronoUnit = chronoUnit;
this.converter = converter;
- this.patterns = patterns;
+ this.valueAdjuster = valueAdjuster;
+ this.minValue = minValue;
+ this.maxValue = maxValue;
+ this.preferredLength = preferredLength;
}
@Override public @Nullable String convert(ParsePosition parsePosition,
String formatString,
@@ -55,7 +104,7 @@ public class NumberFormatPattern implements FormatPattern {
}
String patternToUse = null;
- for (String pattern : patterns) {
+ for (String pattern : getPatterns()) {
if (formatStringTrimmed.startsWith(pattern)) {
patternToUse = pattern;
break;
@@ -121,6 +170,12 @@ public class NumberFormatPattern implements FormatPattern {
return formattedValue;
}
+ /**
+ * Removes leading zeros from string, while preserving the negative sign if
present.
+ *
+ * @param value String to remove leading zeros from
+ * @return input string without leading zeros
+ */
protected String trimLeadingZeros(String value) {
if (value.isEmpty()) {
return value;
@@ -143,4 +198,40 @@ public class NumberFormatPattern implements FormatPattern {
return value;
}
}
+
+ @Override public ChronoUnitEnum getChronoUnit() {
+ return chronoUnit;
+ }
+
+ @Override protected int parseValue(final ParsePosition inputPosition, final
String input,
+ Locale locale, boolean haveFillMode, boolean enforceLength) throws
ParseException {
+ int endIndex = inputPosition.getIndex();
+ for (; endIndex < input.length(); endIndex++) {
+ if (input.charAt(endIndex) < '0' || input.charAt(endIndex) > '9') {
+ break;
+ } else if (enforceLength && endIndex == inputPosition.getIndex() +
preferredLength) {
+ break;
+ }
+ }
+
+ if (endIndex == inputPosition.getIndex()) {
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
+ int value = Integer.parseInt(input.substring(inputPosition.getIndex(),
endIndex));
+ if (value < minValue || value > maxValue) {
+ throw new ParseException("Parsed value outside of valid range",
inputPosition.getIndex());
+ }
+
+ if (valueAdjuster != null) {
+ value = valueAdjuster.apply(value);
+ }
+
+ inputPosition.setIndex(endIndex);
+ return value;
+ }
+
+ @Override protected boolean isNumeric() {
+ return true;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/PostgresqlDateTimeFormatter.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/PostgresqlDateTimeFormatter.java
index b5d1851fb9..978bf578ed 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/PostgresqlDateTimeFormatter.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/PostgresqlDateTimeFormatter.java
@@ -18,14 +18,22 @@ package org.apache.calcite.util.format.postgresql;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.text.ParseException;
import java.text.ParsePosition;
+import java.time.LocalDateTime;
import java.time.Month;
+import java.time.ZoneId;
+import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.time.format.TextStyle;
import java.time.temporal.ChronoField;
+import java.time.temporal.ChronoUnit;
import java.time.temporal.IsoFields;
import java.time.temporal.JulianFields;
+import java.util.HashMap;
import java.util.Locale;
+import java.util.Map;
+import java.util.Set;
/**
* Provides an implementation of toChar that matches PostgreSQL behaviour.
@@ -38,76 +46,135 @@ public class PostgresqlDateTimeFormatter {
@SuppressWarnings("TemporalAccessorGetChronoField")
private static final FormatPattern[] FORMAT_PATTERNS = new FormatPattern[] {
new NumberFormatPattern(
- dt -> {
- final int hour = dt.get(ChronoField.HOUR_OF_AMPM);
- return String.format(Locale.ROOT, "%02d", hour == 0 ? 12 : hour);
- },
- "HH12"),
- new NumberFormatPattern(
+ ChronoUnitEnum.HOURS_IN_DAY,
+ 0,
+ 23,
+ 2,
dt -> String.format(Locale.ROOT, "%02d", dt.getHour()),
"HH24"),
new NumberFormatPattern(
+ ChronoUnitEnum.HOURS_IN_HALF_DAY,
+ 1,
+ 12,
+ 2,
dt -> {
final int hour = dt.get(ChronoField.HOUR_OF_AMPM);
return String.format(Locale.ROOT, "%02d", hour == 0 ? 12 : hour);
},
- "HH"),
+ "HH12", "HH"),
new NumberFormatPattern(
+ ChronoUnitEnum.MINUTES_IN_HOUR,
+ 0,
+ 59,
+ 2,
dt -> String.format(Locale.ROOT, "%02d", dt.getMinute()),
"MI"),
new NumberFormatPattern(
+ ChronoUnitEnum.SECONDS_IN_DAY,
+ 0,
+ 24 * 60 * 60 - 1,
+ 5,
dt -> Integer.toString(dt.get(ChronoField.SECOND_OF_DAY)),
"SSSSS", "SSSS"),
new NumberFormatPattern(
+ ChronoUnitEnum.SECONDS_IN_MINUTE,
+ 0,
+ 59,
+ 2,
dt -> String.format(Locale.ROOT, "%02d", dt.getSecond()),
"SS"),
new NumberFormatPattern(
+ ChronoUnitEnum.MILLIS,
+ 0,
+ 999,
+ 3,
dt -> String.format(Locale.ROOT, "%03d",
dt.get(ChronoField.MILLI_OF_SECOND)),
"MS"),
new NumberFormatPattern(
+ ChronoUnitEnum.MICROS,
+ 0,
+ 999_999,
+ 6,
dt -> String.format(Locale.ROOT, "%06d",
dt.get(ChronoField.MICRO_OF_SECOND)),
"US"),
new NumberFormatPattern(
+ ChronoUnitEnum.TENTHS_OF_SECOND,
+ 0,
+ 9,
+ 1,
dt -> Integer.toString(dt.get(ChronoField.MILLI_OF_SECOND) / 100),
"FF1"),
new NumberFormatPattern(
+ ChronoUnitEnum.HUNDREDTHS_OF_SECOND,
+ 0,
+ 99,
+ 2,
dt -> String.format(Locale.ROOT, "%02d",
dt.get(ChronoField.MILLI_OF_SECOND) / 10),
"FF2"),
new NumberFormatPattern(
+ ChronoUnitEnum.THOUSANDTHS_OF_SECOND,
+ 0,
+ 999,
+ 3,
dt -> String.format(Locale.ROOT, "%03d",
dt.get(ChronoField.MILLI_OF_SECOND)),
"FF3"),
new NumberFormatPattern(
+ ChronoUnitEnum.TENTHS_OF_MS,
+ 0,
+ 9_999,
+ 4,
dt -> String.format(Locale.ROOT, "%04d",
dt.get(ChronoField.MICRO_OF_SECOND) / 100),
"FF4"),
new NumberFormatPattern(
+ ChronoUnitEnum.HUNDREDTHS_OF_MS,
+ 0,
+ 99_999,
+ 5,
dt -> String.format(Locale.ROOT, "%05d",
dt.get(ChronoField.MICRO_OF_SECOND) / 10),
"FF5"),
new NumberFormatPattern(
+ ChronoUnitEnum.THOUSANDTHS_OF_MS,
+ 0,
+ 999_999,
+ 6,
dt -> String.format(Locale.ROOT, "%06d",
dt.get(ChronoField.MICRO_OF_SECOND)),
"FF6"),
- new EnumStringFormatPattern(ChronoField.AMPM_OF_DAY, "AM", "PM"),
- new EnumStringFormatPattern(ChronoField.AMPM_OF_DAY, "am", "pm"),
- new EnumStringFormatPattern(ChronoField.AMPM_OF_DAY, "A.M.", "P.M."),
- new EnumStringFormatPattern(ChronoField.AMPM_OF_DAY, "a.m.", "p.m."),
- new NumberFormatPattern(dt -> {
- final String formattedYear = String.format(Locale.ROOT, "%0,4d",
dt.getYear());
- if (formattedYear.length() == 4 && formattedYear.charAt(0) == '0') {
- return "0," + formattedYear.substring(1);
- } else {
- return formattedYear;
- }
- }, "Y,YYY") {
- @Override protected String trimLeadingZeros(String value) {
- return value;
- }
- },
- new NumberFormatPattern(
+ new EnumStringFormatPattern(
+ ChronoUnitEnum.HALF_DAYS,
+ ChronoField.AMPM_OF_DAY,
+ "AM", "PM"),
+ new EnumStringFormatPattern(
+ ChronoUnitEnum.HALF_DAYS,
+ ChronoField.AMPM_OF_DAY,
+ "am", "pm"),
+ new EnumStringFormatPattern(
+ ChronoUnitEnum.HALF_DAYS,
+ ChronoField.AMPM_OF_DAY,
+ "A.M.", "P.M."),
+ new EnumStringFormatPattern(
+ ChronoUnitEnum.HALF_DAYS,
+ ChronoField.AMPM_OF_DAY,
+ "a.m.", "p.m."),
+ new YearWithCommasFormatPattern(),
+ new NumberFormatPattern(
+ ChronoUnitEnum.YEARS,
+ 0,
+ Integer.MAX_VALUE,
+ 4,
dt -> String.format(Locale.ROOT, "%04d", dt.getYear()),
"YYYY"),
new NumberFormatPattern(
+ ChronoUnitEnum.YEARS_ISO_8601,
+ 0,
+ Integer.MAX_VALUE,
+ 4,
dt -> Integer.toString(dt.get(IsoFields.WEEK_BASED_YEAR)),
"IYYY"),
new NumberFormatPattern(
+ ChronoUnitEnum.YEARS_IN_MILLENIA_ISO_8601,
+ 0,
+ Integer.MAX_VALUE,
+ 3,
dt -> {
final String yearString =
String.format(Locale.ROOT, "%03d",
dt.get(IsoFields.WEEK_BASED_YEAR));
@@ -115,6 +182,10 @@ public class PostgresqlDateTimeFormatter {
},
"IYY"),
new NumberFormatPattern(
+ ChronoUnitEnum.YEARS_IN_CENTURY_ISO_8601,
+ 0,
+ Integer.MAX_VALUE,
+ 2,
dt -> {
final String yearString =
String.format(Locale.ROOT, "%02d",
dt.get(IsoFields.WEEK_BASED_YEAR));
@@ -122,6 +193,10 @@ public class PostgresqlDateTimeFormatter {
},
"IY"),
new NumberFormatPattern(
+ ChronoUnitEnum.YEARS_IN_MILLENIA,
+ 0,
+ Integer.MAX_VALUE,
+ 3,
dt -> {
final String formattedYear = String.format(Locale.ROOT, "%03d",
dt.getYear());
if (formattedYear.length() > 3) {
@@ -132,6 +207,10 @@ public class PostgresqlDateTimeFormatter {
},
"YYY"),
new NumberFormatPattern(
+ ChronoUnitEnum.YEARS_IN_CENTURY,
+ 0,
+ Integer.MAX_VALUE,
+ 2,
dt -> {
final String formattedYear = String.format(Locale.ROOT, "%02d",
dt.getYear());
if (formattedYear.length() > 2) {
@@ -142,6 +221,10 @@ public class PostgresqlDateTimeFormatter {
},
"YY"),
new NumberFormatPattern(
+ ChronoUnitEnum.YEARS_IN_CENTURY,
+ 0,
+ Integer.MAX_VALUE,
+ 1,
dt -> {
final String formattedYear = Integer.toString(dt.getYear());
if (formattedYear.length() > 1) {
@@ -152,9 +235,17 @@ public class PostgresqlDateTimeFormatter {
},
"Y"),
new NumberFormatPattern(
+ ChronoUnitEnum.WEEKS_IN_YEAR_ISO_8601,
+ 1,
+ 53,
+ 2,
dt -> String.format(Locale.ROOT, "%02d",
dt.get(IsoFields.WEEK_OF_WEEK_BASED_YEAR)),
"IW"),
new NumberFormatPattern(
+ ChronoUnitEnum.DAYS_IN_YEAR_ISO_8601,
+ 0,
+ 371,
+ 3,
dt -> {
final Month month = dt.getMonth();
final int dayOfMonth = dt.getDayOfMonth();
@@ -175,18 +266,26 @@ public class PostgresqlDateTimeFormatter {
},
"IDDD"),
new NumberFormatPattern(
+ ChronoUnitEnum.DAYS_IN_WEEK,
+ 1,
+ 7,
+ 1,
dt -> Integer.toString(dt.getDayOfWeek().getValue()),
"ID"),
new NumberFormatPattern(
+ ChronoUnitEnum.YEARS_IN_CENTURY_ISO_8601,
+ 0,
+ Integer.MAX_VALUE,
+ 1,
dt -> {
final String yearString =
Integer.toString(dt.get(IsoFields.WEEK_BASED_YEAR));
return yearString.substring(yearString.length() - 1);
},
"I"),
- new EnumStringFormatPattern(ChronoField.ERA, "BC", "AD"),
- new EnumStringFormatPattern(ChronoField.ERA, "bc", "ad"),
- new EnumStringFormatPattern(ChronoField.ERA, "B.C.", "A.D."),
- new EnumStringFormatPattern(ChronoField.ERA, "b.c.", "a.d."),
+ new EnumStringFormatPattern(ChronoUnitEnum.ERAS, ChronoField.ERA, "BC",
"AD"),
+ new EnumStringFormatPattern(ChronoUnitEnum.ERAS, ChronoField.ERA, "bc",
"ad"),
+ new EnumStringFormatPattern(ChronoUnitEnum.ERAS, ChronoField.ERA,
"B.C.", "A.D."),
+ new EnumStringFormatPattern(ChronoUnitEnum.ERAS, ChronoField.ERA,
"b.c.", "a.d."),
DateStringFormatPattern.forMonth(TextStyle.FULL,
CapitalizationEnum.ALL_UPPER, "MONTH"),
DateStringFormatPattern.forMonth(TextStyle.FULL,
CapitalizationEnum.CAPITALIZED, "Month"),
DateStringFormatPattern.forMonth(TextStyle.FULL,
CapitalizationEnum.ALL_LOWER, "month"),
@@ -194,6 +293,10 @@ public class PostgresqlDateTimeFormatter {
DateStringFormatPattern.forMonth(TextStyle.SHORT,
CapitalizationEnum.CAPITALIZED, "Mon"),
DateStringFormatPattern.forMonth(TextStyle.SHORT,
CapitalizationEnum.ALL_LOWER, "mon"),
new NumberFormatPattern(
+ ChronoUnitEnum.MONTHS_IN_YEAR,
+ 1,
+ 12,
+ 2,
dt -> String.format(Locale.ROOT, "%02d", dt.getMonthValue()),
"MM"),
DateStringFormatPattern.forDayOfWeek(TextStyle.FULL,
CapitalizationEnum.ALL_UPPER, "DAY"),
@@ -203,12 +306,24 @@ public class PostgresqlDateTimeFormatter {
DateStringFormatPattern.forDayOfWeek(TextStyle.SHORT,
CapitalizationEnum.CAPITALIZED, "Dy"),
DateStringFormatPattern.forDayOfWeek(TextStyle.SHORT,
CapitalizationEnum.ALL_LOWER, "dy"),
new NumberFormatPattern(
+ ChronoUnitEnum.DAYS_IN_YEAR,
+ 1,
+ 366,
+ 3,
dt -> String.format(Locale.ROOT, "%03d", dt.getDayOfYear()),
"DDD"),
new NumberFormatPattern(
+ ChronoUnitEnum.DAYS_IN_MONTH,
+ 1,
+ 31,
+ 2,
dt -> String.format(Locale.ROOT, "%02d", dt.getDayOfMonth()),
"DD"),
new NumberFormatPattern(
+ ChronoUnitEnum.DAYS_IN_WEEK,
+ 1,
+ 7,
+ 1,
dt -> {
int dayOfWeek = dt.getDayOfWeek().getValue() + 1;
if (dayOfWeek == 8) {
@@ -216,14 +331,27 @@ public class PostgresqlDateTimeFormatter {
}
return Integer.toString(dayOfWeek);
},
+ v -> v < 7 ? v + 1 : 1,
"D"),
new NumberFormatPattern(
+ ChronoUnitEnum.WEEKS_IN_YEAR,
+ 1,
+ 53,
+ 2,
dt -> Integer.toString((int) Math.ceil((double) dt.getDayOfYear() /
7)),
"WW"),
new NumberFormatPattern(
+ ChronoUnitEnum.WEEKS_IN_MONTH,
+ 1,
+ 5,
+ 1,
dt -> Integer.toString((int) Math.ceil((double) dt.getDayOfMonth() /
7)),
"W"),
new NumberFormatPattern(
+ ChronoUnitEnum.CENTURIES,
+ 0,
+ Integer.MAX_VALUE,
+ 2,
dt -> {
if (dt.get(ChronoField.ERA) == 0) {
return String.format(Locale.ROOT, "-%02d", Math.abs(dt.getYear()
/ 100 - 1));
@@ -233,6 +361,10 @@ public class PostgresqlDateTimeFormatter {
},
"CC"),
new NumberFormatPattern(
+ ChronoUnitEnum.DAYS_JULIAN,
+ 0,
+ Integer.MAX_VALUE,
+ 1,
dt -> {
final long julianDays = dt.getLong(JulianFields.JULIAN_DAY);
if (dt.getYear() < 0) {
@@ -243,16 +375,54 @@ public class PostgresqlDateTimeFormatter {
},
"J"),
new NumberFormatPattern(
+ ChronoUnitEnum.MONTHS_IN_YEAR,
+ 1,
+ 4,
+ 1,
dt -> Integer.toString(dt.get(IsoFields.QUARTER_OF_YEAR)),
"Q"),
new RomanNumeralMonthFormatPattern(true, "RM"),
new RomanNumeralMonthFormatPattern(false, "rm"),
new TimeZoneHoursFormatPattern(),
new TimeZoneMinutesFormatPattern(),
- new TimeZoneFormatPattern(true, "TZ"),
- new TimeZoneFormatPattern(false, "tz"),
- new StringFormatPattern("OF") {
- @Override String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
+ new StringFormatPattern(ChronoUnitEnum.TIMEZONE_MINUTES, "TZ") {
+ @Override protected int parseValue(ParsePosition inputPosition, String
input,
+ Locale locale, boolean haveFillMode, boolean enforceLength) throws
ParseException {
+ throw new ParseException("TZ pattern is not supported in parsing
datetime values",
+ inputPosition.getIndex());
+ }
+
+ @Override protected String dateTimeToString(ZonedDateTime dateTime,
boolean haveFillMode,
+ @Nullable String suffix, Locale locale) {
+ return String.format(
+ locale,
+ "%3s",
+ dateTime.getZone().getDisplayName(TextStyle.SHORT,
locale).toUpperCase(locale));
+ }
+ },
+ new StringFormatPattern(ChronoUnitEnum.TIMEZONE_MINUTES, "tz") {
+ @Override protected int parseValue(ParsePosition inputPosition, String
input, Locale locale,
+ boolean haveFillMode, boolean enforceLength) throws ParseException
{
+ throw new ParseException("tz pattern is not supported in parsing
datetime values",
+ inputPosition.getIndex());
+ }
+
+ @Override protected String dateTimeToString(ZonedDateTime dateTime,
boolean haveFillMode,
+ @Nullable String suffix, Locale locale) {
+ return String.format(
+ locale,
+ "%3s",
+ dateTime.getZone().getDisplayName(TextStyle.SHORT,
locale).toLowerCase(locale));
+ }
+ },
+ new StringFormatPattern(ChronoUnitEnum.TIMEZONE_MINUTES, "OF") {
+ @Override protected int parseValue(ParsePosition inputPosition, String
input, Locale locale,
+ boolean haveFillMode, boolean enforceLength) throws ParseException
{
+ throw new ParseException("OF pattern is not supported in parsing
datetime values",
+ inputPosition.getIndex());
+ }
+
+ @Override protected String dateTimeToString(ZonedDateTime dateTime,
boolean haveFillMode,
@Nullable String suffix, Locale locale) {
final int hours = dateTime.getOffset().get(ChronoField.HOUR_OF_DAY);
final int minutes =
dateTime.getOffset().get(ChronoField.MINUTE_OF_HOUR);
@@ -308,4 +478,371 @@ public class PostgresqlDateTimeFormatter {
return sb.toString();
}
+
+ public static ZonedDateTime toTimestamp(String input, String formatString,
ZoneId zoneId)
+ throws Exception {
+ final ParsePosition inputParsePosition = new ParsePosition(0);
+ final ParsePosition formatParsePosition = new ParsePosition(0);
+ final Map<ChronoUnitEnum, Long> dateTimeParts = new HashMap<>();
+
+ while (inputParsePosition.getIndex() < input.length()
+ && formatParsePosition.getIndex() < formatString.length()) {
+ if (input.charAt(inputParsePosition.getIndex()) == ' '
+ && formatString.charAt(formatParsePosition.getIndex()) == ' ') {
+ inputParsePosition.setIndex(inputParsePosition.getIndex() + 1);
+ formatParsePosition.setIndex(formatParsePosition.getIndex() + 1);
+ continue;
+ } else if (input.charAt(inputParsePosition.getIndex()) == ' ') {
+ inputParsePosition.setIndex(inputParsePosition.getIndex() + 1);
+ continue;
+ } else if (formatString.charAt(formatParsePosition.getIndex()) == ' ') {
+ formatParsePosition.setIndex(formatParsePosition.getIndex() + 1);
+ continue;
+ }
+
+ long parsedValue = 0L;
+ FormatPattern matchedPattern = null;
+
+ for (FormatPattern formatPattern : FORMAT_PATTERNS) {
+ int matchedPatternLength =
+ formatPattern.matchedPatternLength(formatString,
formatParsePosition);
+ if (matchedPatternLength > 0) {
+ final FormatPattern nextPattern =
+ getNextPattern(formatString, formatParsePosition.getIndex() +
matchedPatternLength);
+
+ final boolean enforceLength = nextPattern != null &&
formatPattern.isNumeric()
+ && nextPattern.isNumeric();
+
+ parsedValue =
+ formatPattern.parse(inputParsePosition, input,
formatParsePosition, formatString,
+ enforceLength);
+ matchedPattern = formatPattern;
+ break;
+ }
+ }
+
+ if (matchedPattern == null) {
+ if
(Character.isLetter(formatString.charAt(formatParsePosition.getIndex()))) {
+ throw new IllegalArgumentException();
+ } else {
+ inputParsePosition.setIndex(inputParsePosition.getIndex() + 1);
+ formatParsePosition.setIndex(formatParsePosition.getIndex() + 1);
+ }
+ } else {
+ final Set<ChronoUnitEnum> units = dateTimeParts.keySet();
+ if (!matchedPattern.getChronoUnit().isCompatible(units)) {
+ throw new IllegalArgumentException();
+ }
+
+ dateTimeParts.put(matchedPattern.getChronoUnit(), parsedValue);
+ }
+ }
+
+ return constructDateTimeFromParts(dateTimeParts, zoneId);
+ }
+
+ private static @Nullable FormatPattern getNextPattern(String formatString,
+ int formatPosition) {
+ String formatTrimmed = formatString.substring(formatPosition);
+ for (String prefix : new String[] {"FM", "TM"}) {
+ if (formatTrimmed.startsWith(prefix)) {
+ formatTrimmed = formatString.substring(prefix.length());
+ }
+ }
+
+ for (FormatPattern pattern : FORMAT_PATTERNS) {
+ for (String patternString : pattern.getPatterns()) {
+ if (formatTrimmed.startsWith(patternString)) {
+ return pattern;
+ }
+ }
+ }
+
+ return null;
+ }
+
+ private static ZonedDateTime constructDateTimeFromParts(Map<ChronoUnitEnum,
Long> dateParts,
+ ZoneId zoneId) {
+ LocalDateTime constructedDateTime =
LocalDateTime.now(ZoneId.systemDefault())
+ .truncatedTo(ChronoUnit.DAYS);
+
+ DateCalendarEnum calendar = DateCalendarEnum.NONE;
+ boolean containsCentury = false;
+ for (ChronoUnitEnum unit : dateParts.keySet()) {
+ if (unit.getCalendars().size() == 1) {
+ DateCalendarEnum unitCalendar = unit.getCalendars().iterator().next();
+ if (unitCalendar != DateCalendarEnum.NONE) {
+ calendar = unitCalendar;
+ break;
+ }
+ } else if (unit == ChronoUnitEnum.CENTURIES) {
+ containsCentury = true;
+ }
+ }
+
+ if (calendar == DateCalendarEnum.NONE && containsCentury) {
+ calendar = DateCalendarEnum.GREGORIAN;
+ }
+
+ switch (calendar) {
+ case NONE:
+ constructedDateTime = constructedDateTime
+ .withYear(1)
+ .withMonth(1)
+ .withDayOfMonth(1);
+ break;
+ case GREGORIAN:
+ constructedDateTime = updateWithGregorianFields(constructedDateTime,
dateParts);
+ break;
+ case ISO_8601:
+ constructedDateTime = updateWithIso8601Fields(constructedDateTime,
dateParts);
+ break;
+ case JULIAN:
+ final Long julianDays = dateParts.get(ChronoUnitEnum.DAYS_JULIAN);
+ if (julianDays != null) {
+ constructedDateTime =
constructedDateTime.with(JulianFields.JULIAN_DAY, julianDays);
+ }
+ break;
+ }
+
+ constructedDateTime = updateWithTimeFields(constructedDateTime, dateParts);
+
+ if (dateParts.containsKey(ChronoUnitEnum.TIMEZONE_HOURS)
+ || dateParts.containsKey(ChronoUnitEnum.TIMEZONE_MINUTES)) {
+ final int hours = dateParts.getOrDefault(ChronoUnitEnum.TIMEZONE_HOURS,
0L)
+ .intValue();
+ final int minutes =
dateParts.getOrDefault(ChronoUnitEnum.TIMEZONE_MINUTES, 0L)
+ .intValue();
+
+ return ZonedDateTime.of(constructedDateTime,
ZoneOffset.ofHoursMinutes(hours, minutes))
+ .withZoneSameInstant(zoneId);
+ }
+
+ return ZonedDateTime.of(constructedDateTime, zoneId);
+ }
+
+ private static LocalDateTime updateWithGregorianFields(LocalDateTime
dateTime,
+ Map<ChronoUnitEnum, Long> dateParts) {
+ LocalDateTime updatedDateTime =
dateTime.withYear(getGregorianYear(dateParts)).withDayOfYear(1);
+
+ if (dateParts.containsKey(ChronoUnitEnum.MONTHS_IN_YEAR)) {
+ updatedDateTime =
+
updatedDateTime.withMonth(dateParts.get(ChronoUnitEnum.MONTHS_IN_YEAR).intValue());
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.DAYS_IN_MONTH)) {
+ updatedDateTime =
+
updatedDateTime.withDayOfMonth(dateParts.get(ChronoUnitEnum.DAYS_IN_MONTH).intValue());
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.WEEKS_IN_MONTH)) {
+ updatedDateTime =
+ updatedDateTime.withDayOfMonth(
+ dateParts.get(ChronoUnitEnum.WEEKS_IN_MONTH).intValue() * 7 - 6);
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.WEEKS_IN_YEAR)) {
+ updatedDateTime =
+ updatedDateTime.withDayOfYear(
+ dateParts.get(ChronoUnitEnum.WEEKS_IN_YEAR).intValue() * 7 - 6);
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.DAYS_IN_YEAR)) {
+ updatedDateTime =
+
updatedDateTime.withDayOfYear(dateParts.get(ChronoUnitEnum.DAYS_IN_YEAR).intValue());
+ }
+
+ return updatedDateTime;
+ }
+
+ private static int getGregorianYear(Map<ChronoUnitEnum, Long> dateParts) {
+ int year =
+ getYear(
+ dateParts.get(ChronoUnitEnum.ERAS),
+ dateParts.get(ChronoUnitEnum.YEARS),
+ dateParts.get(ChronoUnitEnum.CENTURIES),
+ dateParts.get(ChronoUnitEnum.YEARS_IN_MILLENIA),
+ dateParts.get(ChronoUnitEnum.YEARS_IN_CENTURY));
+ return year == 0 ? 1 : year;
+ }
+
+ private static LocalDateTime updateWithIso8601Fields(LocalDateTime dateTime,
+ Map<ChronoUnitEnum, Long> dateParts) {
+ final int year = getIso8601Year(dateParts);
+
+ if (!dateParts.containsKey(ChronoUnitEnum.WEEKS_IN_YEAR_ISO_8601)
+ && !dateParts.containsKey(ChronoUnitEnum.DAYS_IN_YEAR_ISO_8601)) {
+ return dateTime.withYear(year).withDayOfYear(1);
+ }
+
+ LocalDateTime updatedDateTime = dateTime
+ .with(ChronoField.DAY_OF_WEEK, 1)
+ .with(IsoFields.WEEK_BASED_YEAR, year)
+ .with(IsoFields.WEEK_OF_WEEK_BASED_YEAR, 1);
+
+ if (dateParts.containsKey(ChronoUnitEnum.WEEKS_IN_YEAR_ISO_8601)) {
+ updatedDateTime =
+ updatedDateTime.with(IsoFields.WEEK_OF_WEEK_BASED_YEAR,
+ dateParts.get(ChronoUnitEnum.WEEKS_IN_YEAR_ISO_8601));
+
+ if (dateParts.containsKey(ChronoUnitEnum.DAYS_IN_WEEK)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.DAY_OF_WEEK,
+ dateParts.get(ChronoUnitEnum.DAYS_IN_WEEK));
+ }
+ } else if (dateParts.containsKey(ChronoUnitEnum.DAYS_IN_YEAR_ISO_8601)) {
+ updatedDateTime =
+
updatedDateTime.plusDays(dateParts.get(ChronoUnitEnum.DAYS_IN_YEAR_ISO_8601) -
1);
+ }
+
+ return updatedDateTime;
+ }
+
+ private static int getIso8601Year(Map<ChronoUnitEnum, Long> dateParts) {
+ int year =
+ getYear(
+ dateParts.get(ChronoUnitEnum.ERAS),
+ dateParts.get(ChronoUnitEnum.YEARS_ISO_8601),
+ dateParts.get(ChronoUnitEnum.CENTURIES),
+ dateParts.get(ChronoUnitEnum.YEARS_IN_MILLENIA_ISO_8601),
+ dateParts.get(ChronoUnitEnum.YEARS_IN_CENTURY_ISO_8601));
+ return year == 0 ? 1 : year;
+ }
+
+ private static int getYear(@Nullable Long era, @Nullable Long years,
+ @Nullable Long centuries, @Nullable Long yearsInMillenia,
+ @Nullable Long yearsInCentury) {
+ int yearSign = 1;
+ if (era != null) {
+ if (era == 0) {
+ yearSign = -1;
+ }
+ }
+
+ if (yearsInMillenia != null) {
+ int year = yearsInMillenia.intValue();
+ if (year < 520) {
+ year += 2000;
+ } else {
+ year += 1000;
+ }
+
+ return yearSign * year;
+ }
+
+ if (centuries != null) {
+ int year = 100 * (centuries.intValue() - 1);
+
+ if (yearsInCentury != null) {
+ year += yearsInCentury.intValue();
+ } else {
+ year += 1;
+ }
+
+ return yearSign * year;
+ }
+
+ if (years != null) {
+ return yearSign * years.intValue();
+ }
+
+ if (yearsInCentury != null) {
+ int year = yearsInCentury.intValue();
+ if (year < 70) {
+ year += 2000;
+ } else {
+ year += 1900;
+ }
+
+ return yearSign * year;
+ }
+
+ return yearSign;
+ }
+
+ private static LocalDateTime updateWithTimeFields(LocalDateTime dateTime,
+ Map<ChronoUnitEnum, Long> dateParts) {
+ LocalDateTime updatedDateTime = dateTime;
+
+ if (dateParts.containsKey(ChronoUnitEnum.HOURS_IN_DAY)) {
+ updatedDateTime =
+
updatedDateTime.withHour(dateParts.get(ChronoUnitEnum.HOURS_IN_DAY).intValue());
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.HALF_DAYS)
+ && dateParts.containsKey(ChronoUnitEnum.HOURS_IN_HALF_DAY)) {
+ updatedDateTime =
+
updatedDateTime.withHour(dateParts.get(ChronoUnitEnum.HALF_DAYS).intValue() * 12
+ + dateParts.get(ChronoUnitEnum.HOURS_IN_HALF_DAY).intValue());
+ } else if (dateParts.containsKey(ChronoUnitEnum.HOURS_IN_HALF_DAY)) {
+ updatedDateTime =
+
updatedDateTime.withHour(dateParts.get(ChronoUnitEnum.HOURS_IN_HALF_DAY).intValue());
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.MINUTES_IN_HOUR)) {
+ updatedDateTime =
+
updatedDateTime.withMinute(dateParts.get(ChronoUnitEnum.MINUTES_IN_HOUR).intValue());
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.SECONDS_IN_DAY)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.SECOND_OF_DAY,
+ dateParts.get(ChronoUnitEnum.SECONDS_IN_DAY));
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.SECONDS_IN_MINUTE)) {
+ updatedDateTime =
+
updatedDateTime.withSecond(dateParts.get(ChronoUnitEnum.SECONDS_IN_MINUTE).intValue());
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.MILLIS)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.MILLI_OF_SECOND,
+ dateParts.get(ChronoUnitEnum.MILLIS));
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.MICROS)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.MICRO_OF_SECOND,
+ dateParts.get(ChronoUnitEnum.MICROS));
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.TENTHS_OF_SECOND)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.MILLI_OF_SECOND,
+ 100 * dateParts.get(ChronoUnitEnum.TENTHS_OF_SECOND));
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.HUNDREDTHS_OF_SECOND)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.MILLI_OF_SECOND,
+ 10 * dateParts.get(ChronoUnitEnum.HUNDREDTHS_OF_SECOND));
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.THOUSANDTHS_OF_SECOND)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.MILLI_OF_SECOND,
+ dateParts.get(ChronoUnitEnum.THOUSANDTHS_OF_SECOND));
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.TENTHS_OF_MS)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.MICRO_OF_SECOND,
+ 100 * dateParts.get(ChronoUnitEnum.TENTHS_OF_MS));
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.HUNDREDTHS_OF_MS)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.MICRO_OF_SECOND,
+ 10 * dateParts.get(ChronoUnitEnum.HUNDREDTHS_OF_MS));
+ }
+
+ if (dateParts.containsKey(ChronoUnitEnum.THOUSANDTHS_OF_MS)) {
+ updatedDateTime =
+ updatedDateTime.with(ChronoField.MICRO_OF_SECOND,
+ dateParts.get(ChronoUnitEnum.THOUSANDTHS_OF_MS));
+ }
+
+ return updatedDateTime;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/RomanNumeralMonthFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/RomanNumeralMonthFormatPattern.java
index f2458896aa..6e18215ce5 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/RomanNumeralMonthFormatPattern.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/RomanNumeralMonthFormatPattern.java
@@ -18,6 +18,8 @@ package org.apache.calcite.util.format.postgresql;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.text.ParseException;
+import java.text.ParsePosition;
import java.time.ZonedDateTime;
import java.util.Locale;
@@ -28,11 +30,11 @@ public class RomanNumeralMonthFormatPattern extends
StringFormatPattern {
private final boolean upperCase;
public RomanNumeralMonthFormatPattern(boolean upperCase, String... patterns)
{
- super(patterns);
+ super(ChronoUnitEnum.MONTHS_IN_YEAR, patterns);
this.upperCase = upperCase;
}
- @Override String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
+ @Override protected String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
@Nullable String suffix, Locale locale) {
final String romanNumeral;
@@ -81,4 +83,49 @@ public class RomanNumeralMonthFormatPattern extends
StringFormatPattern {
return romanNumeral.toLowerCase(locale);
}
}
+
+ @Override protected int parseValue(ParsePosition inputPosition, String
input, Locale locale,
+ boolean haveFillMode, boolean enforceLength) throws ParseException {
+ final String inputTrimmed = input.substring(inputPosition.getIndex());
+
+ if (inputTrimmed.startsWith(upperCase ? "III" : "iii")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 3);
+ return 3;
+ } else if (inputTrimmed.startsWith(upperCase ? "II" : "ii")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 2);
+ return 2;
+ } else if (inputTrimmed.startsWith(upperCase ? "IV" : "iv")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 2);
+ return 4;
+ } else if (inputTrimmed.startsWith(upperCase ? "IX" : "ix")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 2);
+ return 9;
+ } else if (inputTrimmed.startsWith(upperCase ? "I" : "i")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 1);
+ return 1;
+ } else if (inputTrimmed.startsWith(upperCase ? "VIII" : "viii")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 4);
+ return 8;
+ } else if (inputTrimmed.startsWith(upperCase ? "VII" : "vii")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 3);
+ return 7;
+ } else if (inputTrimmed.startsWith(upperCase ? "VI" : "vi")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 2);
+ return 6;
+ } else if (inputTrimmed.startsWith(upperCase ? "V" : "v")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 1);
+ return 5;
+ } else if (inputTrimmed.startsWith(upperCase ? "XII" : "xii")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 3);
+ return 12;
+ } else if (inputTrimmed.startsWith(upperCase ? "XI" : "xi")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 2);
+ return 11;
+ } else if (inputTrimmed.startsWith(upperCase ? "X" : "x")) {
+ inputPosition.setIndex(inputPosition.getIndex() + 1);
+ return 10;
+ }
+
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/StringFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/StringFormatPattern.java
index 4162c4deb8..d4ab0d3d90 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/StringFormatPattern.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/StringFormatPattern.java
@@ -26,11 +26,19 @@ import java.util.Locale;
* A format element that will produce a string. The "FM" prefix and "TH"/"th"
suffixes
* will be silently consumed when the pattern matches.
*/
-public abstract class StringFormatPattern implements FormatPattern {
- private final String[] patterns;
+public abstract class StringFormatPattern extends FormatPattern {
+ private final ChronoUnitEnum chronoUnit;
- protected StringFormatPattern(String... patterns) {
- this.patterns = patterns;
+ /**
+ * Constructs a new StringFormatPattern for the provide list of pattern
strings and
+ * ChronoUnitEnum value. Child classes must use this constructor.
+ *
+ * @param chronoUnit ChronoUnitEnum value that this pattern parses
+ * @param patterns array of pattern strings
+ */
+ protected StringFormatPattern(ChronoUnitEnum chronoUnit, String... patterns)
{
+ super(patterns);
+ this.chronoUnit = chronoUnit;
}
@Override public @Nullable String convert(ParsePosition parsePosition,
String formatString,
@@ -52,7 +60,7 @@ public abstract class StringFormatPattern implements
FormatPattern {
}
String patternToUse = null;
- for (String pattern : patterns) {
+ for (String pattern : getPatterns()) {
if (formatStringTrimmed.startsWith(pattern)) {
patternToUse = pattern;
break;
@@ -81,6 +89,24 @@ public abstract class StringFormatPattern implements
FormatPattern {
haveTranslationMode ? Locale.getDefault() : Locale.US);
}
- abstract String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
+ @Override public ChronoUnitEnum getChronoUnit() {
+ return chronoUnit;
+ }
+
+ /**
+ * Extracts the datetime component from the provided datetime and formats
it. This may
+ * also involve translation to the provided locale.
+ *
+ * @param dateTime extract the datetime component from here
+ * @param haveFillMode is fill mode enabled
+ * @param suffix suffix modifier if any (TH or th)
+ * @param locale locale to translate to
+ * @return formatted string representation of datetime component
+ */
+ protected abstract String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
@Nullable String suffix, Locale locale);
+
+ @Override protected boolean isNumeric() {
+ return false;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneFormatPattern.java
deleted file mode 100644
index 0193417820..0000000000
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneFormatPattern.java
+++ /dev/null
@@ -1,46 +0,0 @@
-/*
- * 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.postgresql;
-
-import org.checkerframework.checker.nullness.qual.Nullable;
-
-import java.time.ZonedDateTime;
-import java.time.format.TextStyle;
-import java.util.Locale;
-
-/**
- * Able to parse timezone codes from string and to get the timezone from a
datetime.
- * Timezone codes are 3 letters, such as PST or UTC.
- */
-public class TimeZoneFormatPattern extends StringFormatPattern {
- final boolean isUpperCase;
-
- public TimeZoneFormatPattern(boolean isUpperCase, String... patterns) {
- super(patterns);
- this.isUpperCase = isUpperCase;
- }
-
- @Override String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
- @Nullable String suffix, Locale locale) {
-
- final String zoneCode = dateTime.getZone().getDisplayName(TextStyle.SHORT,
locale);
- return String.format(
- locale,
- "%3s",
- isUpperCase ? zoneCode.toUpperCase(locale) :
zoneCode.toLowerCase(locale));
- }
-}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneHoursFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneHoursFormatPattern.java
index 5c07559730..2b02d2e837 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneHoursFormatPattern.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneHoursFormatPattern.java
@@ -18,6 +18,8 @@ package org.apache.calcite.util.format.postgresql;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.text.ParseException;
+import java.text.ParsePosition;
import java.time.ZonedDateTime;
import java.time.temporal.ChronoField;
import java.util.Locale;
@@ -29,14 +31,58 @@ import java.util.Locale;
*/
public class TimeZoneHoursFormatPattern extends StringFormatPattern {
public TimeZoneHoursFormatPattern() {
- super("TZH");
+ super(ChronoUnitEnum.TIMEZONE_HOURS, "TZH");
}
- @Override String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
+ @Override protected int parseValue(final ParsePosition inputPosition, final
String input,
+ final Locale locale, final boolean haveFillMode, boolean enforceLength)
+ throws ParseException {
+
+ int inputOffset = inputPosition.getIndex();
+ String inputTrimmed = input.substring(inputOffset);
+
+ boolean isPositive = true;
+ if (inputTrimmed.charAt(0) == '-') {
+ isPositive = false;
+ } else if (inputTrimmed.charAt(0) != '+') {
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
+ inputOffset++;
+ inputTrimmed = input.substring(inputOffset);
+
+ if (!Character.isDigit(inputTrimmed.charAt(0))) {
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
+ int endIndex = inputOffset + 1;
+ if (endIndex > input.length() ||
!Character.isDigit(input.charAt(inputOffset))) {
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
+ if (endIndex < input.length() &&
Character.isDigit(input.charAt(endIndex))) {
+ endIndex++;
+ }
+
+ int timezoneHours = Integer.parseInt(input.substring(inputOffset,
endIndex));
+
+ if (timezoneHours > 15) {
+ throw new ParseException("Value is outside of valid range",
inputPosition.getIndex());
+ }
+
+ inputPosition.setIndex(endIndex);
+ return isPositive ? timezoneHours : -1 * timezoneHours;
+ }
+
+ @Override protected String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
@Nullable String suffix, Locale locale) {
return String.format(
Locale.ROOT,
"%+02d",
dateTime.getOffset().get(ChronoField.OFFSET_SECONDS) / 3600);
}
+
+ @Override protected boolean isNumeric() {
+ return true;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneMinutesFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneMinutesFormatPattern.java
index f8d26426a0..18a28e7ad8 100644
---
a/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneMinutesFormatPattern.java
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/TimeZoneMinutesFormatPattern.java
@@ -18,6 +18,8 @@ package org.apache.calcite.util.format.postgresql;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.text.ParseException;
+import java.text.ParsePosition;
import java.time.ZonedDateTime;
import java.time.temporal.ChronoField;
import java.util.Locale;
@@ -29,14 +31,44 @@ import java.util.Locale;
*/
public class TimeZoneMinutesFormatPattern extends StringFormatPattern {
public TimeZoneMinutesFormatPattern() {
- super("TZM");
+ super(ChronoUnitEnum.TIMEZONE_MINUTES, "TZM");
}
- @Override String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
+ @Override protected int parseValue(final ParsePosition inputPosition, final
String input,
+ final Locale locale, final boolean haveFillMode, boolean enforceLength)
+ throws ParseException {
+
+ if (inputPosition.getIndex() + 2 > input.length()) {
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
+ if (!Character.isDigit(input.charAt(inputPosition.getIndex()))
+ || !Character.isDigit(input.charAt(inputPosition.getIndex() + 1))) {
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
+ int timezoneMinutes =
+ Integer.parseInt(
+ input.substring(inputPosition.getIndex(),
+ inputPosition.getIndex() + 2));
+
+ if (timezoneMinutes >= 60) {
+ throw new ParseException("Value outside of valid range",
inputPosition.getIndex());
+ }
+
+ inputPosition.setIndex(inputPosition.getIndex() + 2);
+ return timezoneMinutes;
+ }
+
+ @Override protected String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
@Nullable String suffix, Locale locale) {
return String.format(
Locale.ROOT,
"%02d",
(dateTime.getOffset().get(ChronoField.OFFSET_SECONDS) % 3600) / 60);
}
+
+ @Override protected boolean isNumeric() {
+ return true;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/util/format/postgresql/YearWithCommasFormatPattern.java
b/core/src/main/java/org/apache/calcite/util/format/postgresql/YearWithCommasFormatPattern.java
new file mode 100644
index 0000000000..0aa3dee35d
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/util/format/postgresql/YearWithCommasFormatPattern.java
@@ -0,0 +1,106 @@
+/*
+ * 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.postgresql;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.text.ParseException;
+import java.text.ParsePosition;
+import java.time.ZonedDateTime;
+import java.util.Locale;
+
+/**
+ * Able to parse and generate string of years with commas to separate the
thousands. An
+ * example year is "1,997".
+ */
+public class YearWithCommasFormatPattern extends StringFormatPattern {
+ public YearWithCommasFormatPattern() {
+ super(ChronoUnitEnum.YEARS, "Y,YYY");
+ }
+
+ @Override protected int parseValue(ParsePosition inputPosition, String
input, Locale locale,
+ boolean haveFillMode, boolean enforceLength) throws ParseException {
+
+ final String inputTrimmed = input.substring(inputPosition.getIndex());
+ final int commaIndex = inputTrimmed.indexOf(',');
+
+ if (commaIndex <= 0 || commaIndex > 3) {
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
+ final String thousands = inputTrimmed.substring(0, commaIndex);
+ int endIndex;
+ if (enforceLength) {
+ if (inputPosition.getIndex() + commaIndex + 4 > input.length()) {
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+
+ endIndex = commaIndex + 4;
+ } else {
+ endIndex = commaIndex + 1;
+ for (; endIndex < inputTrimmed.length(); endIndex++) {
+ if (!Character.isDigit(inputTrimmed.charAt(endIndex))) {
+ break;
+ }
+ }
+
+ if (endIndex == commaIndex + 1 || endIndex > commaIndex + 4) {
+ inputPosition.setErrorIndex(inputPosition.getIndex());
+ throw new ParseException("Unable to parse value",
inputPosition.getIndex());
+ }
+ }
+
+ inputPosition.setIndex(inputPosition.getIndex() + endIndex);
+
+ final String remainingDigits = inputTrimmed.substring(commaIndex + 1,
endIndex);
+ return Integer.parseInt(thousands) * 1000 +
Integer.parseInt(remainingDigits);
+ }
+
+ @Override protected String dateTimeToString(ZonedDateTime dateTime, boolean
haveFillMode,
+ @Nullable String suffix, Locale locale) {
+ final String stringValue = String.format(locale, "%04d",
dateTime.getYear());
+
+ String outputSuffix = "";
+ if (suffix != null) {
+ switch (stringValue.charAt(stringValue.length() - 1)) {
+ case '1':
+ outputSuffix = "st";
+ break;
+ case '2':
+ outputSuffix = "nd";
+ break;
+ case '3':
+ outputSuffix = "rd";
+ break;
+ default:
+ outputSuffix = "th";
+ break;
+ }
+
+ if ("TH".equals(suffix)) {
+ outputSuffix = outputSuffix.toUpperCase(locale);
+ }
+ }
+
+ return stringValue.substring(0, stringValue.length() - 3) + ","
+ + stringValue.substring(stringValue.length() - 3) + outputSuffix;
+ }
+
+ @Override protected boolean isNumeric() {
+ return true;
+ }
+}
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 bb4dc7b783..2b44ef986f 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("TIMESTAMP(0) NOT NULL");
+ .columnType("TIMESTAMP_TZ(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("TIMESTAMP(0) NOT NULL");
+ .columnType("TIMESTAMP_TZ(0) NOT NULL");
wholeExpr("TO_TIMESTAMP(2000, 'YYYY')")
.withOperatorTable(opTable)
.withTypeCoercion(false)
diff --git
a/core/src/test/java/org/apache/calcite/util/format/postgresql/PostgresqlDateTimeFormatterTest.java
b/core/src/test/java/org/apache/calcite/util/format/postgresql/PostgresqlDateTimeFormatterTest.java
index b36491620a..df4ced40c5 100644
---
a/core/src/test/java/org/apache/calcite/util/format/postgresql/PostgresqlDateTimeFormatterTest.java
+++
b/core/src/test/java/org/apache/calcite/util/format/postgresql/PostgresqlDateTimeFormatterTest.java
@@ -24,15 +24,24 @@ import org.junit.jupiter.params.provider.ValueSource;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
+import java.time.temporal.ChronoField;
import java.util.Locale;
import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.fail;
/**
* Unit test for {@link PostgresqlDateTimeFormatter}.
*/
@Isolated
public class PostgresqlDateTimeFormatterTest {
+ private static final ZoneId TIME_ZONE = ZoneId.systemDefault();
+
+ private static final ZonedDateTime DAY_1_CE = createDateTime(1, 1, 1, 0, 0,
0, 0);
+ private static final ZonedDateTime APR_17_2024 = createDateTime(2024, 4, 17,
0, 0, 0, 0);
+ private static final ZonedDateTime JAN_1_2001 = createDateTime(2001, 1, 1,
0, 0, 0, 0);
+ private static final ZonedDateTime JAN_1_2024 = createDateTime(2024, 1, 1,
0, 0, 0, 0);
+
@ParameterizedTest
@ValueSource(strings = {"HH12", "HH"})
void testHH12(String pattern) {
@@ -1311,8 +1320,932 @@ public class PostgresqlDateTimeFormatterTest {
assertEquals("xii", PostgresqlDateTimeFormatter.toChar("rm", date4));
}
- private ZonedDateTime createDateTime(int year, int month, int dayOfMonth,
int hour, int minute,
- int seconds, int nanoseconds) {
+ @Test void testToTimestampHH() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusHours(1),
+ PostgresqlDateTimeFormatter.toTimestamp("01", "HH", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(1),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "HH", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(11),
+ PostgresqlDateTimeFormatter.toTimestamp("11", "HH", TIME_ZONE));
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("72", "HH", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("abc", "HH", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+ }
+
+ @Test void testToTimestampHH12() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusHours(1),
+ PostgresqlDateTimeFormatter.toTimestamp("01", "HH12", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(1),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "HH12", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(11),
+ PostgresqlDateTimeFormatter.toTimestamp("11", "HH12", TIME_ZONE));
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("72", "HH12", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("abc", "HH12", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+ }
+
+ @Test void testToTimestampHH24() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusHours(1),
+ PostgresqlDateTimeFormatter.toTimestamp("01", "HH24", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(1),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "HH24", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(18),
+ PostgresqlDateTimeFormatter.toTimestamp("18", "HH24", TIME_ZONE));
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("72", "HH24", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("abc", "HH24", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+ }
+
+ @Test void testToTimestampMI() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusMinutes(1),
+ PostgresqlDateTimeFormatter.toTimestamp("01", "MI", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMinutes(1),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "MI", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMinutes(57),
+ PostgresqlDateTimeFormatter.toTimestamp("57", "MI", TIME_ZONE));
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("72", "MI", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("abc", "MI", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+ }
+
+ @Test void testToTimestampSS() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusSeconds(1),
+ PostgresqlDateTimeFormatter.toTimestamp("01", "SS", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusSeconds(1),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "SS", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusSeconds(57),
+ PostgresqlDateTimeFormatter.toTimestamp("57", "SS", TIME_ZONE));
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("72", "SS", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("abc", "SS", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+ }
+
+ @Test void testToTimestampMS() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusNanos(1_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("001", "MS", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(1_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "MS", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(999_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("999", "MS", TIME_ZONE));
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("9999", "MS", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("abc", "MS", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+ }
+
+ @Test void testToTimestampUS() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusNanos(1_000),
+ PostgresqlDateTimeFormatter.toTimestamp("001", "US", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(1_000),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "US", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(999_000),
+ PostgresqlDateTimeFormatter.toTimestamp("999", "US", TIME_ZONE));
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("9999999", "US", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("abc", "US", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+ }
+
+ @Test void testToTimestampFF1() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusNanos(100_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "FF1", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(900_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("9", "FF1", TIME_ZONE));
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("72", "FF1", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("abc", "FF1", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+ }
+
+ @Test void testToTimestampFF2() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusNanos(10_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("01", "FF2", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(10_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "FF2", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(970_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("97", "FF2", TIME_ZONE));
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("999", "FF2", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+
+ try {
+ PostgresqlDateTimeFormatter.toTimestamp("abc", "FF2", TIME_ZONE);
+ fail();
+ } catch (Exception e) {
+ }
+ }
+
+ @Test void testToTimestampFF3() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusNanos(1_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("001", "FF3", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(1_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "FF3", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(976_000_000),
+ PostgresqlDateTimeFormatter.toTimestamp("976", "FF3", TIME_ZONE));
+ }
+
+ @Test void testToTimestampFF4() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusNanos(100_000),
+ PostgresqlDateTimeFormatter.toTimestamp("0001", "FF4", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(100_000),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "FF4", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(976_200_000),
+ PostgresqlDateTimeFormatter.toTimestamp("9762", "FF4", TIME_ZONE));
+ }
+
+ @Test void testToTimestampFF5() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusNanos(10_000),
+ PostgresqlDateTimeFormatter.toTimestamp("00001", "FF5", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(10_000),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "FF5", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(976_210_000),
+ PostgresqlDateTimeFormatter.toTimestamp("97621", "FF5", TIME_ZONE));
+ }
+
+ @Test void testToTimestampFF6() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusNanos(1_000),
+ PostgresqlDateTimeFormatter.toTimestamp("000001", "FF6", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(1_000),
+ PostgresqlDateTimeFormatter.toTimestamp("1", "FF6", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusNanos(976_214_000),
+ PostgresqlDateTimeFormatter.toTimestamp("976214", "FF6", TIME_ZONE));
+ }
+
+ @Test void testToTimestampAMPM() throws Exception {
+ assertEquals(
+ DAY_1_CE.plusHours(3),
+ PostgresqlDateTimeFormatter.toTimestamp("03AM", "HH12AM", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(3),
+ PostgresqlDateTimeFormatter.toTimestamp("03AM", "HH12PM", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(15),
+ PostgresqlDateTimeFormatter.toTimestamp("03PM", "HH12AM", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(15),
+ PostgresqlDateTimeFormatter.toTimestamp("03PM", "HH12PM", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(3),
+ PostgresqlDateTimeFormatter.toTimestamp("03A.M.", "HH12A.M.",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(3),
+ PostgresqlDateTimeFormatter.toTimestamp("03A.M.", "HH12P.M.",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(15),
+ PostgresqlDateTimeFormatter.toTimestamp("03P.M.", "HH12A.M.",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(15),
+ PostgresqlDateTimeFormatter.toTimestamp("03P.M.", "HH12P.M.",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(3),
+ PostgresqlDateTimeFormatter.toTimestamp("03am", "HH12am", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(3),
+ PostgresqlDateTimeFormatter.toTimestamp("03am", "HH12pm", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(15),
+ PostgresqlDateTimeFormatter.toTimestamp("03pm", "HH12am", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(15),
+ PostgresqlDateTimeFormatter.toTimestamp("03pm", "HH12pm", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(3),
+ PostgresqlDateTimeFormatter.toTimestamp("03a.m.", "HH12a.m.",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(3),
+ PostgresqlDateTimeFormatter.toTimestamp("03a.m.", "HH12p.m.",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(15),
+ PostgresqlDateTimeFormatter.toTimestamp("03p.m.", "HH12a.m.",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusHours(15),
+ PostgresqlDateTimeFormatter.toTimestamp("03p.m.", "HH12p.m.",
TIME_ZONE));
+ }
+
+ @Test void testToTimestampYYYYWithCommas() throws Exception {
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("0,001", "Y,YYY", TIME_ZONE));
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2,024", "Y,YYY", TIME_ZONE));
+ }
+
+ @Test void testToTimestampYYYY() throws Exception {
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("0001", "YYYY", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "YYYY", TIME_ZONE));
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024", "YYYY", TIME_ZONE));
+ }
+
+ @Test void testToTimestampYYY() throws Exception {
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("001", "YYY", TIME_ZONE));
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "YYY", TIME_ZONE));
+ assertEquals(
+ createDateTime(1987, 1, 1, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("987", "YYY", TIME_ZONE));
+ }
+
+ @Test void testToTimestampYY() throws Exception {
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("01", "YY", TIME_ZONE));
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "YY", TIME_ZONE));
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("24", "YY", TIME_ZONE));
+ }
+
+ @Test void testToTimestampY() throws Exception {
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "Y", TIME_ZONE));
+ assertEquals(
+ JAN_1_2001.plusYears(3),
+ PostgresqlDateTimeFormatter.toTimestamp("4", "Y", TIME_ZONE));
+ }
+
+ @Test void testToTimestampIYYY() throws Exception {
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("0001", "IYYY", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "IYYY", TIME_ZONE));
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024", "IYYY", TIME_ZONE));
+ }
+
+ @Test void testToTimestampIYY() throws Exception {
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("001", "IYY", TIME_ZONE));
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "IYY", TIME_ZONE));
+ assertEquals(
+ createDateTime(1987, 1, 1, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("987", "IYY", TIME_ZONE));
+ }
+
+ @Test void testToTimestampIY() throws Exception {
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("01", "IY", TIME_ZONE));
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "IY", TIME_ZONE));
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("24", "IY", TIME_ZONE));
+ }
+
+ @Test void testToTimestampI() throws Exception {
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "I", TIME_ZONE));
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "I", TIME_ZONE));
+ assertEquals(
+ JAN_1_2001.plusYears(3),
+ PostgresqlDateTimeFormatter.toTimestamp("4", "I", TIME_ZONE));
+ }
+
+ @Test void testToTimestampBCAD() throws Exception {
+ assertEquals(
+ 0,
+ PostgresqlDateTimeFormatter.toTimestamp("1920BC", "YYYYBC", TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 0,
+ PostgresqlDateTimeFormatter.toTimestamp("1920BC", "YYYYAD", TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 1,
+ PostgresqlDateTimeFormatter.toTimestamp("1920AD", "YYYYBC", TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 1,
+ PostgresqlDateTimeFormatter.toTimestamp("1920AD", "YYYYAD", TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 0,
+ PostgresqlDateTimeFormatter.toTimestamp("1920B.C.", "YYYYB.C.",
TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 0,
+ PostgresqlDateTimeFormatter.toTimestamp("1920B.C.", "YYYYA.D.",
TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 1,
+ PostgresqlDateTimeFormatter.toTimestamp("1920A.D.", "YYYYB.C.",
TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 1,
+ PostgresqlDateTimeFormatter.toTimestamp("1920A.D.", "YYYYA.D.",
TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 0,
+ PostgresqlDateTimeFormatter.toTimestamp("1920bc", "YYYYbc", TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 0,
+ PostgresqlDateTimeFormatter.toTimestamp("1920bc", "YYYYad", TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 1,
+ PostgresqlDateTimeFormatter.toTimestamp("1920ad", "YYYYbc", TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 1,
+ PostgresqlDateTimeFormatter.toTimestamp("1920ad", "YYYYad", TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 0,
+ PostgresqlDateTimeFormatter.toTimestamp("1920b.c.", "YYYYb.c.",
TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 0,
+ PostgresqlDateTimeFormatter.toTimestamp("1920b.c.", "YYYYa.d.",
TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 1,
+ PostgresqlDateTimeFormatter.toTimestamp("1920a.d.", "YYYYb.c.",
TIME_ZONE)
+ .get(ChronoField.ERA));
+ assertEquals(
+ 1,
+ PostgresqlDateTimeFormatter.toTimestamp("1920a.d.", "YYYYa.d.",
TIME_ZONE)
+ .get(ChronoField.ERA));
+ }
+
+ @Test void testToTimestampMonthUpperCase() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("JANUARY", "MONTH",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(2),
+ PostgresqlDateTimeFormatter.toTimestamp("MARCH", "MONTH",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(10),
+ PostgresqlDateTimeFormatter.toTimestamp("NOVEMBER", "MONTH",
TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampMonthCapitalized() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("January", "Month",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(2),
+ PostgresqlDateTimeFormatter.toTimestamp("March", "Month",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(10),
+ PostgresqlDateTimeFormatter.toTimestamp("November", "Month",
TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampMonthLowerCase() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("january", "month",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(2),
+ PostgresqlDateTimeFormatter.toTimestamp("march", "month",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(10),
+ PostgresqlDateTimeFormatter.toTimestamp("november", "month",
TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampMonUpperCase() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("JAN", "MON", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(2),
+ PostgresqlDateTimeFormatter.toTimestamp("MAR", "MON", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(10),
+ PostgresqlDateTimeFormatter.toTimestamp("NOV", "MON", TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampMonCapitalized() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("Jan", "Mon", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(2),
+ PostgresqlDateTimeFormatter.toTimestamp("Mar", "Mon", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(10),
+ PostgresqlDateTimeFormatter.toTimestamp("Nov", "Mon", TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampMonLowerCase() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("jan", "mon", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(2),
+ PostgresqlDateTimeFormatter.toTimestamp("mar", "mon", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(10),
+ PostgresqlDateTimeFormatter.toTimestamp("nov", "mon", TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampMM() throws Exception {
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("01", "MM", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "MM", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(10),
+ PostgresqlDateTimeFormatter.toTimestamp("11", "MM", TIME_ZONE));
+ }
+
+ @Test void testToTimestampDayUpperCase() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ createDateTime(1982, 6, 7, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 MONDAY", "IYYY IW
DAY", TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 10, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 THURSDAY", "IYYY IW
DAY", TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 11, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 FRIDAY", "IYYY IW
DAY", TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampDayCapitalized() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ createDateTime(1982, 6, 7, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 Monday", "IYYY IW
Day", TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 10, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 Thursday", "IYYY IW
Day", TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 11, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 Friday", "IYYY IW
Day", TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampDayLowerCase() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ createDateTime(1982, 6, 7, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 monday", "IYYY IW
day", TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 10, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 thursday", "IYYY IW
day", TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 11, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 friday", "IYYY IW
day", TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampDyUpperCase() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ createDateTime(1982, 6, 7, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 MON", "IYYY IW DY",
TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 10, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 THU", "IYYY IW DY",
TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 11, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 FRI", "IYYY IW DY",
TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampDyCapitalized() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ createDateTime(1982, 6, 7, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 Mon", "IYYY IW Dy",
TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 10, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 Thu", "IYYY IW Dy",
TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 11, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 Fri", "IYYY IW Dy",
TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampDyLowerCase() throws Exception {
+ final Locale originalLocale = Locale.getDefault();
+
+ try {
+ Locale.setDefault(Locale.US);
+
+ assertEquals(
+ createDateTime(1982, 6, 7, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 mon", "IYYY IW dy",
TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 10, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 thu", "IYYY IW dy",
TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 11, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 fri", "IYYY IW dy",
TIME_ZONE));
+ } finally {
+ Locale.setDefault(originalLocale);
+ }
+ }
+
+ @Test void testToTimestampDDD() throws Exception {
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024 001", "YYYY DDD",
TIME_ZONE));
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024 1", "YYYY DDD",
TIME_ZONE));
+ assertEquals(
+ createDateTime(2024, 5, 16, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2024 137", "YYYY DDD",
TIME_ZONE));
+ }
+
+ @Test void testToTimestampDD() throws Exception {
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("01", "DD", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "DD", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusDays(22),
+ PostgresqlDateTimeFormatter.toTimestamp("23", "DD", TIME_ZONE));
+ }
+
+ @Test void testToTimestampIDDD() throws Exception {
+ assertEquals(
+ createDateTime(2019, 12, 30, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2020 001", "IYYY IDDD",
TIME_ZONE));
+ assertEquals(
+ createDateTime(2019, 12, 30, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2020 1", "IYYY IDDD",
TIME_ZONE));
+ assertEquals(
+ createDateTime(2020, 5, 14, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2020 137", "IYYY IDDD",
TIME_ZONE));
+ }
+
+ @Test void testToTimestampID() throws Exception {
+ assertEquals(
+ createDateTime(1982, 6, 7, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 1", "IYYY IW ID",
TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 10, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 4", "IYYY IW ID",
TIME_ZONE));
+ assertEquals(
+ createDateTime(1982, 6, 11, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1982 23 5", "IYYY IW ID",
TIME_ZONE));
+ }
+
+ @Test void testToTimestampW() throws Exception {
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024 1 1", "YYYY MM W",
TIME_ZONE));
+ assertEquals(
+ createDateTime(2024, 4, 8, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2024 4 2", "YYYY MM W",
TIME_ZONE));
+ assertEquals(
+ createDateTime(2024, 11, 22, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2024 11 4", "YYYY MM W",
TIME_ZONE));
+ }
+
+ @Test void testToTimestampWW() throws Exception {
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024 01", "YYYY WW",
TIME_ZONE));
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024 1", "YYYY WW",
TIME_ZONE));
+ assertEquals(
+ createDateTime(2024, 12, 16, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2024 51", "YYYY WW",
TIME_ZONE));
+ }
+
+ @Test void testToTimestampIW() throws Exception {
+ assertEquals(
+ createDateTime(2019, 12, 30, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2020 01", "IYYY IW",
TIME_ZONE));
+ assertEquals(
+ createDateTime(2019, 12, 30, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2020 1", "IYYY IW",
TIME_ZONE));
+ assertEquals(
+ createDateTime(2020, 12, 14, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2020 51", "IYYY IW",
TIME_ZONE));
+ }
+
+ @Test void testToTimestampCC() throws Exception {
+ assertEquals(
+ JAN_1_2001,
+ PostgresqlDateTimeFormatter.toTimestamp("21", "CC", TIME_ZONE));
+ assertEquals(
+ createDateTime(1501, 1, 1, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("16", "CC", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("1", "CC", TIME_ZONE));
+ }
+
+ @Test void testToTimestampJ() throws Exception {
+ assertEquals(
+ JAN_1_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2460311", "J", TIME_ZONE));
+ assertEquals(
+ createDateTime(1984, 7, 15, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2445897", "J", TIME_ZONE));
+ assertEquals(
+ createDateTime(234, 3, 21, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("1806606", "J", TIME_ZONE));
+ }
+
+ @Test void testToTimestampRMUpperCase() throws Exception {
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("I", "RM", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(3),
+ PostgresqlDateTimeFormatter.toTimestamp("IV", "RM", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(8),
+ PostgresqlDateTimeFormatter.toTimestamp("IX", "RM", TIME_ZONE));
+ }
+
+ @Test void testToTimestampRMLowerCase() throws Exception {
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("i", "rm", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(3),
+ PostgresqlDateTimeFormatter.toTimestamp("iv", "rm", TIME_ZONE));
+ assertEquals(
+ DAY_1_CE.plusMonths(8),
+ PostgresqlDateTimeFormatter.toTimestamp("ix", "rm", TIME_ZONE));
+ }
+
+ @Test void testToTimestampDateValidFormats() throws Exception {
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024-04-17", "YYYY-MM-DD",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2,024-04-17", "Y,YYY-MM-DD",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("24-04-17", "YYY-MM-DD",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("24-04-17", "YY-MM-DD",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2124-04-17", "CCYY-MM-DD",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("20240417", "YYYYMMDD",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2,0240417", "Y,YYYMMDD",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024-16-3", "IYYY-IW-ID",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024-16 Wednesday", "IYYY-IW
Day", TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024-108", "IYYY-IDDD",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("April 17, 2024", "Month DD,
YYYY", TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("IV 17, 2024", "RM DD, YYYY",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("APR 17, 2024", "MON DD,
YYYY", TIME_ZONE));
+ assertEquals(
+ createDateTime(2024, 4, 15, 0, 0, 0, 0),
+ PostgresqlDateTimeFormatter.toTimestamp("2024-16", "YYYY-WW",
TIME_ZONE));
+ assertEquals(
+ APR_17_2024,
+ PostgresqlDateTimeFormatter.toTimestamp("2024-108", "YYYY-DDD",
TIME_ZONE));
+ assertEquals(
+ DAY_1_CE,
+ PostgresqlDateTimeFormatter.toTimestamp("0000-01-01", "YYYY-MM-DD",
TIME_ZONE));
+ }
+
+ @Test void testToTimestampWithTimezone() throws Exception {
+ final ZoneId utcZone = ZoneId.of("UTC");
+ assertEquals(
+ APR_17_2024.plusHours(7).withZoneSameLocal(utcZone),
+ PostgresqlDateTimeFormatter.toTimestamp("2024-04-17 00:00:00-07:00",
+ "YYYY-MM-DD HH24:MI:SSTZH:TZM", utcZone));
+ }
+
+ private static ZonedDateTime createDateTime(int year, int month, int
dayOfMonth, int hour,
+ int minute, int seconds, int nanoseconds) {
return ZonedDateTime.of(
LocalDateTime.of(year, month, dayOfMonth, hour, minute, seconds,
nanoseconds),
ZoneId.systemDefault());
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 1c0b2cae2b..3bea58f537 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -5149,10 +5149,43 @@ public class SqlOperatorTest {
f.checkNull("to_date(NULL, NULL)");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT);
+ list(SqlLibrary.ORACLE, SqlLibrary.REDSHIFT);
f0.forEachLibrary(libraries, consumer);
}
+ @Test void testToDatePg() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
+ .setFor(SqlLibraryOperators.TO_DATE_PG);
+
+ 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')",
+ "java.sql.SQLException: Invalid format: 'Invalid' for datetime string:
'2022-06-03'.",
+ 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 f0 =
fixture().setFor(SqlLibraryOperators.TO_TIMESTAMP);
@@ -5186,10 +5219,45 @@ public class SqlOperatorTest {
f.checkNull("to_timestamp(NULL, NULL)");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.ORACLE, SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT);
+ list(SqlLibrary.ORACLE, SqlLibrary.REDSHIFT);
f0.forEachLibrary(libraries, consumer);
}
+ @Test void testToTimestampPg() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.POSTGRESQL)
+ .setFor(SqlLibraryOperators.TO_TIMESTAMP_PG);
+
+ f.checkString("to_timestamp('2022-06-03 18:34:56', 'YYYY-MM-DD
HH24:MI:SS')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP_TZ(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_TZ(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_TZ(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_TZ(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_TZ(0) NOT NULL");
+ f.checkString("to_timestamp('18:34:56 2022-154', 'HH24:MI:SS YYYY-DDD')",
+ "2022-06-03 18:34:56",
+ "TIMESTAMP_TZ(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
timestamp "
+ + "string: 'ABCD'.",
+ true);
+ f.checkFails("to_timestamp('2022-06-03 18:34:56', 'Invalid')",
+ "java.sql.SQLException: Invalid format: 'Invalid' for timestamp
string: "
+ + "'2022-06-03 18:34:56'.",
+ 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);
