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 <[email protected]> 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()))
