content: /*
   * 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.avatica.util;
  
  import org.hamcrest.Matcher;
  import org.junit.Test;
  
  import java.math.BigDecimal;
  import java.sql.Time;
  import java.sql.Timestamp;
  import java.text.SimpleDateFormat;
  import java.time.LocalDate;
  import java.time.temporal.IsoFields;
  import java.util.Calendar;
  import java.util.Date;
  import java.util.Locale;
  import java.util.TimeZone;
  
  import static org.apache.calcite.avatica.util.DateTimeUtils.EPOCH_JULIAN;
  import static org.apache.calcite.avatica.util.DateTimeUtils.MILLIS_PER_DAY;
  import static org.apache.calcite.avatica.util.DateTimeUtils.MILLIS_PER_HOUR;
  import static org.apache.calcite.avatica.util.DateTimeUtils.MILLIS_PER_SECOND;
  import static org.apache.calcite.avatica.util.DateTimeUtils.addMonths;
  import static org.apache.calcite.avatica.util.DateTimeUtils.dateStringToUnixDate;
  import static org.apache.calcite.avatica.util.DateTimeUtils.digitCount;
  import static org.apache.calcite.avatica.util.DateTimeUtils.intervalDayTimeToString;
  import static org.apache.calcite.avatica.util.DateTimeUtils.intervalYearMonthToString;
  import static org.apache.calcite.avatica.util.DateTimeUtils.sqlDateToUnixDate;
  import static org.apache.calcite.avatica.util.DateTimeUtils.sqlTimeToUnixTime;
  import static org.apache.calcite.avatica.util.DateTimeUtils.sqlTimestampToUnixTimestamp;
  import static org.apache.calcite.avatica.util.DateTimeUtils.subtractMonths;
  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.unixDateCeil;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixDateExtract;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixDateFloor;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixDateToSqlDate;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixDateToString;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixTimeExtract;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixTimeToSqlTime;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixTimeToString;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixTimestamp;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixTimestampExtract;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixTimestampToSqlTimestamp;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixTimestampToString;
  import static org.apache.calcite.avatica.util.DateTimeUtils.unixTimestampToUtilDate;
  import static org.apache.calcite.avatica.util.DateTimeUtils.utilDateToUnixTimestamp;
  import static org.apache.calcite.avatica.util.DateTimeUtils.ymdToJulian;
  import static org.apache.calcite.avatica.util.DateTimeUtils.ymdToUnixDate;
  
  import static org.hamcrest.CoreMatchers.anyOf;
  import static org.hamcrest.CoreMatchers.equalTo;
  import static org.hamcrest.CoreMatchers.is;
  import static org.hamcrest.CoreMatchers.notNullValue;
  import static org.hamcrest.MatcherAssert.assertThat;
  import static org.junit.Assert.assertEquals;
  import static org.junit.Assert.assertSame;
  import static org.junit.Assert.assertTrue;
  
  /**
   * Tests for {@link DateTimeUtils}.
   */
  public class DateTimeUtilsTest {
    private static final Calendar CALENDAR = Calendar.getInstance(TimeZone.getDefault(), Locale.ROOT);
  
    @Test public void testEasyLog10() {
      assertEquals(1, digitCount(0));
      assertEquals(1, digitCount(1));
      assertEquals(1, digitCount(9));
      assertEquals(2, digitCount(10));
      assertEquals(2, digitCount(11));
      assertEquals(2, digitCount(99));
      assertEquals(3, digitCount(100));
    }
  
    @SuppressWarnings("deprecation")
    @Test public void testFloorDiv() {
      assertThat(DateTimeUtils.floorDiv(13, 3), equalTo(4L));
      assertThat(DateTimeUtils.floorDiv(12, 3), equalTo(4L));
      assertThat(DateTimeUtils.floorDiv(11, 3), equalTo(3L));
      assertThat(DateTimeUtils.floorDiv(-13, 3), equalTo(-5L));
      assertThat(DateTimeUtils.floorDiv(-12, 3), equalTo(-4L));
      assertThat(DateTimeUtils.floorDiv(-11, 3), equalTo(-4L));
      assertThat(DateTimeUtils.floorDiv(0, 3), equalTo(0L));
      assertThat(DateTimeUtils.floorDiv(1, 3), equalTo(0L));
      assertThat(DateTimeUtils.floorDiv(-1, 3), is(-1L));
    }
  
    @SuppressWarnings("deprecation")
    @Test public void testFloorMod() {
      assertThat(DateTimeUtils.floorMod(13, 3), is(1L));
      assertThat(DateTimeUtils.floorMod(12, 3), is(0L));
      assertThat(DateTimeUtils.floorMod(11, 3), is(2L));
      assertThat(DateTimeUtils.floorMod(-13, 3), is(2L));
      assertThat(DateTimeUtils.floorMod(-12, 3), is(0L));
      assertThat(DateTimeUtils.floorMod(-11, 3), is(1L));
      assertThat(DateTimeUtils.floorMod(0, 3), is(0L));
      assertThat(DateTimeUtils.floorMod(1, 3), is(1L));
      assertThat(DateTimeUtils.floorMod(-1, 3), is(2L));
    }
  
    @Test public void testTimeUnitRange() {
      assertSame(TimeUnitRange.of(TimeUnit.YEAR, null), TimeUnitRange.YEAR);
      assertSame(TimeUnitRange.of(TimeUnit.YEAR, TimeUnit.MONTH), TimeUnitRange.YEAR_TO_MONTH);
      assertSame(TimeUnitRange.of(TimeUnit.MONTH, null), TimeUnitRange.MONTH);
      assertSame(TimeUnitRange.of(TimeUnit.DAY, null), TimeUnitRange.DAY);
      assertSame(TimeUnitRange.of(TimeUnit.DAY, TimeUnit.HOUR), TimeUnitRange.DAY_TO_HOUR);
      assertSame(TimeUnitRange.of(TimeUnit.DAY, TimeUnit.MINUTE), TimeUnitRange.DAY_TO_MINUTE);
      assertSame(TimeUnitRange.of(TimeUnit.DAY, TimeUnit.SECOND), TimeUnitRange.DAY_TO_SECOND);
      assertSame(TimeUnitRange.of(TimeUnit.HOUR, null), TimeUnitRange.HOUR);
      assertSame(TimeUnitRange.of(TimeUnit.HOUR, TimeUnit.MINUTE), TimeUnitRange.HOUR_TO_MINUTE);
      assertSame(TimeUnitRange.of(TimeUnit.HOUR, TimeUnit.SECOND), TimeUnitRange.HOUR_TO_SECOND);
      assertSame(TimeUnitRange.of(TimeUnit.MINUTE, null), TimeUnitRange.MINUTE);
      assertSame(TimeUnitRange.of(TimeUnit.MINUTE, TimeUnit.SECOND), TimeUnitRange.MINUTE_TO_SECOND);
      assertSame(TimeUnitRange.of(TimeUnit.SECOND, null), TimeUnitRange.SECOND);
      assertSame(TimeUnitRange.of(TimeUnit.ISOYEAR, null), TimeUnitRange.ISOYEAR);
      assertSame(TimeUnitRange.of(TimeUnit.QUARTER, null), TimeUnitRange.QUARTER);
      assertSame(TimeUnitRange.of(TimeUnit.WEEK, null), TimeUnitRange.WEEK);
      assertSame(TimeUnitRange.of(TimeUnit.MILLISECOND, null), TimeUnitRange.MILLISECOND);
      assertSame(TimeUnitRange.of(TimeUnit.MICROSECOND, null), TimeUnitRange.MICROSECOND);
      assertSame(TimeUnitRange.of(TimeUnit.NANOSECOND, null), TimeUnitRange.NANOSECOND);
      assertSame(TimeUnitRange.of(TimeUnit.DOW, null), TimeUnitRange.DOW);
      assertSame(TimeUnitRange.of(TimeUnit.ISODOW, null), TimeUnitRange.ISODOW);
      assertSame(TimeUnitRange.of(TimeUnit.DOY, null), TimeUnitRange.DOY);
      assertSame(TimeUnitRange.of(TimeUnit.EPOCH, null), TimeUnitRange.EPOCH);
      assertSame(TimeUnitRange.of(TimeUnit.DECADE, null), TimeUnitRange.DECADE);
      assertSame(TimeUnitRange.of(TimeUnit.CENTURY, null), TimeUnitRange.CENTURY);
      assertSame(TimeUnitRange.of(TimeUnit.MILLENNIUM, null), TimeUnitRange.MILLENNIUM);
    }
  
    @Test public void testTimeUnitMultipliers() {
      assertEquals(TimeUnit.NANOSECOND.multiplier,
          TimeUnit.MICROSECOND.multiplier.divide(BigDecimal.valueOf(1000)));
      assertEquals(TimeUnit.MICROSECOND.multiplier,
          TimeUnit.MILLISECOND.multiplier.divide(BigDecimal.valueOf(1000)));
      assertEquals(TimeUnit.MILLISECOND.multiplier,
          TimeUnit.SECOND.multiplier.divide(BigDecimal.valueOf(1000)));
      assertEquals(BigDecimal.valueOf(60),
          TimeUnit.HOUR.multiplier.divide(TimeUnit.MINUTE.multiplier));
      assertEquals(BigDecimal.valueOf(60),
          TimeUnit.MINUTE.multiplier.divide(TimeUnit.SECOND.multiplier));
      assertEquals(BigDecimal.valueOf(24),
          TimeUnit.DAY.multiplier.divide(TimeUnit.HOUR.multiplier));
      assertEquals(BigDecimal.valueOf(7),
          TimeUnit.WEEK.multiplier.divide(TimeUnit.DAY.multiplier));
      assertEquals(BigDecimal.valueOf(4),
          TimeUnit.YEAR.multiplier.divide(TimeUnit.QUARTER.multiplier));
      assertEquals(BigDecimal.valueOf(12),
          TimeUnit.YEAR.multiplier.divide(TimeUnit.MONTH.multiplier));
      assertEquals(BigDecimal.valueOf(12),
          TimeUnit.ISOYEAR.multiplier.divide(TimeUnit.MONTH.multiplier));
      assertEquals(BigDecimal.valueOf(3),
          TimeUnit.QUARTER.multiplier.divide(TimeUnit.MONTH.multiplier));
      assertEquals(BigDecimal.valueOf(10),
          TimeUnit.DECADE.multiplier.divide(TimeUnit.YEAR.multiplier));
      assertEquals(BigDecimal.valueOf(100),
          TimeUnit.CENTURY.multiplier.divide(TimeUnit.YEAR.multiplier));
      assertEquals(BigDecimal.valueOf(1000),
          TimeUnit.MILLENNIUM.multiplier.divide(TimeUnit.YEAR.multiplier));
    }
  
    @Test public void testUnixDateToString() {
      // Verify these using the "date" command. E.g.
      // $ date -u --date="@$(expr 10957 \* 86400)"
      // Sat Jan  1 00:00:00 UTC 2000
      assertEquals("2000-01-01", unixDateToString(10957));
  
      assertEquals("1970-01-01", unixDateToString(0));
      assertEquals("1970-01-02", unixDateToString(1));
      assertEquals("1971-01-01", unixDateToString(365));
      assertEquals("1972-01-01", unixDateToString(730));
      assertEquals("1972-02-28", unixDateToString(788));
      assertEquals("1972-02-29", unixDateToString(789));
      assertEquals("1972-03-01", unixDateToString(790));
  
      assertEquals("1969-01-01", unixDateToString(-365));
      assertEquals("2000-01-01", unixDateToString(10957));
      assertEquals("2000-02-28", unixDateToString(11015));
      assertEquals("2000-02-29", unixDateToString(11016));
      assertEquals("2000-03-01", unixDateToString(11017));
      assertEquals("1900-01-01", unixDateToString(-25567));
      assertEquals("1900-02-28", unixDateToString(-25509));
      assertEquals("1900-03-01", unixDateToString(-25508));
      assertEquals("1945-02-24", unixDateToString(-9077));
    }
  
    @Test public void testYmdToUnixDate() {
      assertEquals(0, ymdToUnixDate(1970, 1, 1));
      assertEquals(365, ymdToUnixDate(1971, 1, 1));
      assertEquals(-365, ymdToUnixDate(1969, 1, 1));
      assertEquals(11015, ymdToUnixDate(2000, 2, 28));
      assertEquals(11016, ymdToUnixDate(2000, 2, 29));
      assertEquals(11017, ymdToUnixDate(2000, 3, 1));
      assertEquals(-9077, ymdToUnixDate(1945, 2, 24));
      assertEquals(-25509, ymdToUnixDate(1900, 2, 28));
      assertEquals(-25508, ymdToUnixDate(1900, 3, 1));
    }
  
    @Test public void testDateToString() {
      checkDateString("1970-01-01", 0);
      //noinspection PointlessArithmeticExpression
      checkDateString("1971-02-03", 0 + 365 + 31 + (3 - 1));
      //noinspection PointlessArithmeticExpression
      checkDateString("1971-02-28", 0 + 365 + 31 + (28 - 1));
      //noinspection PointlessArithmeticExpression
      checkDateString("1971-03-01", 0 + 365 + 31 + 28 + (1 - 1));
      //noinspection PointlessArithmeticExpression
      checkDateString("1972-02-28", 0 + 365 * 2 + 31 + (28 - 1));
      //noinspection PointlessArithmeticExpression
      checkDateString("1972-02-29", 0 + 365 * 2 + 31 + (29 - 1));
      //noinspection PointlessArithmeticExpression
      checkDateString("1972-03-01", 0 + 365 * 2 + 31 + 29 + (1 - 1));
  
      final int d1900 = -(70 * 365 + 70 / 4);
      final int century = 100 * 365 + 100 / 4;
      checkDateString("1900-01-01", d1900);
      // +1 because 1800 is not a leap year
      final int d1800 = d1900 - century + 1;
      checkDateString("1800-01-01", d1800);
      final int d1700 = d1800 - century + 1;
      checkDateString("1700-01-01", d1700);
      final int d1600 = d1700 - century;
      checkDateString("1600-01-01", d1600);
      final int d1500 = d1600 - century + 1;
      checkDateString("1500-01-01", d1500); // fails, about 10 days off
    }
  
    private void checkDateString(String s, int d) {
      assertThat(unixDateToString(d), is(s));
      assertThat(dateStringToUnixDate(s), is(d));
    }
  
    @Test public void testTimeToString() {
      checkTimeString("00", "00:00:00", 0, 0);
      checkTimeString("00:00", "00:00:00", 0, 0);
      checkTimeString("00:00:00", 0, 0);
      checkTimeString("23:59:59", 0, 86400000 - 1000);
      checkTimeString("23:59:59.1", 1, 86400000 - 1000 + 100);
      checkTimeString("23:59:59.01", 2, 86400000 - 1000 + 10);
      checkTimeString("23:59:59.1234", 3, 86400000 - 1000 + 123);
      checkTimeString("23:59:59.1236", 3, 86400000 - 1000 + 124);
      checkTimeString("23:59:59.123456789012345678901234567890", 3,
          86400000 - 1000 + 123);
    }
  
    @Test public void testTimestampExtract() {
      // 1970-01-01 00:00:00.000
      assertThat(unixTimestampExtract(TimeUnitRange.HOUR, 0L), is(0));
      assertThat(unixTimestampExtract(TimeUnitRange.MINUTE, 0L), is(0));
      assertThat(unixTimestampExtract(TimeUnitRange.SECOND, 0L), is(0));
      // 1970-01-02 00:00:00.000
      assertThat(unixTimestampExtract(TimeUnitRange.HOUR, 86400000L), is(0));
      assertThat(unixTimestampExtract(TimeUnitRange.MINUTE, 86400000L), is(0));
      assertThat(unixTimestampExtract(TimeUnitRange.SECOND, 86400000L), is(0));
    }
  
    @Test public void testTimeExtract() {
      // 00:00:00.000
      assertThat(unixTimeExtract(TimeUnitRange.HOUR, 0), is(0));
      assertThat(unixTimeExtract(TimeUnitRange.MINUTE, 0), is(0));
      assertThat(unixTimeExtract(TimeUnitRange.SECOND, 0), is(0));
      // 00:59:59.999
      assertThat(unixTimeExtract(TimeUnitRange.HOUR, 3599999), is(0));
      assertThat(unixTimeExtract(TimeUnitRange.MINUTE, 3599999), is(59));
      assertThat(unixTimeExtract(TimeUnitRange.SECOND, 3599999), is(59));
      // 01:59:59.999
      assertThat(unixTimeExtract(TimeUnitRange.HOUR, 7199999), is(1));
      assertThat(unixTimeExtract(TimeUnitRange.MINUTE, 7199999), is(59));
      assertThat(unixTimeExtract(TimeUnitRange.SECOND, 7199999), is(59));
      // 01:58:59.999
      assertThat(unixTimeExtract(TimeUnitRange.HOUR, 7139999), is(1));
      assertThat(unixTimeExtract(TimeUnitRange.MINUTE, 7139999), is(58));
      assertThat(unixTimeExtract(TimeUnitRange.SECOND, 7139999), is(59));
      // 23:59:59.999
      assertThat(unixTimeExtract(TimeUnitRange.HOUR, 86399999), is(23));
      assertThat(unixTimeExtract(TimeUnitRange.MINUTE, 86399999), is(59));
      assertThat(unixTimeExtract(TimeUnitRange.SECOND, 86399999), is(59));
    }
  
    private void checkTimeString(String s, int p, int d) {
      checkTimeString(s, s, p, d);
    }
  
    private void checkTimeString(String string, String expectedString, int p, int d) {
      int digitsAfterPoint = string.indexOf('.') >= 0
          ? string.length() - string.indexOf('.') - 1
          : 0;
      if (digitsAfterPoint == p) {
        assertThat(unixTimeToString(d, p), is(expectedString));
      }
      assertThat(timeStringToUnixDate(string), is(d));
    }
  
    @Test public void testTimestampToString() {
      // ISO format would be "1970-01-01T00:00:00" but SQL format is different
      checkTimestampString("1970-01-01 00:00:00", 0, 0L);
      checkTimestampString("1970-02-01 23:59:59", 0, 86400000L * 32L - 1000L);
      checkTimestampString("1970-02-01 23:59:59.123", 3,
          86400000L * 32L - 1000L + 123);
      checkTimestampString("1970-02-01 23:59:59.04", 2,
          86400000L * 32L - 1000L + 40);
    }
  
    private void checkTimestampString(String s, int p, long d) {
      assertThat(unixTimestampToString(d, p), is(s));
      assertThat(timestampStringToUnixDate(s), is(d));
    }
  
    @Test public void testIntervalYearMonthToString() {
      TimeUnitRange range = TimeUnitRange.YEAR_TO_MONTH;
      assertEquals("+0-00", intervalYearMonthToString(0, range));
      assertEquals("+1-00", intervalYearMonthToString(12, range));
      assertEquals("+1-01", intervalYearMonthToString(13, range));
      assertEquals("-1-01", intervalYearMonthToString(-13, range));
    }
  
    @Test public void testIntervalDayTimeToString() {
      assertEquals("+0", intervalYearMonthToString(0, TimeUnitRange.YEAR));
      assertEquals("+0-00",
          intervalYearMonthToString(0, TimeUnitRange.YEAR_TO_MONTH));
      assertEquals("+0", intervalYearMonthToString(0, TimeUnitRange.MONTH));
      assertEquals("+0", intervalDayTimeToString(0, TimeUnitRange.DAY, 0));
      assertEquals("+0 00",
          intervalDayTimeToString(0, TimeUnitRange.DAY_TO_HOUR, 0));
      assertEquals("+0 00:00",
          intervalDayTimeToString(0, TimeUnitRange.DAY_TO_MINUTE, 0));
      assertEquals("+0 00:00:00",
          intervalDayTimeToString(0, TimeUnitRange.DAY_TO_SECOND, 0));
      assertEquals("+0", intervalDayTimeToString(0, TimeUnitRange.HOUR, 0));
      assertEquals("+0:00",
          intervalDayTimeToString(0, TimeUnitRange.HOUR_TO_MINUTE, 0));
      assertEquals("+0:00:00",
          intervalDayTimeToString(0, TimeUnitRange.HOUR_TO_SECOND, 0));
      assertEquals("+0",
          intervalDayTimeToString(0, TimeUnitRange.MINUTE, 0));
      assertEquals("+0:00",
          intervalDayTimeToString(0, TimeUnitRange.MINUTE_TO_SECOND, 0));
      assertEquals("+0",
          intervalDayTimeToString(0, TimeUnitRange.SECOND, 0));
    }
  
    @Test public void testYmdToJulian() {
      // All checked using http://aa.usno.navy.mil/data/docs/JulianDate.php.
      // We round up - if JulianDate.php gives 2451544.5, we use 2451545.
      assertThat(ymdToJulian(2014, 4, 3), is(2456751));
  
      // 2000 is a leap year
      assertThat(ymdToJulian(2000, 1, 1), is(2451545));
      assertThat(ymdToJulian(2000, 2, 28), is(2451603));
      assertThat(ymdToJulian(2000, 2, 29), is(2451604));
      assertThat(ymdToJulian(2000, 3, 1), is(2451605));
  
      assertThat(ymdToJulian(1970, 1, 1), is(2440588));
      assertThat(ymdToJulian(1970, 1, 1), is(EPOCH_JULIAN));
      assertThat(ymdToJulian(1901, 1, 1), is(2415386));
  
      // 1900 is not a leap year
      assertThat(ymdToJulian(1900, 10, 17), is(2415310));
      assertThat(ymdToJulian(1900, 3, 1), is(2415080));
      assertThat(ymdToJulian(1900, 2, 28), is(2415079));
      assertThat(ymdToJulian(1900, 2, 1), is(2415052));
      assertThat(ymdToJulian(1900, 1, 1), is(2415021));
  
      assertThat(ymdToJulian(1777, 7, 4), is(2370281));
  
      // 2016 is a leap year
      assertThat(ymdToJulian(2016, 2, 28), is(2457447));
      assertThat(ymdToJulian(2016, 2, 29), is(2457448));
      assertThat(ymdToJulian(2016, 3, 1), is(2457449));
    }
  
    @Test public void testExtract() {
      assertThat(unixDateExtract(TimeUnitRange.YEAR, 0), is(1970L));
      assertThat(unixDateExtract(TimeUnitRange.YEAR, -1), is(1969L));
      assertThat(unixDateExtract(TimeUnitRange.YEAR, 364), is(1970L));
      assertThat(unixDateExtract(TimeUnitRange.YEAR, 365), is(1971L));
  
      assertThat(unixDateExtract(TimeUnitRange.MONTH, 0), is(1L));
      assertThat(unixDateExtract(TimeUnitRange.MONTH, -1), is(12L));
      assertThat(unixDateExtract(TimeUnitRange.MONTH, 364), is(12L));
      assertThat(unixDateExtract(TimeUnitRange.MONTH, 365), is(1L));
  
      // 1969/12/31 was a Wed (4)
      assertThat(unixDateExtract(TimeUnitRange.DOW, -1), is(4L)); // wed
      assertThat(unixDateExtract(TimeUnitRange.DOW, 0), is(5L)); // thu
      assertThat(unixDateExtract(TimeUnitRange.DOW, 1), is(6L)); // fri
      assertThat(unixDateExtract(TimeUnitRange.DOW, 2), is(7L)); // sat
      assertThat(unixDateExtract(TimeUnitRange.DOW, 3), is(1L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.DOW, 365), is(6L));
      assertThat(unixDateExtract(TimeUnitRange.DOW, 366), is(7L));
  
      // 1969/12/31 was a Wed (4)
      assertThat(unixDateExtract(TimeUnitRange.ISODOW, -1), is(3L)); // wed
      assertThat(unixDateExtract(TimeUnitRange.ISODOW, 0), is(4L)); // thu
      assertThat(unixDateExtract(TimeUnitRange.ISODOW, 1), is(5L)); // fri
      assertThat(unixDateExtract(TimeUnitRange.ISODOW, 2), is(6L)); // sat
      assertThat(unixDateExtract(TimeUnitRange.ISODOW, 3), is(7L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.ISODOW, 365), is(5L));
      assertThat(unixDateExtract(TimeUnitRange.ISODOW, 366), is(6L));
  
      assertThat(unixDateExtract(TimeUnitRange.DOY, -1), is(365L));
      assertThat(unixDateExtract(TimeUnitRange.DOY, 0), is(1L));
      assertThat(unixDateExtract(TimeUnitRange.DOY, 1), is(2L));
      assertThat(unixDateExtract(TimeUnitRange.DOY, 2), is(3L));
      assertThat(unixDateExtract(TimeUnitRange.DOY, 3), is(4L));
      assertThat(unixDateExtract(TimeUnitRange.DOY, 364), is(365L));
      assertThat(unixDateExtract(TimeUnitRange.DOY, 365), is(1L));
      assertThat(unixDateExtract(TimeUnitRange.DOY, 366), is(2L));
      assertThat(unixDateExtract(TimeUnitRange.DOY, 365 + 365 + 366 - 1),
          is(366L)); // 1972/12/31
      assertThat(unixDateExtract(TimeUnitRange.DOY, 365 + 365 + 366),
          is(1L)); // 1973/1/1
  
      // The number of the week of the year that the day is in. By definition
      // (ISO 8601), the first week of a year contains January 4 of that year.
      // (The ISO-8601 week starts on Monday.) In other words, the first Thursday
      // of a year is in week 1 of that year.
      //
      // Because of this, it is possible for early January dates to be part of
      // the 52nd or 53rd week of the previous year. For example, 2005-01-01 is
      // part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd
      // week of year 2005.
      assertThat(ymdToUnixDate(1970, 1, 1), is(0));
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2003, 1, 1)),
          is(1L)); // wed
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2004, 1, 1)),
          is(1L)); // thu
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2005, 1, 1)),
          is(53L)); // sat
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2005, 1, 2)),
          is(53L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2005, 12, 31)),
          is(52L)); // sat
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2006, 1, 1)),
          is(52L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2006, 1, 2)),
          is(1L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2006, 12, 31)),
          is(52L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2007, 1, 1)),
          is(1L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2007, 12, 30)),
          is(52L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2007, 12, 31)),
          is(1L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2008, 12, 28)),
          is(52L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2008, 12, 29)),
          is(1L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2008, 12, 30)),
          is(1L)); // tue
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2008, 12, 31)),
          is(1L)); // wen
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2009, 1, 1)),
          is(1L)); // thu
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2009, 12, 31)),
          is(53L)); // thu
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2010, 1, 1)),
          is(53L)); // fri
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2010, 1, 2)),
          is(53L)); // sat
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2010, 1, 3)),
          is(53L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2010, 1, 4)),
          is(1L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2012, 12, 30)),
          is(52L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2012, 12, 31)),
          is(1L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2014, 12, 30)),
          is(1L)); // tue
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(2014, 12, 31)),
          is(1L)); // wen
      assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(1970, 1, 1)),
          is(1L)); // thu
  
      // Based on the rule: The number of the ISO 8601 week-numbering week of the year.
      // By definition, ISO weeks start on Mondays and the first week of a year contains
      // January 4 of that year. In other words, the first Thursday of a year is in
      // week 1 of that year.
      // For that reason 1969-12-31, 1969-12-30 and 1969-12-29 are in the 1-st ISO week of 1970
      assertThat(unixDateExtract(TimeUnitRange.WEEK, -4), is(52L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, -3), is(1L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, -2), is(1L)); // tue
      assertThat(unixDateExtract(TimeUnitRange.WEEK, -1), is(1L)); // wed
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 0), is(1L)); // thu
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 1), is(1L)); // fri
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 2), is(1L)); // sat
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 3), is(1L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 4), is(2L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 7), is(2L)); // thu
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 10), is(2L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 11), is(3L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 359), is(52L)); // sat
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 360), is(52L)); // sun
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 361), is(53L)); // mon
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 364), is(53L)); // thu
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 365), is(53L)); // fri
      assertThat(unixDateExtract(TimeUnitRange.WEEK, 368), is(1L)); // mon
  
      assertThat(unixDateExtract(TimeUnitRange.QUARTER, -1), is(4L));
      assertThat(unixDateExtract(TimeUnitRange.QUARTER, 0), is(1L));
      assertThat(unixDateExtract(TimeUnitRange.QUARTER, 365), is(1L));
      assertThat(unixDateExtract(TimeUnitRange.QUARTER, 366), is(1L));
  
      thereAndBack(1900, 1, 1);
      thereAndBack(1900, 2, 28); // no leap day
      thereAndBack(1900, 3, 1);
      thereAndBack(1901, 1, 1);
      thereAndBack(1901, 2, 28); // no leap day
      thereAndBack(1901, 3, 1);
      thereAndBack(1902, 1, 1);
      thereAndBack(1903, 1, 1);
      thereAndBack(1904, 1, 1);
      thereAndBack(2000, 1, 1);
      thereAndBack(2000, 2, 28);
      thereAndBack(2000, 2, 29); // leap day
      thereAndBack(2000, 3, 1);
      thereAndBack(1964, 1, 1);
      thereAndBack(1964, 2, 28);
      thereAndBack(1964, 2, 29); // leap day
      thereAndBack(1964, 3, 1);
      thereAndBack(1864, 1, 1);
      thereAndBack(1864, 2, 28);
      thereAndBack(1864, 2, 29); // leap day
      thereAndBack(1864, 3, 1);
      thereAndBack(1900, 1, 1);
      thereAndBack(1900, 2, 28);
      thereAndBack(1900, 3, 1);
      thereAndBack(2004, 2, 28);
      thereAndBack(2004, 2, 29); // leap day
      thereAndBack(2004, 3, 1);
      thereAndBack(2005, 2, 28); // no leap day
      thereAndBack(2005, 3, 1);
      thereAndBack(1601, 1, 1);
      // Doesn't work much earlier than 1600 because of leap year differences.
      // Before 1600, does the user expect Gregorian calendar?
      if (false) {
        thereAndBack(1581, 1, 1);
        thereAndBack(1, 1, 1);
      }
  
      // Per PostgreSQL: The first century starts at 0001-01-01 00:00:00 AD,
      // although they did not know it at the time. This definition applies to
      // all Gregorian calendar countries. There is no century number 0, you go
      // from -1 century to 1 century. If you disagree with this, please write
      // your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican.
  
      // The 21st century started on 2001/01/01
      assertThat(
          unixDateExtract(TimeUnitRange.CENTURY, ymdToUnixDate(2001, 1, 1)),
          is(21L));
      assertThat(
          unixDateExtract(TimeUnitRange.CENTURY, ymdToUnixDate(2000, 12, 31)),
          is(20L));
      assertThat(
          unixDateExtract(TimeUnitRange.CENTURY, ymdToUnixDate(1852, 6, 7)),
          is(19L));
      assertThat(
          unixDateExtract(TimeUnitRange.CENTURY, ymdToUnixDate(1, 2, 1)),
          is(1L));
      assertThat(
          unixDateExtract(TimeUnitRange.CENTURY, ymdToUnixDate(1, 1, 1)),
          is(1L));
      assertThat(
          unixDateExtract(TimeUnitRange.CENTURY, ymdToUnixDate(-2, 1, 1)),
          is(-1L));
  
      //The 201st decade started on 2010/01/01. A little bit different but based on
      //https://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
      assertThat(
          unixDateExtract(TimeUnitRange.DECADE, ymdToUnixDate(2010, 1, 1)),
          is(201L));
      assertThat(
          unixDateExtract(TimeUnitRange.DECADE, ymdToUnixDate(2000, 12, 31)),
          is(200L));
      assertThat(
          unixDateExtract(TimeUnitRange.DECADE, ymdToUnixDate(1852, 6, 7)),
          is(185L));
      assertThat(
          unixDateExtract(TimeUnitRange.DECADE, ymdToUnixDate(1, 2, 1)),
          is(0L));
      // TODO: For a small time range around year 1, due to the Gregorian shift,
      // we end up in the wrong decade. Should be 1.
      assertThat(
          unixDateExtract(TimeUnitRange.DECADE, ymdToUnixDate(1, 1, 1)),
          is(0L));
      assertThat(
          unixDateExtract(TimeUnitRange.DECADE, ymdToUnixDate(-2, 1, 1)),
          is(0L));
      assertThat(
          unixDateExtract(TimeUnitRange.DECADE, ymdToUnixDate(-20, 1, 1)),
          is(-2L));
  
      // The 3rd millennium started on 2001/01/01
      assertThat(
          unixDateExtract(TimeUnitRange.MILLENNIUM, ymdToUnixDate(2001, 1, 1)),
          is(3L));
      assertThat(
          unixDateExtract(TimeUnitRange.MILLENNIUM, ymdToUnixDate(2000, 12, 31)),
          is(2L));
      assertThat(
          unixDateExtract(TimeUnitRange.MILLENNIUM, ymdToUnixDate(1852, 6, 7)),
          is(2L));
      assertThat(
          unixDateExtract(TimeUnitRange.MILLENNIUM, ymdToUnixDate(1, 1, 1)),
          is(1L));
      assertThat(
          unixDateExtract(TimeUnitRange.MILLENNIUM, ymdToUnixDate(1, 2, 1)),
          is(1L));
      assertThat(
          unixDateExtract(TimeUnitRange.MILLENNIUM, ymdToUnixDate(-2, 1, 1)),
          is(-1L));
  
      // The ISO 8601 week-numbering year that the date falls in (not applicable
      // to intervals). Each ISO 8601 week-numbering year begins with the Monday
      // of the week containing the 4th of January, so in early January or late
      // December the ISO year may be different from the Gregorian year. See the
      // week field for more information.
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2003, 1, 1)),
          is(2003L)); // wed
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2004, 1, 1)),
          is(2004L)); // thu
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2005, 1, 1)),
          is(2004L)); // sat
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2005, 1, 2)),
          is(2004L)); // sun
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2005, 1, 3)),
          is(2005L)); // mon
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2005, 12, 31)),
          is(2005L)); // sat
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2006, 1, 1)),
          is(2005L)); // sun
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2006, 1, 2)),
          is(2006L)); // mon
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2006, 12, 31)),
          is(2006L)); // sun
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2007, 1, 1)),
          is(2007L)); // mon
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2007, 12, 30)),
          is(2007L)); // sun
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2007, 12, 31)),
          is(2008L)); // mon
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2008, 12, 28)),
          is(2008L)); // sun
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2008, 12, 29)),
          is(2009L)); // mon
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2008, 12, 30)),
          is(2009L)); // tue
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2008, 12, 31)),
          is(2009L)); // wen
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2009, 1, 1)),
          is(2009L)); // thu
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2009, 12, 31)),
          is(2009L)); // thu
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2010, 1, 1)),
          is(2009L)); // fri
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2010, 1, 2)),
          is(2009L)); // sat
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2010, 1, 3)),
          is(2009L)); // sun
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2010, 1, 4)),
          is(2010L)); // mon
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2012, 12, 29)),
          is(2012L)); // sat
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2012, 12, 30)),
          is(2012L)); // sun
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2012, 12, 31)),
          is(2013L)); // mon
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2014, 12, 30)),
          is(2015L)); // tue
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(2014, 12, 31)),
          is(2015L)); // wen
      assertThat(
          unixDateExtract(TimeUnitRange.ISOYEAR, ymdToUnixDate(1970, 1, 1)),
          is(1970L)); // thu
  
      // For date and timestamp values, the number of seconds since 1970-01-01 00:00:00 UTC
      // (can be negative); for interval values, the total number of seconds in the interval
      assertThat(
          unixDateExtract(TimeUnitRange.EPOCH, ymdToUnixDate(2001, 1, 1)),
          is(978_307_200L));
      assertThat(
          unixDateExtract(TimeUnitRange.EPOCH, ymdToUnixDate(1969, 12, 31)),
          is(-86_400L));
      assertThat(
          unixDateExtract(TimeUnitRange.EPOCH, ymdToUnixDate(1970, 1, 1)),
          is(0L));
      assertThat(
          unixDateExtract(TimeUnitRange.EPOCH, ymdToUnixDate(1, 1, 1)),
          is(-62_135_596_800L));
      assertThat(
          unixDateExtract(TimeUnitRange.EPOCH, ymdToUnixDate(1, 2, 1)),
          is(-62_132_918_400L));
      assertThat(
          unixDateExtract(TimeUnitRange.EPOCH, ymdToUnixDate(-2, 1, 1)),
          is(-62_230_291_200L));
    }
  
    @Test public void testUnixDate() {
      int days = DateTimeUtils.dateStringToUnixDate("1500-04-30");
      assertThat(DateTimeUtils.unixDateToString(days), is("1500-04-30"));
      assertThat(
          DateTimeUtils.unixDateToString(
              DateTimeUtils.dateStringToUnixDate(
                  DateTimeUtils.unixDateToString(
                      DateTimeUtils.dateStringToUnixDate(
                          DateTimeUtils.unixDateToString(
                              DateTimeUtils.dateStringToUnixDate("1500-04-30")))))),
          is("1500-04-30"));
  
      final int d1900 = -(70 * 365 + 70 / 4);
      final int century = 100 * 365 + 100 / 4;
      checkDateString("1900-01-01", d1900);
      // +1 because 1800 is not a leap year
      final int d1800 = d1900 - century + 1;
      checkDateString("1800-01-01", d1800);
      final int d1700 = d1800 - century + 1;
      checkDateString("1700-01-01", d1700);
      final int d1600 = d1700 - century;
      checkDateString("1600-01-01", d1600);
      final int d1500 = d1600 - century + 1;
      checkDateString("1500-01-01", d1500);
      final int d1400 = d1500 - century + 1;
      checkDateString("1400-01-01", d1400);
      final int d1300 = d1400 - century + 1;
      checkDateString("1300-01-01", d1300);
      final int d1200 = d1300 - century;
      checkDateString("1200-01-01", d1200);
      final int d1100 = d1200 - century + 1;
      checkDateString("1100-01-01", d1100);
      final int d1000 = d1100 - century + 1;
      checkDateString("1000-01-01", d1000);
      final int d900 = d1000 - century + 1;
      checkDateString("0900-01-01", d900);
      final int d800 = d900 - century;
      checkDateString("0800-01-01", d800);
      final int d700 = d800 - century + 1;
      checkDateString("0700-01-01", d700);
      final int d600 = d700 - century + 1;
      checkDateString("0600-01-01", d600);
      final int d500 = d600 - century + 1;
      checkDateString("0500-01-01", d500);
      final int d400 = d500 - century;
      checkDateString("0400-01-01", d400);
      final int d300 = d400 - century + 1;
      checkDateString("0300-01-01", d300);
      final int d200 = d300 - century + 1;
      checkDateString("0200-01-01", d200);
      final int d100 = d200 - century + 1;
      checkDateString("0100-01-01", d100);
    }
  
    @Test public void testDateConversion() {
      for (int i = 1; i < 4000; ++i) {
        for (int j = 1; j <= 12; ++j) {
          String date = String.format(Locale.ENGLISH, "%04d-%02d-28", i, j);
          assertThat(unixDateToString(ymdToUnixDate(i, j, 28)), is(date));
        }
      }
    }
  
    private void thereAndBack(int year, int month, int day) {
      final int unixDate = ymdToUnixDate(year, month, day);
      final LocalDate javaDate = LocalDate.of(year, month, day); // ref impl
  
      assertThat(unixDateExtract(TimeUnitRange.YEAR, unixDate),
          is((long) year));
      assertThat(unixDateExtract(TimeUnitRange.MONTH, unixDate),
          is((long) month));
      assertThat(unixDateExtract(TimeUnitRange.DAY, unixDate),
          is((long) day));
      final long isoYear = unixDateExtract(TimeUnitRange.ISOYEAR, unixDate);
      assertTrue(isoYear >= year - 1 && isoYear <= year + 1);
      assertThat(isoYear, is((long) javaDate.get(IsoFields.WEEK_BASED_YEAR)));
  
      final long w = unixDateExtract(TimeUnitRange.WEEK, unixDate);
      assertTrue(w >= 1 && w <= 53);
      assertThat(w, is((long) javaDate.get(IsoFields.WEEK_OF_WEEK_BASED_YEAR)));
  
      final long dow = unixDateExtract(TimeUnitRange.DOW, unixDate);
      assertTrue(dow >= 1 && dow <= 7);
      final long iso_dow = unixDateExtract(TimeUnitRange.ISODOW, unixDate);
      assertTrue(iso_dow >= 1 && iso_dow <= 7);
  
      final long isoYearFloor = unixDateFloor(TimeUnitRange.ISOYEAR, unixDate);
      assertThat(unixDateExtract(TimeUnitRange.ISOYEAR, isoYearFloor),
          is(isoYear));
      assertThat(unixDateExtract(TimeUnitRange.ISOYEAR, isoYearFloor - 1),
          is(isoYear - 1));
  
      final long isoYearCeil = unixDateCeil(TimeUnitRange.ISOYEAR, unixDate);
      if (isoYearFloor == unixDate) {
        assertThat(unixDateExtract(TimeUnitRange.ISOYEAR, isoYearCeil),
            is(isoYear));
        assertThat(unixDateExtract(TimeUnitRange.ISOYEAR, isoYearCeil - 1),
            is(isoYear - 1));
      } else {
        assertThat(unixDateExtract(TimeUnitRange.ISOYEAR, isoYearCeil),
            is(isoYear + 1));
        assertThat(unixDateExtract(TimeUnitRange.ISOYEAR, isoYearCeil - 1),
            is(isoYear));
      }
  
      final long doy = unixDateExtract(TimeUnitRange.DOY, unixDate);
      assertTrue(doy >= 1 && doy <= 366);
      final long q = unixDateExtract(TimeUnitRange.QUARTER, unixDate);
      assertTrue(q >= 1 && q <= 4);
      final long d = unixDateExtract(TimeUnitRange.DECADE, unixDate);
      assertThat(d, is((long) year / 10));
      final long c = unixDateExtract(TimeUnitRange.CENTURY, unixDate);
      assertThat(c,
          is(year > 0 ? ((long) year + 99) / 100 : ((long) year - 99) / 100));
      final long m = unixDateExtract(TimeUnitRange.MILLENNIUM, unixDate);
      assertThat(m,
          is(year > 0 ? ((long) year + 999) / 1000 : ((long) year - 999) / 1000));
    }
  
    @Test public void testAddMonths() {
      checkAddMonths(2016, 1, 1, 2016, 2, 1, 1);
      checkAddMonths(2016, 1, 1, 2017, 1, 1, 12);
      checkAddMonths(2016, 1, 1, 2017, 2, 1, 13);
      checkAddMonths(2016, 1, 1, 2015, 1, 1, -12);
      checkAddMonths(2016, 1, 1, 2018, 10, 1, 33);
      checkAddMonths(2016, 1, 31, 2016, 4, 30, 3);
      checkAddMonths(2016, 4, 30, 2016, 7, 30, 3);
      checkAddMonths(2016, 1, 31, 2016, 2, 29, 1);
      checkAddMonths(2016, 3, 31, 2016, 2, 29, -1);
      checkAddMonths(2016, 3, 31, 2116, 3, 31, 1200);
      checkAddMonths(2016, 2, 28, 2116, 2, 28, 1200);
      checkAddMonths(2019, 9, 1, 2020, 3, 1, 6);
      checkAddMonths(2019, 9, 1, 2016, 8, 1, -37);
    }
  
    private void checkAddMonths(int y0, int m0, int d0, int y1, int m1, int d1,
        int months) {
      final int date0 = ymdToUnixDate(y0, m0, d0);
      final long date = addMonths(date0, months);
      final int date1 = ymdToUnixDate(y1, m1, d1);
      assertThat((int) date, is(date1));
  
      assertThat(subtractMonths(date1, date0),
          anyOf(is(months - 1), is(months), is(months + 1)));
      assertThat(subtractMonths(date1 + 1, date0),
          anyOf(is(months), is(months + 1)));
      assertThat(subtractMonths(date1, date0 + 1),
          anyOf(is(months), is(months - 1)));
      assertThat(subtractMonths(d2ts(date1, 1), d2ts(date0, 0)),
          anyOf(is(months - 1), is(months), is(months + 1)));
      assertThat(subtractMonths(d2ts(date1, 0), d2ts(date0, 1)),
          anyOf(is(months - 1), is(months), is(months + 1)));
    }
  
    /** Converts a date (days since epoch) and milliseconds (since midnight)
     * into a timestamp (milliseconds since epoch). */
    private long d2ts(int date, int millis) {
      return date * DateTimeUtils.MILLIS_PER_DAY + millis;
    }
  
    @Test public void testSubtractMonths() {
      assertThat(subtractMonths(
              ymdToUnixDate(2004, 9, 11),
              ymdToUnixDate(2004, 9, 11)), is(0));
      assertThat(subtractMonths(
              ymdToUnixDate(2005, 9, 11),
              ymdToUnixDate(2004, 9, 11)), is(12));
      assertThat(subtractMonths(
              ymdToUnixDate(2006, 9, 11),
              ymdToUnixDate(2004, 9, 11)), is(24));
      assertThat(subtractMonths(
              ymdToUnixDate(2007, 9, 11),
              ymdToUnixDate(2004, 9, 11)), is(36));
      assertThat(subtractMonths(
              ymdToUnixDate(2004, 9, 11),
              ymdToUnixDate(2005, 9, 11)), is(-12));
      assertThat(subtractMonths(
              ymdToUnixDate(2003, 9, 11),
              ymdToUnixDate(2005, 9, 11)), is(-24));
      assertThat(subtractMonths(
              ymdToUnixDate(2005, 2, 28),
              ymdToUnixDate(2004, 2, 28)), is(12));
      assertThat(subtractMonths(
              ymdToUnixDate(2005, 2, 28),
              ymdToUnixDate(2004, 2, 29)), is(11));
      assertThat(subtractMonths(
              ymdToUnixDate(2005, 2, 28),
              ymdToUnixDate(2004, 2, 28)), is(12));
      assertThat(subtractMonths(
              ymdToUnixDate(2005, 3, 28),
              ymdToUnixDate(2004, 3, 29)), is(11));
      assertThat(subtractMonths(
              ymdToUnixDate(2004, 2, 29),
              ymdToUnixDate(2003, 2, 28)), is(12));
      assertThat(subtractMonths(
              ymdToUnixDate(2005, 2, 28),
              ymdToUnixDate(2003, 2, 28)), is(24));
      assertThat(subtractMonths(
              ymdToUnixDate(2001, 10, 10),
              ymdToUnixDate(1999, 9, 11)), is(24));
      assertThat(subtractMonths(
              ymdToUnixDate(2001, 9, 11),
              ymdToUnixDate(1999, 9, 11)), is(24));
      assertThat(subtractMonths(
              ymdToUnixDate(2001, 5, 1),
              ymdToUnixDate(2001, 2, 1)), is(3));
      assertThat(subtractMonths(
              ymdToUnixDate(2000, 2, 29),
              ymdToUnixDate(2000, 3, 28)), is(0));
      assertThat(subtractMonths(
              ymdToUnixDate(2000, 2, 29),
              ymdToUnixDate(1991, 3, 28)), is(107));
    }
  
    @Test public void testUnixTimestamp() {
      assertThat(unixTimestamp(1970, 1, 1, 0, 0, 0), is(0L));
      final long day = 86400000L;
      assertThat(unixTimestamp(1970, 1, 2, 0, 0, 0), is(day));
      assertThat(unixTimestamp(1970, 1, 1, 23, 59, 59), is(86399000L));
  
      // 1900 is not a leap year
      final long y1900 = -2203977600000L;
      assertThat(unixTimestamp(1900, 2, 28, 0, 0, 0), is(y1900));
      assertThat(unixTimestamp(1900, 3, 1, 0, 0, 0), is(y1900 + day));
  
      // 2000 is a leap year
      final long y2k = 951696000000L;
      assertThat(unixTimestamp(2000, 2, 28, 0, 0, 0), is(y2k));
      assertThat(unixTimestamp(2000, 2, 29, 0, 0, 0), is(y2k + day));
      assertThat(unixTimestamp(2000, 3, 1, 0, 0, 0), is(y2k + day + day));
  
      // 2016 is a leap year
      final long y2016 = 1456617600000L;
      assertThat(unixTimestamp(2016, 2, 28, 0, 0, 0), is(y2016));
      assertThat(unixTimestamp(2016, 2, 29, 0, 0, 0), is(y2016 + day));
      assertThat(unixTimestamp(2016, 3, 1, 0, 0, 0), is(y2016 + day + day));
    }
  
    @Test public void testParse() {
      final SimpleDateFormat formatD =
          new SimpleDateFormat(DateTimeUtils.DATE_FORMAT_STRING, Locale.ROOT);
      final Calendar c =
          DateTimeUtils.parseDateFormat("1234-04-12", formatD,
              DateTimeUtils.UTC_ZONE);
      assertThat(c, notNullValue());
      assertThat(c.get(Calendar.YEAR), is(1234));
      assertThat(c.get(Calendar.MONTH), is(Calendar.APRIL));
      assertThat(c.get(Calendar.DAY_OF_MONTH), is(12));
  
      final SimpleDateFormat formatTs =
          new SimpleDateFormat(DateTimeUtils.TIMESTAMP_FORMAT_STRING,
              Locale.ROOT);
      final DateTimeUtils.PrecisionTime pt =
          DateTimeUtils.parsePrecisionDateTimeLiteral(
              "1234-04-12 01:23:45.06789", formatTs, DateTimeUtils.UTC_ZONE, -1);
      assertThat(pt, notNullValue());
      assertThat(pt.getCalendar().get(Calendar.YEAR), is(1234));
      assertThat(pt.getCalendar().get(Calendar.MONTH), is(Calendar.APRIL));
      assertThat(pt.getCalendar().get(Calendar.DAY_OF_MONTH), is(12));
      assertThat(pt.getCalendar().get(Calendar.HOUR_OF_DAY), is(1));
      assertThat(pt.getCalendar().get(Calendar.MINUTE), is(23));
      assertThat(pt.getCalendar().get(Calendar.SECOND), is(45));
      assertThat(pt.getCalendar().get(Calendar.MILLISECOND), is(67));
      assertThat(pt.getFraction(), is("06789"));
      assertThat(pt.getPrecision(), is(5));
  
      // as above, but limit to 2 fractional digits
      final DateTimeUtils.PrecisionTime pt2 =
          DateTimeUtils.parsePrecisionDateTimeLiteral(
              "1234-04-12 01:23:45.06789", formatTs, DateTimeUtils.UTC_ZONE, 2);
      assertThat(pt2, notNullValue());
      assertThat(pt2.getCalendar().get(Calendar.MILLISECOND), is(60));
      assertThat(pt2.getFraction(), is("06"));
    }
  
    @Test public void testUnixDateFloorCeil() {
      final long y1001 = ymdToUnixDate(1001, 1, 1);
      final long y1801 = -(169 * 365 + 169 / 4 - 1);
      final long y1890 = -(80 * 365 + 80 / 4 - 1);
      final long y1900 = -(70 * 365 + 70 / 4);
      final long y1899 = y1900 - 365;
      final long y1901 = y1900 + 365;
      final long y1902 = y1901 + 365;
      final long y1903 = y1902 + 365;
      final long y1904 = y1903 + 365;
      final long y1905 = y1904 + 366;
      final long y1906 = y1905 + 365;
      final long y1907 = y1903 + 4 * 365 + 1;
      final long y1910 = -(60 * 365 + 60 / 4);
      final long y2001 = 31 * 365 + 31 / 4 + 1;
      final long y1900_0102 = y1900 + 1;
      final long y1900_0506 = y1900 - 1 + 31 + 28 + 31 + 30 + 6; // sunday
      final long y1900_0512 = y1900 - 1 + 31 + 28 + 31 + 30 + 12; // saturday
      final long y1900_0513 = y1900 - 1 + 31 + 28 + 31 + 30 + 13; // sunday
      final long y1900_0514 = y1900 - 1 + 31 + 28 + 31 + 30 + 14; // monday
      final long y1900_0520 = y1900 - 1 + 31 + 28 + 31 + 30 + 20; // sunday
      final long y1900_0401 = y1900 - 1 + 31 + 28 + 31 + 1;
      final long y1900_0501 = y1900 - 1 + 31 + 28 + 31 + 30 + 1;
      final long y1900_0601 = y1900 - 1 + 31 + 28 + 31 + 30 + 31 + 1;
      final long y1900_0701 = y1900 - 1 + 31 + 28 + 31 + 30 + 31 + 30 + 1;
      final long y1900_1001 = y1900 - 1 + 31 + 28 + 31 + 30 + 31 + 30 + 31 + 31 + 30 + 1;
      final long y1900_1002 = y1900 - 1 + 31 + 28 + 31 + 30 + 31 + 30 + 31 + 31 + 30 + 2;
      final long y1900_1231 = y1901 - 1;
      final long y1902_1229 = y1903 - 3;
      final long y1904_0103 = y1904 + 2;
      final long y1904_0104 = y1904 + 3;
      checkDateString("1801-01-01", (int) y1801);
      checkDateString("1900-01-01", (int) y1900);
      checkDateString("1900-01-02", (int) y1900_0102);
      checkDateString("1899-01-01", (int) y1899);
      checkDateString("1901-01-01", (int) y1901);
      checkDateString("1902-01-01", (int) y1902);
      checkDateString("1902-12-29", (int) y1902_1229);
      checkDateString("1903-01-01", (int) y1903);
      checkDateString("1904-01-01", (int) y1904);
      checkDateString("1904-01-03", (int) y1904_0103);
      checkDateString("1904-01-04", (int) y1904_0104);
      checkDateString("1905-01-01", (int) y1905);
      checkDateString("1906-01-01", (int) y1906);
      checkDateString("1907-01-01", (int) y1907);
      checkDateString("1910-01-01", (int) y1910);
      checkDateString("2001-01-01", (int) y2001);
      assertThat(unixDateFloor(TimeUnitRange.MILLENNIUM, y1907), is(y1001));
      assertThat(unixDateCeil(TimeUnitRange.MILLENNIUM, y1907), is(y2001));
      assertThat(unixDateFloor(TimeUnitRange.CENTURY, y1899), is(y1801));
      assertThat(unixDateCeil(TimeUnitRange.CENTURY, y1899), is(y1901));
      assertThat(unixDateFloor(TimeUnitRange.DECADE, y1899), is(y1890));
      assertThat(unixDateFloor(TimeUnitRange.DECADE, y1900_0701), is(y1900));
      assertThat(unixDateFloor(TimeUnitRange.DECADE, y1907), is(y1900));
      assertThat(unixDateCeil(TimeUnitRange.DECADE, y1899), is(y1900));
      assertThat(unixDateCeil(TimeUnitRange.DECADE, y1900_0701), is(y1910));
      assertThat(unixDateCeil(TimeUnitRange.DECADE, y1907), is(y1910));
      assertThat(unixDateFloor(TimeUnitRange.YEAR, y1900_0102), is(y1900));
      assertThat(unixDateCeil(TimeUnitRange.YEAR, y1900_0102), is(y1901));
      assertThat(unixDateFloor(TimeUnitRange.QUARTER, y1900_0514), is(y1900_0401));
      assertThat(unixDateCeil(TimeUnitRange.QUARTER, y1900_0514), is(y1900_0701));
      assertThat(unixDateFloor(TimeUnitRange.QUARTER, y1900_1001), is(y1900_1001));
      assertThat(unixDateCeil(TimeUnitRange.QUARTER, y1900_1001), is(y1900_1001));
      assertThat(unixDateFloor(TimeUnitRange.QUARTER, y1900_1002), is(y1900_1001));
      assertThat(unixDateCeil(TimeUnitRange.QUARTER, y1900_1002), is(y1901));
      assertThat(unixDateFloor(TimeUnitRange.MONTH, y1900_0514), is(y1900_0501));
      assertThat(unixDateCeil(TimeUnitRange.MONTH, y1900_0514), is(y1900_0601));
      assertThat(unixDateFloor(TimeUnitRange.WEEK, y1900_0514), is(y1900_0513));
      assertThat(unixDateCeil(TimeUnitRange.WEEK, y1900_0514), is(y1900_0520));
      assertThat(unixDateFloor(TimeUnitRange.WEEK, y1900_0514), is(y1900_0513));
      assertThat(unixDateCeil(TimeUnitRange.WEEK, y1900_0514), is(y1900_0520));
      assertThat(unixDateFloor(TimeUnitRange.WEEK, y1900_0513), is(y1900_0513));
      assertThat(unixDateCeil(TimeUnitRange.WEEK, y1900_0513), is(y1900_0513));
      assertThat(unixDateFloor(TimeUnitRange.WEEK, y1900_0512), is(y1900_0506));
      assertThat(unixDateCeil(TimeUnitRange.WEEK, y1900_0512), is(y1900_0513));
      assertThat(unixDateFloor(TimeUnitRange.DAY, y1900_0514), is(y1900_0514));
      assertThat(unixDateCeil(TimeUnitRange.DAY, y1900_0514), is(y1900_0514));
  
      // 1900/01/01 is a Monday, therefore the first day of the ISO year;
      // the next ISO year starts on 1900/12/31
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1900), is(y1900));
      assertThat(unixDateCeil(TimeUnitRange.ISOYEAR, y1900), is(y1900));
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1900_0102), is(y1900));
      assertThat(unixDateCeil(TimeUnitRange.ISOYEAR, y1900_0102), is(y1900_1231));
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1900_1231), is(y1900_1231));
      assertThat(unixDateCeil(TimeUnitRange.ISOYEAR, y1900_1231), is(y1900_1231));
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1901), is(y1900_1231));
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1903), is(y1902_1229));
  
      // 1904/01/01 is a Friday, the next ISO year starts on Mon 1904/01/04,
      // the previous ISO year started on 1902/12/29.
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1904), is(y1902_1229));
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1904_0103), is(y1902_1229));
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1904_0104), is(y1904_0104));
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1905), is(y1904_0104));
  
      // 1906/01/01 is a Monday.
      assertThat(unixDateFloor(TimeUnitRange.ISOYEAR, y1906), is(y1906));
    }
  
    /**
     * Test that a date in the local time zone converts to a unix timestamp in UTC.
     */
    @Test public void testSqlDateToUnixDateWithLocalTimeZone() {
      assertThat(sqlDateToUnixDate(java.sql.Date.valueOf("1970-01-01"), CALENDAR), is(0));
      assertThat(sqlDateToUnixDate(java.sql.Date.valueOf("1500-04-30"), CALENDAR),
          is(dateStringToUnixDate("1500-04-30")));
    }
  
    /**
     * Test rounding up dates before the unix epoch (1970-01-01).
     *
     * <p>Calcite depends on this rounding behaviour for some of its tests. This ensures that
     * {@code new Date(0L)} will return "1970-01-01" regardless of the default timezone.
     */
    @Test public void testSqlDateToUnixDateRounding() {
      final java.sql.Date partial = java.sql.Date.valueOf("1969-12-31");
      partial.setTime(partial.getTime() + 1);
      assertThat(sqlDateToUnixDate(partial, CALENDAR), is(0));
    }
  
    /**
     * Test that no time zone conversion happens when the given calendar is {@code null}.
     */
    @Test public void testSqlDateToUnixDateWithNullCalendar() {
      assertThat(sqlDateToUnixDate(new java.sql.Date(0L), (Calendar) null), is(0));
      assertThat(sqlDateToUnixDate(new java.sql.Date(-MILLIS_PER_DAY), (Calendar) null), is(-1));
  
      final Calendar julianCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
      julianCal.set(1500, Calendar.APRIL, 30, 0, 0, 0);
      julianCal.set(Calendar.MILLISECOND, 0);
      assertThat(
          sqlDateToUnixDate(new java.sql.Date(julianCal.getTimeInMillis()), (Calendar) null),
          is(-171545 /* 1500-04-30 in ISO calendar */));
    }
  
    /**
     * Test using a custom {@link Calendar} to calculate the unix timestamp. Dates created by a
     * {@link Calendar} should be converted to a unix date in the given time zone. Dates created by a
     * {@link java.sql.Date} method should be converted relative to the local time and not UTC.
     */
    @Test public void testSqlDateToUnixDateWithCustomCalendar() {
      final java.sql.Date epoch = java.sql.Date.valueOf("1970-01-01");
  
      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(sqlDateToUnixDate(new java.sql.Date(utcCal.getTimeInMillis()), utcCal), is(0));
  
      final TimeZone minusDayZone = TimeZone.getDefault();
      minusDayZone.setRawOffset((int) (minusDayZone.getRawOffset() - MILLIS_PER_DAY));
      final Calendar minusDayCal = Calendar.getInstance(minusDayZone, Locale.ROOT);
      assertThat(sqlDateToUnixDate(epoch, minusDayCal), is(-1));
  
      final TimeZone plusDayZone = TimeZone.getDefault();
      plusDayZone.setRawOffset((int) (plusDayZone.getRawOffset() + MILLIS_PER_DAY));
      final Calendar plusDayCal = Calendar.getInstance(plusDayZone, Locale.ROOT);
      assertThat(sqlDateToUnixDate(epoch, plusDayCal), is(1));
    }
  
    /**
     * Test calendar conversion from the standard Gregorian calendar used by {@code java.sql} and the
     * proleptic Gregorian calendar used by unix timestamps.
     */
    @Test public void testSqlDateToUnixDateWithGregorianShift() {
      assertThat(sqlDateToUnixDate(java.sql.Date.valueOf("1582-10-04"), CALENDAR),
          is(dateStringToUnixDate("1582-10-04")));
      assertThat(sqlDateToUnixDate(java.sql.Date.valueOf("1582-10-05"), CALENDAR),
          is(dateStringToUnixDate("1582-10-15")));
      assertThat(sqlDateToUnixDate(java.sql.Date.valueOf("1582-10-15"), CALENDAR),
          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 UTC 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 only uses the UTC Time zone.
     */
    @Test public void testSqlDateToUnixDateWithAnsiDateRange() {
      final Calendar ansiCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
  
      // Test 0001-01-02 UTC to avoid issues with Java modifying 0001-01-01 when outside AD
      ansiCal.set(1, Calendar.JANUARY, 2, 0, 0, 0);
      ansiCal.set(Calendar.MILLISECOND, 0);
      assertThat("Converts 0001-01-02 from SQL to unix date",
          sqlDateToUnixDate(new java.sql.Date(ansiCal.getTimeInMillis()), ansiCal),
          is(dateStringToUnixDate("0001-01-02")));
  
      // Test remaining years 0002-01-01 to 9999-01-01
      ansiCal.set(Calendar.DATE, 1);
      for (int i = 2; i <= 9999; ++i) {
        final String str = String.format(Locale.ROOT, "%04d-01-01", i);
        ansiCal.set(Calendar.YEAR, i);
        assertThat("Converts '" + str + "' from SQL to unix date",
            sqlDateToUnixDate(new java.sql.Date(ansiCal.getTimeInMillis()), ansiCal),
            is(dateStringToUnixDate(str)));
      }
  
      // Test end of range 9999-12-31
      ansiCal.set(9999, Calendar.DECEMBER, 31, 0, 0, 0);
      ansiCal.set(Calendar.MILLISECOND, 0);
      assertThat("Converts 9999-12-31 from SQL to unix date",
          sqlDateToUnixDate(new java.sql.Date(ansiCal.getTimeInMillis()), ansiCal),
          is(dateStringToUnixDate("9999-12-31")));
    }
  
    /**
     * Test that a unix timestamp converts to a date in the local time zone.
     */
    @Test public void testUnixDateToSqlDateWithLocalTimeZone() {
      assertThat(unixDateToSqlDate(0, CALENDAR), is(java.sql.Date.valueOf("1970-01-01")));
      assertThat(unixDateToSqlDate(dateStringToUnixDate("1500-04-30"), CALENDAR),
          is(java.sql.Date.valueOf("1500-04-30")));
    }
  
    /**
     * Test that no time zone conversion happens when the given calendar is {@code null}.
     */
    @Test public void testUnixDateToSqlDateWithNullCalendar() {
      assertThat(unixDateToSqlDate(0, null), is(new java.sql.Date(0L)));
      assertThat(unixDateToSqlDate(-1, null), is(new java.sql.Date(-MILLIS_PER_DAY)));
  
      final Calendar julianCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
      julianCal.set(1500, Calendar.APRIL, 30, 0, 0, 0);
      julianCal.set(Calendar.MILLISECOND, 0);
      assertThat(
          unixDateToSqlDate(-171545 /* 1500-04-30 in ISO calendar */, null),
          is(new java.sql.Date(julianCal.getTimeInMillis())));
    }
  
    /**
     * Test using a custom {@link Calendar} to calculate the SQL date. Dates created by a
     * {@link Calendar} should be converted to a SQL date in the given time zone. Otherwise, unix
     * dates should be converted relative to the local time and not UTC.
     */
    @Test public void testUnixDateToSqlDateWithCustomCalendar() {
      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(unixDateToSqlDate(0, utcCal).getTime(), is(utcCal.getTimeInMillis()));
  
      final TimeZone minusFiveZone = TimeZone.getDefault();
      minusFiveZone.setRawOffset((int) (minusFiveZone.getOffset(0L) - 5 * MILLIS_PER_HOUR));
      final Calendar minusFiveCal = Calendar.getInstance(minusFiveZone, Locale.ROOT);
      assertThat(unixDateToSqlDate(0, minusFiveCal).toString(), is("1970-01-01"));
  
      final TimeZone plusFiveZone = TimeZone.getDefault();
      plusFiveZone.setRawOffset((int) (plusFiveZone.getOffset(0L) + 5 * MILLIS_PER_HOUR));
      final Calendar plusFiveCal = Calendar.getInstance(plusFiveZone, Locale.ROOT);
      assertThat(unixDateToSqlDate(0, plusFiveCal).toString(), is("1969-12-31"));
    }
  
    /**
     * Test calendar conversion from the standard Gregorian calendar used by {@code java.sql} and the
     * proleptic Gregorian calendar used by unix timestamps.
     */
    @Test public void testUnixDateToSqlDateWithGregorianShift() {
      assertThat(unixDateToSqlDate(dateStringToUnixDate("1582-10-04"), CALENDAR),
          is(java.sql.Date.valueOf("1582-10-04")));
      assertThat(unixDateToSqlDate(dateStringToUnixDate("1582-10-05"), CALENDAR),
          is(java.sql.Date.valueOf("1582-10-15")));
      assertThat(unixDateToSqlDate(dateStringToUnixDate("1582-10-15"), CALENDAR),
          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 public void testUnixDateToSqlDateWithAnsiDateRange() {
      for (int i = 1; i <= 9999; ++i) {
        final String str = String.format(Locale.ROOT, "%04d-01-01", i);
        assertThat(unixDateToSqlDate(dateStringToUnixDate(str), CALENDAR),
            is(java.sql.Date.valueOf(str)));
      }
  
      assertThat(unixDateToSqlDate(dateStringToUnixDate("9999-12-31"), CALENDAR),
          is(java.sql.Date.valueOf("9999-12-31")));
    }
  
    /**
     * Test that a {@link java.util.Date} in the local time zone converts to a unix timestamp in UTC.
     */
    @Test public void testUtilDateToUnixTimestampWithLocalTimeZone() {
      final Date unixEpoch = new Date(-CALENDAR.getTimeZone().getOffset(0L));
      assertThat(utilDateToUnixTimestamp(unixEpoch, CALENDAR), is(0L));
  
      final long currentTime = System.currentTimeMillis();
      final Date currentDate = new Date(currentTime);
      assertThat(utilDateToUnixTimestamp(currentDate, CALENDAR),
          is(currentTime + CALENDAR.getTimeZone().getOffset(currentTime)));
    }
  
    /**
     * Test that no time zone conversion happens when the given calendar is {@code null}.
     */
    @Test public void testUtilDateToUnixTimestampWithNullCalendar() {
      assertThat(utilDateToUnixTimestamp(new Date(0L), (Calendar) null),
          is(0L));
  
      final Calendar julianCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
      julianCal.set(1500, Calendar.APRIL, 30, 0, 0, 0);
      julianCal.set(Calendar.MILLISECOND, 0);
      assertThat(
          utilDateToUnixTimestamp(new Date(julianCal.getTimeInMillis()), (Calendar) null),
          is(-171545 * MILLIS_PER_DAY /* 1500-04-30 in ISO calendar */));
    }
  
    /**
     * Test using a custom {@link Calendar} to calculate the unix timestamp. Dates created by a
     * {@link Calendar} should be converted to a unix date in the given time zone.
     */
    @Test public void testUtilDateToUnixTimestampWithCustomCalendar() {
      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(utilDateToUnixTimestamp(new Date(utcCal.getTimeInMillis()), utcCal), is(0L));
    }
  
    /**
     * Test that a unix timestamp converts to a date in the local time zone.
     */
    @Test public void testUnixTimestampToUtilDateWithLocalTimeZone() {
      final Date unixEpoch = new Date(-CALENDAR.getTimeZone().getOffset(0L));
      assertThat(unixTimestampToUtilDate(0L, CALENDAR), is(unixEpoch));
  
      final long currentTime = System.currentTimeMillis();
      final Date currentDate = new Date(currentTime);
      assertThat(unixTimestampToUtilDate(
              currentTime + CALENDAR.getTimeZone().getOffset(currentTime),
              CALENDAR),
          is(currentDate));
    }
  
    /**
     * Test that no time zone conversion happens when the given calendar is {@code null}.
     */
    @Test public void testUnixTimestampToUtilDateWithNullCalendar() {
      assertThat(unixTimestampToUtilDate(0L, null), is(new Date(0L)));
  
      final Calendar julianCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
      julianCal.set(1500, Calendar.APRIL, 30, 0, 0, 0);
      julianCal.set(Calendar.MILLISECOND, 0);
      assertThat(
          unixTimestampToUtilDate(-171545 * MILLIS_PER_DAY /* 1500-04-30 in ISO calendar */, null),
          is(new Date(julianCal.getTimeInMillis())));
    }
  
    /**
     * Test using a custom {@link Calendar} to calculate the {@link Date}. Dates created by a
     * {@link Calendar} should be converted to a {@link Date} in the given time zone.
     */
    @Test public void testUnixTimestampToUtilDateWithCustomCalendar() {
      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(unixTimestampToUtilDate(0, utcCal).getTime(), is(utcCal.getTimeInMillis()));
    }
  
    /**
     * Test that a time in the local time zone converts to a unix time in UTC.
     */
    @Test public void testSqlTimeToUnixTimeWithLocalTimeZone() {
      assertEquals(0, sqlTimeToUnixTime(Time.valueOf("00:00:00"), CALENDAR));
      assertEquals(MILLIS_PER_DAY - MILLIS_PER_SECOND,
          sqlTimeToUnixTime(Time.valueOf("23:59:59"), CALENDAR));
    }
  
    /**
     * Test that no time zone conversion happens when the given calendar is {@code null}.
     */
    @Test public void testSqlTimeToUnixTimeWithNullCalendar() {
      assertEquals(0L, sqlTimeToUnixTime(new Time(0L), (Calendar) null));
  
      final long endOfDay = MILLIS_PER_DAY - MILLIS_PER_SECOND;
      assertEquals(endOfDay, sqlTimeToUnixTime(new Time(endOfDay), (Calendar) null));
    }
  
    /**
     * Test using a custom {@link Calendar} to calculate the unix time. Times created by a
     * {@link Calendar} should be converted to a unix time in the given time zone. Times created by a
     * {@link Time} method should be converted relative to the local time and not UTC.
     */
    @Test public void testSqlTimeToUnixTimeWithCustomCalendar() {
      final Time epoch = Time.valueOf("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(sqlTimeToUnixTime(new Time(utcCal.getTimeInMillis()), utcCal), is(0));
  
      final TimeZone minusFiveZone = TimeZone.getDefault();
      minusFiveZone.setRawOffset((int) (minusFiveZone.getRawOffset() - 5 * MILLIS_PER_HOUR));
      final Calendar minusFiveCal = Calendar.getInstance(minusFiveZone, Locale.ROOT);
      assertEquals(19 * MILLIS_PER_HOUR, sqlTimeToUnixTime(epoch, minusFiveCal));
  
      final TimeZone plusFiveZone = TimeZone.getDefault();
      plusFiveZone.setRawOffset((int) (plusFiveZone.getRawOffset() + 5 * MILLIS_PER_HOUR));
      final Calendar plusFiveCal = Calendar.getInstance(plusFiveZone, Locale.ROOT);
      assertEquals(5 * MILLIS_PER_HOUR, sqlTimeToUnixTime(epoch, plusFiveCal));
    }
  
    /**
     * Test that a unix time converts to a SQL time in the local time zone.
     */
    @Test public void testUnixTimeToSqlTimeWithLocalTimeZone() {
      assertThat(unixTimeToSqlTime(0, CALENDAR), is(Time.valueOf("00:00:00")));
      assertThat(unixTimeToSqlTime((int) (MILLIS_PER_DAY - MILLIS_PER_SECOND), CALENDAR),
          is(Time.valueOf("23:59:59")));
    }
  
    /**
     * Test that no time zone conversion happens when the given calendar is {@code null}.
     */
    @Test public void testUnixTimeToSqlTimeWithNullCalendar() {
      assertThat(unixTimeToSqlTime(0, null), is(new Time(0L)));
  
      final int endOfDay = (int) (MILLIS_PER_DAY - MILLIS_PER_SECOND);
      assertThat(unixTimeToSqlTime(endOfDay, null), is(new Time(endOfDay)));
    }
  
    /**
     * Test using a custom {@link Calendar} to calculate the SQL time. Times created by a
     * {@link Calendar} should be converted to a SQL time in the given time zone. Otherwise, unix
     * times should be converted relative to the local time and not UTC.
     */
    @Test public void testUnixTimeToSqlTimeWithCustomCalendar() {
      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(unixTimeToSqlTime(0, utcCal).getTime(), is(utcCal.getTimeInMillis()));
  
      final TimeZone minusFiveZone = TimeZone.getDefault();
      minusFiveZone.setRawOffset((int) (minusFiveZone.getRawOffset() - 5 * MILLIS_PER_HOUR));
      final Calendar minusFiveCal = Calendar.getInstance(minusFiveZone, Locale.ROOT);
      assertThat(unixTimeToSqlTime(0, minusFiveCal).toString(), is("05:00:00"));
  
      final TimeZone plusFiveZone = TimeZone.getDefault();
      plusFiveZone.setRawOffset((int) (plusFiveZone.getRawOffset() + 5 * MILLIS_PER_HOUR));
      final Calendar plusFiveCal = Calendar.getInstance(plusFiveZone, Locale.ROOT);
      assertThat(unixTimeToSqlTime(0, plusFiveCal).toString(), is("19:00:00"));
    }
  
    /**
     * Test that a timestamp in the local time zone converts to a unix timestamp in UTC.
     */
    @Test public void testSqlTimestampToUnixTimestampWithLocalTimeZone() {
      assertThat(sqlTimestampToUnixTimestamp(Timestamp.valueOf("1970-01-01 00:00:00"), CALENDAR),
          is(0L));
      assertThat(sqlTimestampToUnixTimestamp(Timestamp.valueOf("2014-09-30 15:28:27.356"), CALENDAR),
          is(timestampStringToUnixDate("2014-09-30 15:28:27.356")));
      assertThat(sqlTimestampToUnixTimestamp(Timestamp.valueOf("1500-04-30 12:00:00.123"), CALENDAR),
          is(timestampStringToUnixDate("1500-04-30 12:00:00.123")));
    }
  
    /**
     * Test that no time zone conversion happens when the given calendar is {@code null}.
     */
    @Test public void testSqlTimestampToUnixTimestampWithNullCalendar() {
      assertThat(sqlTimestampToUnixTimestamp(new Timestamp(0L), (Calendar) null), is(0L));
  
      final Calendar julianCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
      julianCal.set(1500, Calendar.APRIL, 30, 0, 0, 0);
      julianCal.set(Calendar.MILLISECOND, 0);
      assertThat(
          sqlTimestampToUnixTimestamp(new Timestamp(julianCal.getTimeInMillis()), (Calendar) null),
          is(-171545 * MILLIS_PER_DAY /* 1500-04-30 in ISO calendar */));
    }
  
    /**
     * Test using a custom {@link Calendar} 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 public void testSqlTimestampToUnixTimestampWithCustomCalendar() {
      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(sqlTimestampToUnixTimestamp(new Timestamp(utcCal.getTimeInMillis()), utcCal),
          is(0L));
  
      final TimeZone minusFiveZone = TimeZone.getDefault();
      minusFiveZone.setRawOffset((int) (minusFiveZone.getRawOffset() - 5 * MILLIS_PER_HOUR));
      final Calendar minusFiveCal = Calendar.getInstance(minusFiveZone, Locale.ROOT);
      assertThat(sqlTimestampToUnixTimestamp(epoch, minusFiveCal),
          is(-5 * MILLIS_PER_HOUR));
  
      final TimeZone plusFiveZone = TimeZone.getDefault();
      plusFiveZone.setRawOffset((int) (plusFiveZone.getRawOffset() + 5 * MILLIS_PER_HOUR));
      final Calendar plusFiveCal = Calendar.getInstance(plusFiveZone, Locale.ROOT);
      assertThat(sqlTimestampToUnixTimestamp(epoch, plusFiveCal),
          is(5 * MILLIS_PER_HOUR));
    }
  
    /**
     * Test calendar conversion from the standard Gregorian calendar used by {@code java.sql} and the
     * proleptic Gregorian calendar used by unix timestamps.
     */
    @Test public void testSqlTimestampToUnixTimestampWithGregorianShift() {
      assertThat(sqlTimestampToUnixTimestamp(Timestamp.valueOf("1582-10-05 00:00:00"), CALENDAR),
          is(timestampStringToUnixDate("1582-10-15 00:00:00")));
      assertThat(sqlTimestampToUnixTimestamp(Timestamp.valueOf("1582-10-04 00:00:00"), CALENDAR),
          is(timestampStringToUnixDate("1582-10-04 00:00:00")));
      assertThat(sqlTimestampToUnixTimestamp(Timestamp.valueOf("1582-10-15 00:00:00"), CALENDAR),
          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 UTC 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 only uses the UTC Time zone.
     */
    @Test public void testSqlTimestampToUnixTimestampWithAnsiDateRange() {
      final Calendar ansiCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
  
      // Test 0001-01-02 UTC to avoid issues with Java modifying 0001-01-01 when outside AD
      ansiCal.set(1, Calendar.JANUARY, 2, 0, 0, 0);
      ansiCal.set(Calendar.MILLISECOND, 0);
      assertThat("Converts 0001-01-02 from SQL to unix timestamp",
          sqlTimestampToUnixTimestamp(new Timestamp(ansiCal.getTimeInMillis()), ansiCal),
          is(timestampStringToUnixDate("0001-01-02 00:00:00")));
  
      // Test remaining years 0002-01-01 to 9999-01-01
      ansiCal.set(Calendar.DATE, 1);
      for (int i = 2; i <= 9999; ++i) {
        final String str = String.format(Locale.ROOT, "%04d-01-01 00:00:00", i);
        ansiCal.set(Calendar.YEAR, i);
        assertThat("Converts '" + str + "' from SQL to unix timestamp",
            sqlTimestampToUnixTimestamp(new Timestamp(ansiCal.getTimeInMillis()), ansiCal),
            is(timestampStringToUnixDate(str)));
      }
  
      // Test end of range 9999-12-31
      ansiCal.set(9999, Calendar.DECEMBER, 31, 0, 0, 0);
      ansiCal.set(Calendar.MILLISECOND, 0);
      assertThat("Converts 9999-12-31 from SQL to unix date",
          sqlTimestampToUnixTimestamp(new Timestamp(ansiCal.getTimeInMillis()), ansiCal),
          is(timestampStringToUnixDate("9999-12-31 00:00:00")));
    }
  
    /**
     * Test that a unix timestamp converts to a SQL timestamp in the local time zone.
     */
    @Test public void testUnixTimestampToSqlTimestampWithLocalTimeZone() {
      assertThat(unixTimestampToSqlTimestamp(0L, CALENDAR),
          is(Timestamp.valueOf("1970-01-01 00:00:00.0")));
      assertThat(unixTimestampToSqlTimestamp(1412090907356L, CALENDAR),
          is(Timestamp.valueOf("2014-09-30 15:28:27.356")));
      assertThat(unixTimestampToSqlTimestamp(-14821444799877L, CALENDAR),
          is(Timestamp.valueOf("1500-04-30 12:00:00.123")));
      assertThat(unixTimestampToSqlTimestamp(
              timestampStringToUnixDate("1500-04-30 12:00:00.123"),
              CALENDAR),
          is(Timestamp.valueOf("1500-04-30 12:00:00.123")));
    }
  
    /**
     * Test that no time zone conversion happens when the given calendar is {@code null}.
     */
    @Test public void testUnixTimestampToSqlTimestampWithNullCalendar() {
      assertThat(unixTimestampToSqlTimestamp(0L, null), is(new Timestamp(0L)));
  
      final Calendar julianCal = Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
      julianCal.set(1500, Calendar.APRIL, 30, 0, 0, 0);
      julianCal.set(Calendar.MILLISECOND, 0);
      assertThat(
          unixTimestampToSqlTimestamp(
              -171545 * MILLIS_PER_DAY /* 1500-04-30 in ISO calendar */,
              null),
          is(new Timestamp(julianCal.getTimeInMillis())));
    }
  
    /**
     * Test using a custom {@link Calendar} to calculate the SQL timestamp. Timestamps created by a
     * {@link Calendar} should be converted to a SQL timestamp in the given time zone. Otherwise,
     * unix timestamps should be converted relative to the local time and not UTC.
     */
    @Test public void testUnixTimestampToSqlTimestampWithCustomCalendar() {
      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(unixTimestampToSqlTimestamp(0L, utcCal).getTime(), is(utcCal.getTimeInMillis()));
  
      final TimeZone minusFiveZone = TimeZone.getDefault();
      minusFiveZone.setRawOffset((int) (minusFiveZone.getRawOffset() - 5 * MILLIS_PER_HOUR));
      final Calendar minusFiveCal = Calendar.getInstance(minusFiveZone, Locale.ROOT);
      assertThat(unixTimestampToSqlTimestamp(0L, minusFiveCal),
          is(Timestamp.valueOf("1970-01-01 05:00:00")));
  
      final TimeZone plusFiveZone = TimeZone.getDefault();
      plusFiveZone.setRawOffset((int) (plusFiveZone.getRawOffset() + 5 * MILLIS_PER_HOUR));
      final Calendar plusFiveCal = Calendar.getInstance(plusFiveZone, Locale.ROOT);
      assertThat(unixTimestampToSqlTimestamp(0L, plusFiveCal),
          is(Timestamp.valueOf("1969-12-31 19:00:00")));
    }
  
    /**
     * Test calendar conversion from the standard Gregorian calendar used by {@code java.sql} and the
     * proleptic Gregorian calendar used by unix timestamps.
     */
    @Test public void testUnixTimestampToSqlTimestampWithGregorianShift() {
      assertThat(
          unixTimestampToSqlTimestamp(
              timestampStringToUnixDate("1582-10-04 00:00:00"),
              CALENDAR),
          is(Timestamp.valueOf("1582-10-04 00:00:00.0")));
      assertThat(
          unixTimestampToSqlTimestamp(
              timestampStringToUnixDate("1582-10-05 00:00:00"),
              CALENDAR),
          is(Timestamp.valueOf("1582-10-15 00:00:00.0")));
      assertThat(
          unixTimestampToSqlTimestamp(
              timestampStringToUnixDate("1582-10-15 00:00:00"),
              CALENDAR),
          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 public void testUnixTimestampToSqlTimestampWithAnsiDateRange() {
      for (int i = 1; i <= 9999; ++i) {
        final String str = String.format(Locale.ROOT, "%04d-01-01 00:00:00", i);
        assertThat(unixTimestampToSqlTimestamp(timestampStringToUnixDate(str), CALENDAR),
            is(Timestamp.valueOf(str)));
      }
  
      assertThat(
          unixTimestampToSqlTimestamp(timestampStringToUnixDate("9999-12-31 00:00:00"), CALENDAR),
          is(Timestamp.valueOf("9999-12-31 00:00:00")));
    }
  
    /** Until we upgrade to Junit5. */
    private static void assertThrows(Runnable runnable,
        Class<? extends Throwable> throwableClass,
        Matcher<String> messageMatcher) {
      try {
        runnable.run();
        throw new AssertionError("Exception not raised");
      } catch (Throwable e) {
        assertThat(throwableClass.isInstance(e), is(true));
        assertThat(e.getMessage(), messageMatcher);
      }
    }
  
    /**
     * Test exception is raised if date in inappropriate meaning.
     */
    @Test public void testBrokenDate() {
      // Test case for https://issues.apache.org/jira/browse/CALCITE-6248
      // [CALCITE-6248] Illegal dates are accepted by casts
      assertThrows(() -> DateTimeUtils.dateStringToUnixDate("2023-02-29"),
          IllegalArgumentException.class,
          is("Value of DAY field is out of range in '2023-02-29'"));
  
      // Test case for https://issues.apache.org/jira/browse/CALCITE-6248
      // [CALCITE-6248] Illegal dates are accepted by casts
      assertThrows(() -> DateTimeUtils.dateStringToUnixDate("2023-13-1"),
          IllegalArgumentException.class,
          is("Value of MONTH field is out of range in '2023-13-1'"));
  
      // Test case for https://issues.apache.org/jira/browse/CALCITE-6248
      // [CALCITE-6248] Illegal dates are accepted by casts
      assertThrows(() -> DateTimeUtils.dateStringToUnixDate("0-1-1"),
          IllegalArgumentException.class,
          is("Value of YEAR field is out of range in '0-1-1'"));
  
      // 2023 is not a leap year
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("2023-02-29 12:00:00.123"),
          IllegalArgumentException.class,
          is("Value of DAY field is out of range in '2023-02-29 12:00:00.123'"));
  
      // 2000 is a leap year
      long x = timestampStringToUnixDate("2000-02-29 12:00:00.123");
      assertThat(x, is(951825600123L));
  
      // 1900 is not a leap year
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("1900-02-29 12:00:00.123"),
          IllegalArgumentException.class,
          is("Value of DAY field is out of range in '1900-02-29 12:00:00.123'"));
  
      // 2100 is not a leap year
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("2100-02-29 12:00:00.123"),
          IllegalArgumentException.class,
          is("Value of DAY field is out of range in '2100-02-29 12:00:00.123'"));
  
      // 1600 is a leap year
      long x2 = timestampStringToUnixDate("1600-02-29 12:00:00.123");
      assertThat(x2, is(-11670955199877L));
  
      // 1500 is not a leap year
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("1500-02-29 12:00:00.123"),
          IllegalArgumentException.class,
          is("Value of DAY field is out of range in '1500-02-29 12:00:00.123'"));
  
      // April has only 30 days
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("2023-04-31 12:00:00.123"),
          IllegalArgumentException.class,
          is("Value of DAY field is out of range in '2023-04-31 12:00:00.123'"));
  
      // Month 13 is invalid
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("2023-13-29 12:00:00.123"),
          IllegalArgumentException.class,
          is("Invalid DATE value, '2023-13-29 12:00:00.123'"));
  
      // Month -3 is invalid
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("2023--3-29 12:00:00.123"),
          IllegalArgumentException.class,
          is("Invalid DATE value, '2023--3-29 12:00:00.123'"));
  
      // '123-1' is an invalid fractional second
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("2023-02-27 12:00:00.123-1"),
          IllegalArgumentException.class,
          is("Invalid TIME value, '2023-02-27 12:00:00.123-1'"));
  
      // Invalid day 270
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("2023-02-270 12:00:00.123"),
          IllegalArgumentException.class,
          is("Invalid DATE value, '2023-02-270 12:00:00.123'"));
  
      // Invalid fractional seconds '123-1'
      assertThrows(() ->
              DateTimeUtils.timestampStringToUnixDate("2023-02-27 12:00:00.123-1"),
          IllegalArgumentException.class,
          is("Invalid TIME value, '2023-02-27 12:00:00.123-1'"));
  
      // Hour 24 is invalid
      assertThrows(() ->
          DateTimeUtils.timestampStringToUnixDate("2023-02-28 24:00:00.123"),
          IllegalArgumentException.class,
          is("Value of HOUR field is out of range in '2023-02-28 24:00:00.123'"));
    }
  }
  
  // End DateTimeUtilsTest.java
  