You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2023/01/22 03:00:35 UTC
[calcite] 01/02: [CALCITE-5414] Use DateTimeUtils to correctly convert between java.sql types and Unix timestamps
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 0ad974c308d74d2de4bb32f3cff66dc08a3bed59
Author: Greg Hart <gr...@protegrity.com>
AuthorDate: Tue Nov 15 10:04:06 2022 -0800
[CALCITE-5414] Use DateTimeUtils to correctly convert between java.sql types and Unix timestamps
Close apache/calcite#2995
---
.../adapter/jdbc/JdbcToEnumerableConverter.java | 8 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 270 +++++++++++++---
.../org/apache/calcite/util/BuiltInMethod.java | 12 +-
.../java/org/apache/calcite/test/JdbcTest.java | 84 +++++
.../org/apache/calcite/test/SqlFunctionsTest.java | 341 +++++++++++++++++++++
.../java/org/apache/calcite/test/QuidemTest.java | 1 +
6 files changed, 668 insertions(+), 48 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcToEnumerableConverter.java b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcToEnumerableConverter.java
index 8f02d040ef..442bc564b5 100644
--- a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcToEnumerableConverter.java
+++ b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcToEnumerableConverter.java
@@ -308,8 +308,12 @@ public class JdbcToEnumerableConverter
switch (sqlTypeName) {
case DATE:
return (nullable
- ? BuiltInMethod.DATE_TO_INT_OPTIONAL
- : BuiltInMethod.DATE_TO_INT).method;
+ ? (offset
+ ? BuiltInMethod.DATE_TO_INT_OPTIONAL_OFFSET
+ : BuiltInMethod.DATE_TO_INT_OPTIONAL)
+ : (offset
+ ? BuiltInMethod.DATE_TO_INT_OFFSET
+ : BuiltInMethod.DATE_TO_INT)).method;
case TIME:
return (nullable
? BuiltInMethod.TIME_TO_INT_OPTIONAL
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 61d04f0051..9a7fa9b9b1 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -60,6 +60,8 @@ import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
@@ -67,7 +69,6 @@ import java.util.Base64;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
-import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
@@ -93,11 +94,11 @@ import static java.util.Objects.requireNonNull;
*
* <p>Not present: and, or, not (builtin operators are better, because they
* use lazy evaluation. Implementations do not check for null values; the
- * calling code must do that.</p>
+ * calling code must do that.
*
* <p>Many of the functions do not check for null values. This is intentional.
* If null arguments are possible, the code-generation framework checks for
- * nulls before calling the functions.</p>
+ * nulls before calling the functions.
*/
@SuppressWarnings("UnnecessaryUnboxing")
@Deterministic
@@ -2086,41 +2087,96 @@ public class SqlFunctions {
: (Short) cannotConvert(o, short.class);
}
- /** Converts the Java type used for UDF parameters of SQL DATE type
- * ({@link java.sql.Date}) to internal representation (int).
+ /**
+ * Converts a SQL DATE value from the Java type
+ * ({@link java.sql.Date}) to the internal representation type
+ * (number of days since January 1st, 1970 as {@code int})
+ * in the local time zone.
+ *
+ * <p>Since a time zone is not available, the date is converted to represent
+ * the same date as a Unix date in UTC as the {@link java.sql.Date} value in
+ * the local time zone.
*
- * <p>Converse of {@link #internalToDate(int)}. */
- public static int toInt(java.util.Date v) {
+ * @see #toInt(java.sql.Date, TimeZone)
+ * @see #internalToDate(int) converse method
+ */
+ public static int toInt(java.sql.Date v) {
return toInt(v, LOCAL_TZ);
}
- public static int toInt(java.util.Date v, TimeZone timeZone) {
- return (int) (toLong(v, timeZone) / DateTimeUtils.MILLIS_PER_DAY);
+ /**
+ * Converts a SQL DATE value from the Java type
+ * ({@link java.sql.Date}) to the internal representation type
+ * (number of days since January 1st, 1970 as {@code int}).
+ *
+ * <p>The {@link java.sql.Date} class uses the standard Gregorian calendar
+ * which switches from the Julian calendar to the Gregorian calendar in
+ * October 1582. For compatibility with ISO-8601, the internal representation
+ * is converted to use the proleptic Gregorian calendar.
+ *
+ * <p>If the date contains a partial day, it will be rounded to a full day
+ * depending on the milliseconds value. If the milliseconds value is positive,
+ * it will be rounded down to the closest full day. If the milliseconds value
+ * is negative, it will be rounded up to the closest full day.
+ */
+ public static int toInt(java.sql.Date v, TimeZone timeZone) {
+ return DateTimeUtils.sqlDateToUnixDate(v, timeZone);
}
- public static @PolyNull Integer toIntOptional(java.util.@PolyNull Date v) {
- return v == null ? castNonNull(null) : toInt(v);
+ /**
+ * Converts a nullable SQL DATE value from the Java type
+ * ({@link java.sql.Date}) to the internal representation type
+ * (number of days since January 1st, 1970 as {@link Integer})
+ * in the local time zone.
+ *
+ * <p>Since a time zone is not available, the date is converted to represent
+ * the same date as a Unix date in UTC as the {@link java.sql.Date} value in
+ * the local time zone.
+ *
+ * @see #toInt(java.sql.Date, TimeZone)
+ * @see #internalToDate(Integer) converse method
+ */
+ public static @PolyNull Integer toIntOptional(java.sql.@PolyNull Date v) {
+ return v == null
+ ? castNonNull(null)
+ : toInt(v);
}
- public static @PolyNull Integer toIntOptional(java.util.@PolyNull Date v,
+ /**
+ * Converts a nullable SQL DATE value from the Java type
+ * ({@link java.sql.Date}) to the internal representation type
+ * (number of days since January 1st, 1970 as {@link Integer}).
+ *
+ * @see #toInt(java.sql.Date, TimeZone)
+ */
+ public static @PolyNull Integer toIntOptional(java.sql.@PolyNull Date v,
TimeZone timeZone) {
return v == null
? castNonNull(null)
: toInt(v, timeZone);
}
- public static long toLong(Date v) {
- return toLong(v, LOCAL_TZ);
- }
-
- /** Converts the Java type used for UDF parameters of SQL TIME type
- * ({@link java.sql.Time}) to internal representation (int).
+ /**
+ * Converts a SQL TIME value from the Java type
+ * ({@link java.sql.Time}) to the internal representation type
+ * (number of milliseconds since January 1st, 1970 as {@code int})
+ * in the local time zone.
*
- * <p>Converse of {@link #internalToTime(int)}. */
+ * @see #toIntOptional(java.sql.Time)
+ * @see #internalToTime(int) converse method
+ */
public static int toInt(java.sql.Time v) {
- return (int) (toLong(v) % DateTimeUtils.MILLIS_PER_DAY);
+ return DateTimeUtils.sqlTimeToUnixTime(v, LOCAL_TZ);
}
+ /**
+ * Converts a nullable SQL TIME value from the Java type
+ * ({@link java.sql.Time}) to the internal representation type
+ * (number of milliseconds since January 1st, 1970 as {@link Integer}).
+ *
+ * @see #toInt(java.sql.Time)
+ * @see #internalToTime(Integer) converse method
+ */
public static @PolyNull Integer toIntOptional(java.sql.@PolyNull Time v) {
return v == null ? castNonNull(null) : toInt(v);
}
@@ -2137,7 +2193,8 @@ public class SqlFunctions {
return o instanceof Integer ? (Integer) o
: o instanceof Number ? toInt((Number) o)
: o instanceof String ? toInt((String) o)
- : o instanceof java.util.Date ? toInt((java.util.Date) o)
+ : o instanceof java.sql.Date ? toInt((java.sql.Date) o)
+ : o instanceof java.sql.Time ? toInt((java.sql.Time) o)
: (Integer) cannotConvert(o, int.class);
}
@@ -2145,27 +2202,80 @@ public class SqlFunctions {
return o == null ? castNonNull(null) : toInt(o);
}
- /** Converts the Java type used for UDF parameters of SQL TIMESTAMP type
- * ({@link java.sql.Timestamp}) to internal representation (long).
+ /**
+ * Converts a SQL TIMESTAMP value from the Java type
+ * ({@link java.util.Date}) to the internal representation type
+ * (number of milliseconds since January 1st, 1970 as {@code long}).
*
- * <p>Converse of {@link #internalToTimestamp(long)}. */
+ * <p>Since a time zone is not available, converts the timestamp to represent
+ * the same date and time as a Unix timestamp in UTC as the
+ * {@link java.util.Date} value in the local time zone.
+ *
+ * <p>The {@link java.util.Date} class uses the standard Gregorian calendar
+ * which switches from the Julian calendar to the Gregorian calendar in
+ * October 1582. For compatibility with ISO-8601, converts the internal
+ * representation to use the proleptic Gregorian calendar.
+ */
+ public static long toLong(java.util.Date v) {
+ return DateTimeUtils.utilDateToUnixTimestamp(v, LOCAL_TZ);
+ }
+
+ /**
+ * Converts a SQL TIMESTAMP value from the Java type
+ * ({@link Timestamp}) to the internal representation type
+ * (number of milliseconds since January 1st, 1970 as {@code long}).
+ *
+ * <p>Since a time zone is not available, converts the timestamp to represent
+ * the same date and time as a Unix timestamp in UTC as the
+ * {@link Timestamp} value in the local time zone.
+ *
+ * @see #toLong(Timestamp, TimeZone)
+ * @see #internalToTimestamp(Long) converse method
+ */
public static long toLong(Timestamp v) {
return toLong(v, LOCAL_TZ);
}
- // mainly intended for java.sql.Timestamp but works for other dates also
- @SuppressWarnings("JavaUtilDate")
- public static long toLong(java.util.Date v, TimeZone timeZone) {
- final long time = v.getTime();
- return time + timeZone.getOffset(time);
+ /**
+ * Converts a SQL TIMESTAMP value from the Java type
+ * ({@link Timestamp}) to the internal representation type
+ * (number of milliseconds since January 1st, 1970 as {@code long}).
+ *
+ * <p>For backwards compatibility, time zone offsets are calculated in
+ * relation to the local time zone instead of UTC. Providing the default time
+ * zone or {@code null} will return the timestamp unmodified.
+ *
+ * <p>The {@link Timestamp} class uses the standard Gregorian calendar which
+ * switches from the Julian calendar to the Gregorian calendar in
+ * October 1582. For compatibility with ISO-8601, the internal representation
+ * is converted to use the proleptic Gregorian calendar.
+ */
+ public static long toLong(Timestamp v, TimeZone timeZone) {
+ return DateTimeUtils.sqlTimestampToUnixTimestamp(v, timeZone);
}
- // mainly intended for java.sql.Timestamp but works for other dates also
- public static @PolyNull Long toLongOptional(java.util.@PolyNull Date v) {
+ /**
+ * Converts a nullable SQL TIMESTAMP value from the Java type
+ * ({@link Timestamp}) to the internal representation type
+ * (number of milliseconds since January 1st, 1970 as {@link Long})
+ * in the local time zone.
+ *
+ * @see #toLong(Timestamp, TimeZone)
+ * @see #internalToTimestamp(Long) converse method
+ */
+ public static @PolyNull Long toLongOptional(@PolyNull Timestamp v) {
return v == null ? castNonNull(null) : toLong(v, LOCAL_TZ);
}
- public static @PolyNull Long toLongOptional(@PolyNull Timestamp v, TimeZone timeZone) {
+ /**
+ * Converts a nullable SQL TIMESTAMP value from the Java type
+ * ({@link Timestamp}) to the internal representation type
+ * (number of milliseconds since January 1st, 1970 as {@link Long}).
+ *
+ * @see #toLong(Timestamp, TimeZone)
+ */
+ public static @PolyNull Long toLongOptional(@PolyNull Timestamp v,
+ TimeZone timeZone) {
if (v == null) {
return castNonNull(null);
}
@@ -2187,6 +2297,9 @@ public class SqlFunctions {
return o instanceof Long ? (Long) o
: o instanceof Number ? toLong((Number) o)
: o instanceof String ? toLong((String) o)
+ : o instanceof java.sql.Date ? toInt((java.sql.Date) o)
+ : o instanceof java.sql.Time ? toInt((java.sql.Time) o)
+ : o instanceof java.sql.Timestamp ? toLong((java.sql.Timestamp) o)
: o instanceof java.util.Date ? toLong((java.util.Date) o)
: (Long) cannotConvert(o, long.class);
}
@@ -2243,24 +2356,61 @@ public class SqlFunctions {
: toBigDecimal(o.toString());
}
- /** Converts the internal representation of a SQL DATE (int) to the Java
- * type used for UDF parameters ({@link java.sql.Date}). */
+ /**
+ * Converts a SQL DATE value from the internal representation type
+ * (number of days since January 1st, 1970) to the Java type
+ * ({@link java.sql.Date}).
+ *
+ * <p>Since a time zone is not available, converts the date to represent the
+ * same date as a {@link java.sql.Date} in the local time zone as the Unix
+ * date in UTC.
+ *
+ * <p>The Unix date should be the number of days since January 1st, 1970
+ * using the proleptic Gregorian calendar as defined by ISO-8601. The
+ * returned {@link java.sql.Date} object will use the standard Gregorian
+ * calendar which switches from the Julian calendar to the Gregorian calendar
+ * in October 1582.
+ *
+ * @see #internalToDate(Integer)
+ * @see #toInt(java.sql.Date) converse method
+ */
public static java.sql.Date internalToDate(int v) {
- final long t = v * DateTimeUtils.MILLIS_PER_DAY;
- return new java.sql.Date(t - LOCAL_TZ.getOffset(t));
+ final LocalDate date = LocalDate.ofEpochDay(v);
+ return java.sql.Date.valueOf(date);
}
- /** As {@link #internalToDate(int)} but allows nulls. */
+ /**
+ * Converts a nullable SQL DATE value from the internal representation type
+ * (number of days since January 1st, 1970) to the Java type
+ * ({@link java.sql.Date}).
+ *
+ * @see #internalToDate(int)
+ * @see #toIntOptional(java.sql.Date) converse method
+ */
public static java.sql.@PolyNull Date internalToDate(@PolyNull Integer v) {
return v == null ? castNonNull(null) : internalToDate(v.intValue());
}
- /** Converts the internal representation of a SQL TIME (int) to the Java
- * type used for UDF parameters ({@link java.sql.Time}). */
+ /**
+ * Converts a SQL TIME value from the internal representation type
+ * (number of milliseconds since January 1st, 1970) to the Java type
+ * ({@link java.sql.Time}).
+ *
+ * @see #internalToTime(Integer)
+ * @see #toInt(java.sql.Time) converse method
+ */
public static java.sql.Time internalToTime(int v) {
return new java.sql.Time(v - LOCAL_TZ.getOffset(v));
}
+ /**
+ * Converts a nullable SQL TIME value from the internal representation type
+ * (number of milliseconds since January 1st, 1970) to the Java type
+ * ({@link java.sql.Time}).
+ *
+ * @see #internalToTime(Integer)
+ * @see #toIntOptional(java.sql.Time) converse method
+ */
public static java.sql.@PolyNull Time internalToTime(@PolyNull Integer v) {
return v == null ? castNonNull(null) : internalToTime(v.intValue());
}
@@ -2316,12 +2466,48 @@ public class SqlFunctions {
.toString();
}
- /** Converts the internal representation of a SQL TIMESTAMP (long) to the Java
- * type used for UDF parameters ({@link java.sql.Timestamp}). */
+ /**
+ * Converts a SQL TIMESTAMP value from the internal representation type
+ * (number of milliseconds since January 1st, 1970) to the Java Type
+ * ({@link Timestamp})
+ * in the local time zone.
+ *
+ * <p>Since a time zone is not available, the timestamp is converted to
+ * represent the same timestamp as a {@link Timestamp} in the local time zone
+ * as the Unix timestamp in UTC.
+ *
+ * <p>The Unix timestamp should be the number of milliseconds since
+ * January 1st, 1970 using the proleptic Gregorian calendar as defined by
+ * ISO-8601. The returned {@link Timestamp} object will use the standard
+ * Gregorian calendar which switches from the Julian calendar to the
+ * Gregorian calendar in October 1582.
+ *
+ * @see #internalToTimestamp(Long)
+ * @see #toLong(Timestamp, TimeZone)
+ * @see #toLongOptional(Timestamp)
+ * @see #toLongOptional(Timestamp, TimeZone)
+ * @see #toLong(Timestamp) converse method
+ */
public static java.sql.Timestamp internalToTimestamp(long v) {
- return new java.sql.Timestamp(v - LOCAL_TZ.getOffset(v));
+ final LocalDateTime dateTime = LocalDateTime.ofEpochSecond(
+ Math.floorDiv(v, DateTimeUtils.MILLIS_PER_SECOND),
+ (int) (Math.floorMod(v, DateTimeUtils.MILLIS_PER_SECOND) * DateTimeUtils.NANOS_PER_MILLI),
+ ZoneOffset.UTC);
+ return java.sql.Timestamp.valueOf(dateTime);
}
+ /**
+ * Converts a nullable SQL TIMESTAMP value from the internal representation
+ * type (number of milliseconds since January 1st, 1970) to the Java Type
+ * ({@link Timestamp})
+ * in the local time zone.
+ *
+ * @see #internalToTimestamp(long)
+ * @see #toLong(Timestamp)
+ * @see #toLong(Timestamp, TimeZone)
+ * @see #toLongOptional(Timestamp, TimeZone)
+ * @see #toLongOptional(Timestamp) converse method
+ */
public static java.sql.@PolyNull Timestamp internalToTimestamp(@PolyNull Long v) {
return v == null ? castNonNull(null) : internalToTimestamp(v.longValue());
}
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 da839ea09d..ff9c5fcb30 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -563,13 +563,17 @@ public enum BuiltInMethod {
Comparable.class, Comparator.class),
ROUND_LONG(SqlFunctions.class, "round", long.class, long.class),
ROUND_INT(SqlFunctions.class, "round", int.class, int.class),
- DATE_TO_INT(SqlFunctions.class, "toInt", java.util.Date.class),
+ DATE_TO_INT(SqlFunctions.class, "toInt", java.sql.Date.class),
+ DATE_TO_INT_OFFSET(SqlFunctions.class, "toInt", java.sql.Date.class,
+ TimeZone.class),
DATE_TO_INT_OPTIONAL(SqlFunctions.class, "toIntOptional",
- java.util.Date.class),
+ java.sql.Date.class),
+ DATE_TO_INT_OPTIONAL_OFFSET(SqlFunctions.class, "toIntOptional",
+ java.sql.Date.class, TimeZone.class),
TIME_TO_INT(SqlFunctions.class, "toInt", Time.class),
TIME_TO_INT_OPTIONAL(SqlFunctions.class, "toIntOptional", Time.class),
- TIMESTAMP_TO_LONG(SqlFunctions.class, "toLong", java.util.Date.class),
- TIMESTAMP_TO_LONG_OFFSET(SqlFunctions.class, "toLong", java.util.Date.class,
+ TIMESTAMP_TO_LONG(SqlFunctions.class, "toLong", Timestamp.class),
+ TIMESTAMP_TO_LONG_OFFSET(SqlFunctions.class, "toLong", Timestamp.class,
TimeZone.class),
TIMESTAMP_TO_LONG_OPTIONAL(SqlFunctions.class, "toLongOptional",
Timestamp.class),
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 61f97f90fa..d54a9224a0 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -8030,6 +8030,90 @@ public class JdbcTest {
.returns("EXPR$0=[0E+1, 1.1]\n");
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5414">[CALCITE-5414]</a>
+ * Convert between standard Gregorian and proleptic Gregorian calendars for
+ * literal dates in local time zone. */
+ @Test void testLiteralDateToSqlTimestamp() {
+ CalciteAssert.that()
+ .with(CalciteConnectionProperty.TIME_ZONE, TimeZone.getDefault().getID())
+ .query("select cast('1500-04-30' as date)")
+ .returns(resultSet -> {
+ try {
+ assertTrue(resultSet.next());
+ assertEquals("1500-04-30", resultSet.getString(1));
+ assertEquals(Date.valueOf("1500-04-30"), resultSet.getDate(1));
+ assertFalse(resultSet.next());
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ });
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5414">[CALCITE-5414]</a>
+ * Convert between standard Gregorian and proleptic Gregorian calendars for
+ * literal timestamps in local time zone. */
+ @Test void testLiteralTimestampToSqlTimestamp() {
+ CalciteAssert.that()
+ .with(CalciteConnectionProperty.TIME_ZONE, TimeZone.getDefault().getID())
+ .query("select cast('1500-04-30 12:00:00' as timestamp)")
+ .returns(resultSet -> {
+ try {
+ assertTrue(resultSet.next());
+ assertEquals("1500-04-30 12:00:00", resultSet.getString(1));
+ assertEquals(Timestamp.valueOf("1500-04-30 12:00:00"), resultSet.getTimestamp(1));
+ assertFalse(resultSet.next());
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ });
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5414">[CALCITE-5414]</a>
+ * Convert between standard Gregorian and proleptic Gregorian calendars for
+ * dynamic dates in local time zone. */
+ @Test void testDynamicDateToSqlTimestamp() {
+ final Date date = Date.valueOf("1500-04-30");
+ CalciteAssert.that()
+ .with(CalciteConnectionProperty.TIME_ZONE, TimeZone.getDefault().getID())
+ .query("select cast(? as date)")
+ .consumesPreparedStatement(statement -> statement.setDate(1, date))
+ .returns(resultSet -> {
+ try {
+ assertTrue(resultSet.next());
+ assertEquals("1500-04-30", resultSet.getString(1));
+ assertEquals(date, resultSet.getDate(1));
+ assertFalse(resultSet.next());
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ });
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5414">[CALCITE-5414]</a>
+ * Convert between standard Gregorian and proleptic Gregorian calendars for
+ * dynamic timestamps in local time zone. */
+ @Test void testDynamicTimestampToSqlTimestamp() {
+ final Timestamp timestamp = Timestamp.valueOf("1500-04-30 12:00:00");
+ CalciteAssert.that()
+ .with(CalciteConnectionProperty.TIME_ZONE, TimeZone.getDefault().getID())
+ .query("select cast(? as timestamp)")
+ .consumesPreparedStatement(statement -> statement.setTimestamp(1, timestamp))
+ .returns(resultSet -> {
+ try {
+ assertTrue(resultSet.next());
+ assertEquals("1500-04-30 12:00:00", resultSet.getString(1));
+ assertEquals(timestamp, resultSet.getTimestamp(1));
+ assertFalse(resultSet.next());
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ });
+ }
+
private static String sums(int n, boolean c) {
final StringBuilder b = new StringBuilder();
for (int i = 0; i < n; i++) {
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index 683333b596..ea1fdc2d6f 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -25,11 +25,20 @@ import org.apache.calcite.runtime.Utilities;
import org.junit.jupiter.api.Test;
import java.math.BigDecimal;
+import java.sql.Time;
+import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
+import java.util.Calendar;
import java.util.Collections;
import java.util.List;
+import java.util.Locale;
+import java.util.TimeZone;
+import static org.apache.calcite.avatica.util.DateTimeUtils.MILLIS_PER_DAY;
+import static org.apache.calcite.avatica.util.DateTimeUtils.dateStringToUnixDate;
+import static org.apache.calcite.avatica.util.DateTimeUtils.timeStringToUnixDate;
+import static org.apache.calcite.avatica.util.DateTimeUtils.timestampStringToUnixDate;
import static org.apache.calcite.avatica.util.DateTimeUtils.ymdToUnixDate;
import static org.apache.calcite.runtime.SqlFunctions.addMonths;
import static org.apache.calcite.runtime.SqlFunctions.charLength;
@@ -37,6 +46,9 @@ import static org.apache.calcite.runtime.SqlFunctions.concat;
import static org.apache.calcite.runtime.SqlFunctions.fromBase64;
import static org.apache.calcite.runtime.SqlFunctions.greater;
import static org.apache.calcite.runtime.SqlFunctions.initcap;
+import static org.apache.calcite.runtime.SqlFunctions.internalToDate;
+import static org.apache.calcite.runtime.SqlFunctions.internalToTime;
+import static org.apache.calcite.runtime.SqlFunctions.internalToTimestamp;
import static org.apache.calcite.runtime.SqlFunctions.lesser;
import static org.apache.calcite.runtime.SqlFunctions.lower;
import static org.apache.calcite.runtime.SqlFunctions.ltrim;
@@ -47,6 +59,10 @@ import static org.apache.calcite.runtime.SqlFunctions.rtrim;
import static org.apache.calcite.runtime.SqlFunctions.sha1;
import static org.apache.calcite.runtime.SqlFunctions.subtractMonths;
import static org.apache.calcite.runtime.SqlFunctions.toBase64;
+import static org.apache.calcite.runtime.SqlFunctions.toInt;
+import static org.apache.calcite.runtime.SqlFunctions.toIntOptional;
+import static org.apache.calcite.runtime.SqlFunctions.toLong;
+import static org.apache.calcite.runtime.SqlFunctions.toLongOptional;
import static org.apache.calcite.runtime.SqlFunctions.trim;
import static org.apache.calcite.runtime.SqlFunctions.upper;
import static org.apache.calcite.test.Matchers.within;
@@ -974,4 +990,329 @@ class SqlFunctionsTest {
// ok
}
}
+
+ /**
+ * Tests that a date in the local time zone converts to a Unix timestamp in
+ * UTC.
+ */
+ @Test void testToIntWithSqlDate() {
+ assertThat(toInt(new java.sql.Date(0L)), is(0)); // rounded to closest day
+ assertThat(sqlDate("1970-01-01"), is(0));
+ assertThat(sqlDate("1500-04-30"), is(dateStringToUnixDate("1500-04-30")));
+ }
+
+ /**
+ * Test calendar conversion from the standard Gregorian calendar used by
+ * {@code java.sql} and the proleptic Gregorian calendar used by Unix
+ * timestamps.
+ */
+ @Test void testToIntWithSqlDateInGregorianShift() {
+ assertThat(sqlDate("1582-10-04"), is(dateStringToUnixDate("1582-10-04")));
+ assertThat(sqlDate("1582-10-05"), is(dateStringToUnixDate("1582-10-15")));
+ assertThat(sqlDate("1582-10-15"), is(dateStringToUnixDate("1582-10-15")));
+ }
+
+ /**
+ * Test date range 0001-01-01 to 9999-12-31 required by ANSI SQL.
+ *
+ * <p>Java may not be able to represent 0001-01-01 depending on the default
+ * time zone. If the date would fall outside of Anno Domini (AD) when
+ * converted to the default time zone, that date should not be tested.
+ *
+ * <p>Not every time zone has a January 1st 12:00am, so this test skips those
+ * dates.
+ */
+ @Test void testToIntWithSqlDateInAnsiDateRange() {
+ for (int i = 2; i <= 9999; ++i) {
+ final String str = String.format(Locale.ROOT, "%04d-01-01", i);
+ final java.sql.Date date = java.sql.Date.valueOf(str);
+ final Timestamp timestamp = new Timestamp(date.getTime());
+ if (timestamp.toString().endsWith("00:00:00.0")) {
+ // Test equality if the time is valid in Java
+ assertThat("Converts '" + str + "' from SQL to Unix date",
+ toInt(date),
+ is(dateStringToUnixDate(str)));
+ } else {
+ // Test result matches legacy behavior if the time cannot be
+ // represented in Java. This probably results in a different date but
+ // is pretty rare.
+ final long expected =
+ (date.getTime() + DateTimeUtils.DEFAULT_ZONE.getOffset(date.getTime()))
+ / DateTimeUtils.MILLIS_PER_DAY;
+ assertThat("Converts '" + str
+ + "' from SQL to Unix date using legacy behavior",
+ toInt(date),
+ is((int) expected));
+ }
+ }
+ }
+
+ /**
+ * Test using a custom {@link TimeZone} to calculate the Unix timestamp.
+ * Dates created by a {@link java.sql.Date} method should be converted
+ * relative to the local time and not UTC.
+ */
+ @Test public void testToIntWithTimeZone() {
+ // Dates created by a Calendar should be converted to a Unix date in that
+ // time zone
+ final Calendar utcCal =
+ Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
+ utcCal.set(1970, Calendar.JANUARY, 1, 0, 0, 0);
+ utcCal.set(Calendar.MILLISECOND, 0);
+ assertThat(
+ toInt(new java.sql.Date(utcCal.getTimeInMillis()),
+ utcCal.getTimeZone()),
+ is(0));
+
+ // Dates should be relative to the local time and not UTC
+ final java.sql.Date epoch = java.sql.Date.valueOf("1970-01-01");
+
+ final TimeZone minusDayZone = TimeZone.getDefault();
+ minusDayZone.setRawOffset((int) (minusDayZone.getOffset(0L) - MILLIS_PER_DAY));
+ assertThat(toInt(epoch, minusDayZone), is(-1));
+
+ final TimeZone plusDayZone = TimeZone.getDefault();
+ plusDayZone.setRawOffset((int) (plusDayZone.getOffset(0L) + MILLIS_PER_DAY));
+ assertThat(toInt(epoch, plusDayZone), is(1));
+ }
+
+ /**
+ * Tests that a nullable date in the local time zone converts to a Unix
+ * timestamp in UTC.
+ */
+ @Test void testToIntOptionalWithLocalTimeZone() {
+ assertThat(toIntOptional(java.sql.Date.valueOf("1970-01-01")), is(0));
+ assertThat(toIntOptional((java.sql.Date) null), is(nullValue()));
+ }
+
+ /**
+ * Tests that a nullable date in the given time zone converts to a Unix
+ * timestamp in UTC.
+ */
+ @Test void testToIntOptionalWithCustomTimeZone() {
+ final TimeZone utc = TimeZone.getTimeZone("UTC");
+ assertThat(toIntOptional(new java.sql.Date(0L), utc), is(0));
+ assertThat(toIntOptional(null, utc), is(nullValue()));
+ }
+
+ /**
+ * Tests that a time in the local time zone converts to a Unix time in UTC.
+ */
+ @Test void testToIntWithSqlTime() {
+ assertThat(sqlTime("00:00:00"), is(timeStringToUnixDate("00:00:00")));
+ assertThat(sqlTime("23:59:59"), is(timeStringToUnixDate("23:59:59")));
+ }
+
+ /**
+ * Tests that a nullable time in the local time zone converts to a Unix time
+ * in UTC.
+ */
+ @Test void testToIntOptionalWithSqlTime() {
+ assertThat(toIntOptional(Time.valueOf("00:00:00")), is(0));
+ assertThat(toIntOptional((Time) null), is(nullValue()));
+ }
+
+ /**
+ * Tests that a timestamp in the local time zone converts to a Unix timestamp
+ * in UTC.
+ */
+ @Test void testToLongWithSqlTimestamp() {
+ assertThat(sqlTimestamp("1970-01-01 00:00:00"), is(0L));
+ assertThat(sqlTimestamp("2014-09-30 15:28:27.356"),
+ is(timestampStringToUnixDate("2014-09-30 15:28:27.356")));
+ assertThat(sqlTimestamp("1500-04-30 12:00:00.123"),
+ is(timestampStringToUnixDate("1500-04-30 12:00:00.123")));
+ }
+
+ /**
+ * Test using a custom {@link TimeZone} to calculate the Unix timestamp.
+ * Timestamps created by a {@link Calendar} should be converted to a Unix
+ * timestamp in the given time zone. Timestamps created by a {@link Timestamp}
+ * method should be converted relative to the local time and not UTC.
+ */
+ @Test void testToLongWithSqlTimestampAndCustomTimeZone() {
+ final Timestamp epoch = java.sql.Timestamp.valueOf("1970-01-01 00:00:00");
+
+ final Calendar utcCal =
+ Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
+ utcCal.set(1970, Calendar.JANUARY, 1, 0, 0, 0);
+ utcCal.set(Calendar.MILLISECOND, 0);
+ assertThat(toLong(new Timestamp(utcCal.getTimeInMillis()), utcCal.getTimeZone()),
+ is(0L));
+
+ final TimeZone est = TimeZone.getTimeZone("GMT-5:00");
+ assertThat(toLong(epoch, est),
+ is(epoch.getTime() + est.getOffset(epoch.getTime())));
+
+ final TimeZone ist = TimeZone.getTimeZone("GMT+5:00");
+ assertThat(toLong(epoch, ist),
+ is(epoch.getTime() + ist.getOffset(epoch.getTime())));
+ }
+
+ /**
+ * Test calendar conversion from the standard Gregorian calendar used by
+ * {@code java.sql} and the proleptic Gregorian calendar used by Unix
+ * timestamps.
+ */
+ @Test void testToLongWithSqlTimestampInGregorianShift() {
+ assertThat(sqlTimestamp("1582-10-04 00:00:00"),
+ is(timestampStringToUnixDate("1582-10-04 00:00:00")));
+ assertThat(sqlTimestamp("1582-10-05 00:00:00"),
+ is(timestampStringToUnixDate("1582-10-15 00:00:00")));
+ assertThat(sqlTimestamp("1582-10-15 00:00:00"),
+ is(timestampStringToUnixDate("1582-10-15 00:00:00")));
+ }
+
+ /**
+ * Test date range 0001-01-01 to 9999-12-31 required by ANSI SQL.
+ *
+ * <p>Java may not be able to represent 0001-01-01 depending on the default
+ * time zone. If the date would fall outside of Anno Domini (AD) when
+ * converted to the default time zone, that date should not be tested.
+ *
+ * <p>Not every time zone has a January 1st 12:00am, so this test skips those
+ * dates.
+ */
+ @Test void testToLongWithSqlTimestampInAnsiDateRange() {
+ for (int i = 2; i <= 9999; ++i) {
+ final String str = String.format(Locale.ROOT, "%04d-01-01 00:00:00", i);
+ final Timestamp timestamp = Timestamp.valueOf(str);
+ if (timestamp.toString().endsWith("00:00:00.0")) {
+ // Test equality if the time is valid in Java
+ assertThat("Converts '" + str + "' from SQL to Unix timestamp",
+ toLong(timestamp),
+ is(timestampStringToUnixDate(str)));
+ } else {
+ // Test result matches legacy behavior if the time cannot be represented in Java
+ // This probably results in a different date but is pretty rare
+ final long expected = timestamp.getTime()
+ + DateTimeUtils.DEFAULT_ZONE.getOffset(timestamp.getTime());
+ assertThat("Converts '" + str
+ + "' from SQL to Unix timestamp using legacy behavior",
+ toLong(timestamp),
+ is(expected));
+ }
+ }
+ }
+
+ /**
+ * Tests that a nullable timestamp in the local time zone converts to a Unix
+ * timestamp in UTC.
+ */
+ @Test void testToLongOptionalWithLocalTimeZone() {
+ assertThat(toLongOptional(Timestamp.valueOf("1970-01-01 00:00:00")), is(0L));
+ assertThat(toLongOptional(null), is(nullValue()));
+ }
+
+ /**
+ * Tests that a nullable timestamp in the given time zone converts to a Unix
+ * timestamp in UTC.
+ */
+ @Test void testToLongOptionalWithCustomTimeZone() {
+ final TimeZone utc = TimeZone.getTimeZone("UTC");
+ assertThat(toLongOptional(new Timestamp(0L), utc), is(0L));
+ assertThat(toLongOptional(null, utc), is(nullValue()));
+ }
+
+ /**
+ * Tests that a Unix timestamp converts to a date in the local time zone.
+ */
+ @Test void testInternalToDate() {
+ assertThat(internalToDate(0), is(java.sql.Date.valueOf("1970-01-01")));
+ assertThat(internalToDate(dateStringToUnixDate("1500-04-30")),
+ is(java.sql.Date.valueOf("1500-04-30")));
+ }
+
+ /**
+ * Test calendar conversion from the standard Gregorian calendar used by
+ * {@code java.sql} and the proleptic Gregorian calendar used by Unix
+ * timestamps.
+ */
+ @Test void testInternalToDateWithGregorianShift() {
+ // Gregorian shift
+ assertThat(internalToDate(dateStringToUnixDate("1582-10-04")),
+ is(java.sql.Date.valueOf("1582-10-04")));
+ assertThat(internalToDate(dateStringToUnixDate("1582-10-05")),
+ is(java.sql.Date.valueOf("1582-10-15")));
+ assertThat(internalToDate(dateStringToUnixDate("1582-10-15")),
+ is(java.sql.Date.valueOf("1582-10-15")));
+ }
+
+ /**
+ * Test date range 0001-01-01 to 9999-12-31 required by ANSI SQL.
+ *
+ * <p>Java may not be able to represent all dates depending on the default time zone, but both
+ * the expected and actual assertion values handles that in the same way.
+ */
+ @Test void testInternalToDateWithAnsiDateRange() {
+ for (int i = 2; i <= 9999; ++i) {
+ final String str = String.format(Locale.ROOT, "%04d-01-01", i);
+ assertThat(internalToDate(dateStringToUnixDate(str)),
+ is(java.sql.Date.valueOf(str)));
+ }
+ }
+
+ /**
+ * Tests that a Unix time converts to a SQL time in the local time zone.
+ */
+ @Test void testInternalToTime() {
+ assertThat(internalToTime(0), is(Time.valueOf("00:00:00")));
+ assertThat(internalToTime(86399000), is(Time.valueOf("23:59:59")));
+ }
+
+ /**
+ * Tests that a Unix timestamp converts to a SQL timestamp in the local time
+ * zone.
+ */
+ @Test void testInternalToTimestamp() {
+ assertThat(internalToTimestamp(0),
+ is(Timestamp.valueOf("1970-01-01 00:00:00.0")));
+ assertThat(internalToTimestamp(timestampStringToUnixDate("2014-09-30 15:28:27.356")),
+ is(Timestamp.valueOf("2014-09-30 15:28:27.356")));
+ assertThat(internalToTimestamp(timestampStringToUnixDate("1500-04-30 12:00:00.123")),
+ is(Timestamp.valueOf("1500-04-30 12:00:00.123")));
+ }
+
+ /**
+ * Test calendar conversion from the standard Gregorian calendar used by
+ * {@code java.sql} and the proleptic Gregorian calendar used by Unix timestamps.
+ */
+ @Test void testInternalToTimestampWithGregorianShift() {
+ assertThat(
+ internalToTimestamp(timestampStringToUnixDate("1582-10-04 00:00:00")),
+ is(Timestamp.valueOf("1582-10-04 00:00:00.0")));
+ assertThat(
+ internalToTimestamp(timestampStringToUnixDate("1582-10-05 00:00:00")),
+ is(Timestamp.valueOf("1582-10-15 00:00:00.0")));
+ assertThat(
+ internalToTimestamp(timestampStringToUnixDate("1582-10-15 00:00:00")),
+ is(Timestamp.valueOf("1582-10-15 00:00:00.0")));
+ }
+
+ /**
+ * Test date range 0001-01-01 to 9999-12-31 required by ANSI SQL.
+ *
+ * <p>Java may not be able to represent all dates depending on the default
+ * time zone, but both the expected and actual assertion values handles that
+ * in the same way.
+ */
+ @Test void testInternalToTimestampWithAnsiDateRange() {
+ for (int i = 2; i <= 9999; ++i) {
+ final String str = String.format(Locale.ROOT, "%04d-01-01 00:00:00", i);
+ assertThat(internalToTimestamp(timestampStringToUnixDate(str)),
+ is(Timestamp.valueOf(str)));
+ }
+ }
+
+ private int sqlDate(String str) {
+ return toInt(java.sql.Date.valueOf(str));
+ }
+
+ private int sqlTime(String str) {
+ return toInt(java.sql.Time.valueOf(str));
+ }
+
+ private long sqlTimestamp(String str) {
+ return toLong(java.sql.Timestamp.valueOf(str));
+ }
}
diff --git a/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java b/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
index debb0b4ff7..555c01f95e 100644
--- a/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/QuidemTest.java
@@ -293,6 +293,7 @@ public abstract class QuidemTest {
.connect();
case "catchall":
return CalciteAssert.that()
+ .with(CalciteConnectionProperty.TIME_ZONE, "UTC")
.withSchema("s",
new ReflectiveSchema(
new CatchallSchema()))