You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2017/01/28 16:56:36 UTC
[1/4] calcite git commit: [CALCITE-1608] Move addMonths and
subtractMonths from Calcite SqlFunctions to Avatica DateTimeUtils
Repository: calcite
Updated Branches:
refs/heads/master 7605d4218 -> beb465334
[CALCITE-1608] Move addMonths and subtractMonths from Calcite SqlFunctions to Avatica DateTimeUtils
Also, Copy tests from Calcite SqlFunctionsTest to Avatica DateTimeUtilsTest.
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/d6c5d6e1
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/d6c5d6e1
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/d6c5d6e1
Branch: refs/heads/master
Commit: d6c5d6e122f23ea1e62a4e02636f79011cbe2248
Parents: 94cb577
Author: Julian Hyde <jh...@apache.org>
Authored: Thu Jan 26 17:19:53 2017 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Fri Jan 27 23:56:54 2017 -0800
----------------------------------------------------------------------
.../calcite/avatica/util/DateTimeUtils.java | 87 +++++
.../calcite/avatica/util/DateTimeUtilsTest.java | 340 +++++++++++++++++++
2 files changed, 427 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/d6c5d6e1/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
----------------------------------------------------------------------
diff --git a/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java b/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
index 67e1245..2c16235 100644
--- a/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
+++ b/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
@@ -800,6 +800,93 @@ public class DateTimeUtils {
+ (long) second * MILLIS_PER_SECOND;
}
+ /** Adds a given number of months to a timestamp, represented as the number
+ * of milliseconds since the epoch. */
+ public static long addMonths(long timestamp, int m) {
+ final long millis =
+ DateTimeUtils.floorMod(timestamp, DateTimeUtils.MILLIS_PER_DAY);
+ timestamp -= millis;
+ final long x =
+ addMonths((int) (timestamp / DateTimeUtils.MILLIS_PER_DAY), m);
+ return x * DateTimeUtils.MILLIS_PER_DAY + millis;
+ }
+
+ /** Adds a given number of months to a date, represented as the number of
+ * days since the epoch. */
+ public static int addMonths(int date, int m) {
+ int y0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.YEAR, date);
+ int m0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.MONTH, date);
+ int d0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.DAY, date);
+ int y = m / 12;
+ y0 += y;
+ m0 += m - y * 12;
+ int last = lastDay(y0, m0);
+ if (d0 > last) {
+ d0 = 1;
+ if (++m0 > 12) {
+ m0 = 1;
+ ++y0;
+ }
+ }
+ return DateTimeUtils.ymdToUnixDate(y0, m0, d0);
+ }
+
+ private static int lastDay(int y, int m) {
+ switch (m) {
+ case 2:
+ return y % 4 == 0
+ && (y % 100 != 0
+ || y % 400 == 0)
+ ? 29 : 28;
+ case 4:
+ case 6:
+ case 9:
+ case 11:
+ return 30;
+ default:
+ return 31;
+ }
+ }
+
+ /** Finds the number of months between two dates, each represented as the
+ * number of days since the epoch. */
+ public static int subtractMonths(int date0, int date1) {
+ if (date0 < date1) {
+ return -subtractMonths(date1, date0);
+ }
+ // Start with an estimate.
+ // Since no month has more than 31 days, the estimate is <= the true value.
+ int m = (date0 - date1) / 31;
+ for (;;) {
+ int date2 = addMonths(date1, m);
+ if (date2 >= date0) {
+ return m;
+ }
+ int date3 = addMonths(date1, m + 1);
+ if (date3 > date0) {
+ return m;
+ }
+ ++m;
+ }
+ }
+
+ public static int subtractMonths(long t0, long t1) {
+ final long millis0 =
+ DateTimeUtils.floorMod(t0, DateTimeUtils.MILLIS_PER_DAY);
+ final int d0 = (int) DateTimeUtils.floorDiv(t0 - millis0,
+ DateTimeUtils.MILLIS_PER_DAY);
+ final long millis1 =
+ DateTimeUtils.floorMod(t1, DateTimeUtils.MILLIS_PER_DAY);
+ final int d1 = (int) DateTimeUtils.floorDiv(t1 - millis1,
+ DateTimeUtils.MILLIS_PER_DAY);
+ int x = subtractMonths(d0, d1);
+ final long d2 = addMonths(d1, x);
+ if (d2 == d0 && millis0 < millis1) {
+ --x;
+ }
+ return x;
+ }
+
/** Divide, rounding towards negative infinity. */
public static long floorDiv(long x, long y) {
long r = x / y;
http://git-wip-us.apache.org/repos/asf/calcite/blob/d6c5d6e1/avatica/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
----------------------------------------------------------------------
diff --git a/avatica/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java b/avatica/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
new file mode 100644
index 0000000..f5222de
--- /dev/null
+++ b/avatica/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
@@ -0,0 +1,340 @@
+/*
+ * 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.junit.Test;
+
+import static org.apache.calcite.avatica.util.DateTimeUtils.EPOCH_JULIAN;
+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.floorDiv;
+import static org.apache.calcite.avatica.util.DateTimeUtils.floorMod;
+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.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.unixDateExtract;
+import static org.apache.calcite.avatica.util.DateTimeUtils.unixDateToString;
+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.unixTimestampToString;
+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.junit.Assert.assertEquals;
+import static org.junit.Assert.assertThat;
+
+/**
+ * Tests for {@link DateTimeUtils}.
+ */
+public class DateTimeUtilsTest {
+ @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));
+ }
+
+ @Test public void testFloorDiv() {
+ assertThat(floorDiv(13, 3), equalTo(4L));
+ assertThat(floorDiv(12, 3), equalTo(4L));
+ assertThat(floorDiv(11, 3), equalTo(3L));
+ assertThat(floorDiv(-13, 3), equalTo(-5L));
+ assertThat(floorDiv(-12, 3), equalTo(-4L));
+ assertThat(floorDiv(-11, 3), equalTo(-4L));
+ assertThat(floorDiv(0, 3), equalTo(0L));
+ assertThat(floorDiv(1, 3), equalTo(0L));
+ assertThat(floorDiv(-1, 3), is(-1L));
+ }
+
+ @Test public void testFloorMod() {
+ assertThat(floorMod(13, 3), is(1L));
+ assertThat(floorMod(12, 3), is(0L));
+ assertThat(floorMod(11, 3), is(2L));
+ assertThat(floorMod(-13, 3), is(2L));
+ assertThat(floorMod(-12, 3), is(0L));
+ assertThat(floorMod(-11, 3), is(1L));
+ assertThat(floorMod(0, 3), is(0L));
+ assertThat(floorMod(1, 3), is(1L));
+ assertThat(floorMod(-1, 3), is(2L));
+ }
+
+ @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));
+ }
+
+ 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", 0);
+ checkTimeString("23:59:59", 86400000 - 1000);
+ }
+
+ private void checkTimeString(String s, int d) {
+ assertThat(unixTimeToString(d), is(s));
+ assertThat(timeStringToUnixDate(s), 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", 0L);
+ checkTimestampString("1970-02-01 23:59:59", 86400000L * 32L - 1000L);
+ }
+
+ private void checkTimestampString(String s, long d) {
+ assertThat(unixTimestampToString(d), 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));
+
+ 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(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);
+ }
+
+ private void thereAndBack(int year, int month, int day) {
+ final int unixDate = ymdToUnixDate(year, month, day);
+ assertThat(unixDateExtract(TimeUnitRange.YEAR, unixDate),
+ is((long) year));
+ assertThat(unixDateExtract(TimeUnitRange.MONTH, unixDate),
+ is((long) month));
+ assertThat(unixDateExtract(TimeUnitRange.DAY, unixDate),
+ is((long) day));
+ }
+
+ @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, 5, 1, 3); // roll up
+ checkAddMonths(2016, 4, 30, 2016, 7, 30, 3); // roll up
+ checkAddMonths(2016, 1, 31, 2016, 3, 1, 1);
+ checkAddMonths(2016, 3, 31, 2016, 3, 1, -1);
+ checkAddMonths(2016, 3, 31, 2116, 3, 31, 1200);
+ checkAddMonths(2016, 2, 28, 2116, 2, 28, 1200);
+ }
+
+ 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), 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), 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 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));
+ }
+}
+
+// End DateTimeUtilsTest.java
[2/4] calcite git commit: [CALCITE-1606] Add datetime scalar
functions (Laurent Goujon)
Posted by jh...@apache.org.
[CALCITE-1606] Add datetime scalar functions (Laurent Goujon)
Add support for the missing JDBC/ODBC datetime scalar functions: YEAR,
MONTH, WEEK, DAYOFYEAR, DAYOFMONTH, DAYOFWEEK, HOUR, MINUTE, SECOND.
Also EXTRACT with the corresponding time-unit arguments.
Close apache/calcite#364
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/94cb5778
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/94cb5778
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/94cb5778
Branch: refs/heads/master
Commit: 94cb577898e0cab2c1acc92a981133323918660c
Parents: 7605d42
Author: Laurent Goujon <la...@dremio.com>
Authored: Thu Jan 26 13:41:50 2017 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Fri Jan 27 23:56:54 2017 -0800
----------------------------------------------------------------------
core/src/main/codegen/templates/Parser.jj | 5 +
.../apache/calcite/sql/SqlJdbcFunctionCall.java | 10 ++
.../calcite/sql/fun/SqlDatePartFunction.java | 81 +++++++++
.../calcite/sql/fun/SqlQuarterFunction.java | 63 -------
.../calcite/sql/fun/SqlStdOperatorTable.java | 85 +++++++++-
.../sql2rel/StandardConvertletTable.java | 32 ----
.../apache/calcite/sql/test/SqlAdvisorTest.java | 7 +-
.../calcite/sql/test/SqlOperatorBaseTest.java | 170 ++++++++++++++++---
site/_docs/reference.md | 28 ++-
9 files changed, 348 insertions(+), 133 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/94cb5778/core/src/main/codegen/templates/Parser.jj
----------------------------------------------------------------------
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 6887a21..dcc0558 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -4738,6 +4738,7 @@ SqlIdentifier ReservedFunctionName() :
| <FLOOR>
| <FUSION>
| <GROUPING>
+ | <HOUR>
| <LAST_VALUE>
| <LN>
| <LOCALTIME>
@@ -4745,7 +4746,9 @@ SqlIdentifier ReservedFunctionName() :
| <LOWER>
| <MAX>
| <MIN>
+ | <MINUTE>
| <MOD>
+ | <MONTH>
| <NULLIF>
| <OCTET_LENGTH>
| <PERCENT_RANK>
@@ -4754,6 +4757,7 @@ SqlIdentifier ReservedFunctionName() :
| <REGR_SXX>
| <REGR_SYY>
| <ROW_NUMBER>
+ | <SECOND>
| <SQRT>
| <STDDEV_POP>
| <STDDEV_SAMP>
@@ -4762,6 +4766,7 @@ SqlIdentifier ReservedFunctionName() :
| <USER>
| <VAR_POP>
| <VAR_SAMP>
+ | <YEAR>
)
{
return new SqlIdentifier(unquotedIdentifier(), getPos());
http://git-wip-us.apache.org/repos/asf/calcite/blob/94cb5778/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
index 34c42be..20a01da 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
@@ -707,7 +707,17 @@ public class SqlJdbcFunctionCall extends SqlFunction {
operands[0]);
}
});
+ map.put("YEAR", simple(SqlStdOperatorTable.YEAR));
map.put("QUARTER", simple(SqlStdOperatorTable.QUARTER));
+ map.put("MONTH", simple(SqlStdOperatorTable.MONTH));
+ map.put("WEEK", simple(SqlStdOperatorTable.WEEK));
+ map.put("DAYOFYEAR", simple(SqlStdOperatorTable.DAYOFYEAR));
+ map.put("DAYOFMONTH", simple(SqlStdOperatorTable.DAYOFMONTH));
+ map.put("DAYOFWEEK", simple(SqlStdOperatorTable.DAYOFWEEK));
+ map.put("HOUR", simple(SqlStdOperatorTable.HOUR));
+ map.put("MINUTE", simple(SqlStdOperatorTable.MINUTE));
+ map.put("SECOND", simple(SqlStdOperatorTable.SECOND));
+
map.put("RTRIM",
new SimpleMakeCall(SqlStdOperatorTable.TRIM) {
@Override public SqlCall createCall(SqlParserPos pos,
http://git-wip-us.apache.org/repos/asf/calcite/blob/94cb5778/core/src/main/java/org/apache/calcite/sql/fun/SqlDatePartFunction.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlDatePartFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatePartFunction.java
new file mode 100644
index 0000000..e6c5bde
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlDatePartFunction.java
@@ -0,0 +1,81 @@
+/*
+ * 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.sql.fun;
+
+import org.apache.calcite.avatica.util.TimeUnit;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCallBinding;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlIntervalQualifier;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlOperandCountRange;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.InferTypes;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlOperandCountRanges;
+import org.apache.calcite.sql.validate.SqlValidator;
+
+import java.util.List;
+
+/**
+ * SqlDatePartFunction represents the SQL:1999 standard {@code YEAR},
+ * {@code QUARTER}, {@code MONTH} and {@code DAY} functions.
+ */
+public class SqlDatePartFunction extends SqlFunction {
+ //~ Constructors -----------------------------------------------------------
+ private final TimeUnit timeUnit;
+
+ public SqlDatePartFunction(String name, TimeUnit timeUnit) {
+ super(name,
+ SqlKind.OTHER,
+ ReturnTypes.BIGINT_NULLABLE,
+ InferTypes.FIRST_KNOWN,
+ OperandTypes.DATETIME,
+ SqlFunctionCategory.TIMEDATE);
+ this.timeUnit = timeUnit;
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public SqlNode rewriteCall(SqlValidator validator, SqlCall call) {
+ final List<SqlNode> operands = call.getOperandList();
+ final SqlParserPos pos = call.getParserPosition();
+ return SqlStdOperatorTable.EXTRACT.createCall(pos,
+ new SqlIntervalQualifier(timeUnit, null, SqlParserPos.ZERO),
+ operands.get(0));
+ }
+
+ public SqlOperandCountRange getOperandCountRange() {
+ return SqlOperandCountRanges.of(1);
+ }
+
+ public String getSignatureTemplate(int operandsCount) {
+ assert 1 == operandsCount;
+ return "{0}({1})";
+ }
+
+ public boolean checkOperandTypes(SqlCallBinding callBinding,
+ boolean throwOnFailure) {
+ return OperandTypes.DATETIME.checkSingleOperandType(callBinding,
+ callBinding.operand(0), 0, throwOnFailure);
+ }
+}
+
+// End SqlDatePartFunction.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/94cb5778/core/src/main/java/org/apache/calcite/sql/fun/SqlQuarterFunction.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlQuarterFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlQuarterFunction.java
deleted file mode 100644
index b76c1dd..0000000
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlQuarterFunction.java
+++ /dev/null
@@ -1,63 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one or more
- * contributor license agreements. See the NOTICE file distributed with
- * this work for additional information regarding copyright ownership.
- * The ASF licenses this file to you under the Apache License, Version 2.0
- * (the "License"); you may not use this file except in compliance with
- * the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- */
-package org.apache.calcite.sql.fun;
-
-import org.apache.calcite.sql.SqlCallBinding;
-import org.apache.calcite.sql.SqlFunction;
-import org.apache.calcite.sql.SqlFunctionCategory;
-import org.apache.calcite.sql.SqlKind;
-import org.apache.calcite.sql.SqlOperandCountRange;
-import org.apache.calcite.sql.type.InferTypes;
-import org.apache.calcite.sql.type.OperandTypes;
-import org.apache.calcite.sql.type.ReturnTypes;
-import org.apache.calcite.sql.type.SqlOperandCountRanges;
-
-/**
- * SqlQuarterFunction represents the SQL:1999 standard {@code QUARTER}
- * function. Determines Quarter (1,2,3,4) of a given date.
- */
-public class SqlQuarterFunction extends SqlFunction {
- //~ Constructors -----------------------------------------------------------
-
- public SqlQuarterFunction() {
- super("QUARTER",
- SqlKind.OTHER,
- ReturnTypes.BIGINT_NULLABLE,
- InferTypes.FIRST_KNOWN,
- OperandTypes.DATETIME,
- SqlFunctionCategory.TIMEDATE);
- }
-
- //~ Methods ----------------------------------------------------------------
-
- public SqlOperandCountRange getOperandCountRange() {
- return SqlOperandCountRanges.of(1);
- }
-
- public String getSignatureTemplate(int operandsCount) {
- assert 1 == operandsCount;
- return "{0}({1})";
- }
-
- public boolean checkOperandTypes(SqlCallBinding callBinding,
- boolean throwOnFailure) {
- return OperandTypes.DATETIME.checkSingleOperandType(callBinding,
- callBinding.operand(0), 0, throwOnFailure);
- }
-}
-
-// End SqlQuarterFunction.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/94cb5778/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 5c71006..dfbdfa0 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.sql.fun;
+import org.apache.calcite.avatica.util.TimeUnit;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.sql.SqlAggFunction;
@@ -1534,12 +1535,94 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
public static final SqlFunction EXTRACT = new SqlExtractFunction();
/**
+ * The SQL <code>YEAR</code> operator. Returns the Year
+ * from a DATETIME E.g.<br>
+ * <code>YEAR(date '2008-9-23')</code> returns <code>
+ * 2008</code>
+ */
+ public static final SqlDatePartFunction YEAR =
+ new SqlDatePartFunction("YEAR", TimeUnit.YEAR);
+
+ /**
* The SQL <code>QUARTER</code> operator. Returns the Quarter
* from a DATETIME E.g.<br>
* <code>QUARTER(date '2008-9-23')</code> returns <code>
* 3</code>
*/
- public static final SqlQuarterFunction QUARTER = new SqlQuarterFunction();
+ public static final SqlDatePartFunction QUARTER =
+ new SqlDatePartFunction("QUARTER", TimeUnit.QUARTER);
+
+ /**
+ * The SQL <code>MONTH</code> operator. Returns the Month
+ * from a DATETIME E.g.<br>
+ * <code>MONTH(date '2008-9-23')</code> returns <code>
+ * 9</code>
+ */
+ public static final SqlDatePartFunction MONTH =
+ new SqlDatePartFunction("MONTH", TimeUnit.MONTH);
+
+ /**
+ * The SQL <code>WEEK</code> operator. Returns the Week
+ * from a DATETIME E.g.<br>
+ * <code>WEEK(date '2008-9-23')</code> returns <code>
+ * 39</code>
+ */
+ public static final SqlDatePartFunction WEEK =
+ new SqlDatePartFunction("WEEK", TimeUnit.WEEK);
+
+ /**
+ * The SQL <code>DAYOFYEAR</code> operator. Returns the DOY
+ * from a DATETIME E.g.<br>
+ * <code>DAYOFYEAR(date '2008-9-23')</code> returns <code>
+ * 267</code>
+ */
+ public static final SqlDatePartFunction DAYOFYEAR =
+ new SqlDatePartFunction("DAYOFYEAR", TimeUnit.DOY);
+
+ /**
+ * The SQL <code>DAYOFMONTH</code> operator. Returns the Day
+ * from a DATETIME E.g.<br>
+ * <code>DAYOFMONTH(date '2008-9-23')</code> returns <code>
+ * 23</code>
+ */
+ public static final SqlDatePartFunction DAYOFMONTH =
+ new SqlDatePartFunction("DAYOFMONTH", TimeUnit.DAY);
+
+ /**
+ * The SQL <code>DAYOFWEEK</code> operator. Returns the DOW
+ * from a DATETIME E.g.<br>
+ * <code>DAYOFWEEK(date '2008-9-23')</code> returns <code>
+ * 2</code>
+ */
+ public static final SqlDatePartFunction DAYOFWEEK =
+ new SqlDatePartFunction("DAYOFWEEK", TimeUnit.DOW);
+
+ /**
+ * The SQL <code>HOUR</code> operator. Returns the Hour
+ * from a DATETIME E.g.<br>
+ * <code>HOUR(timestamp '2008-9-23 01:23:45')</code> returns <code>
+ * 1</code>
+ */
+ public static final SqlDatePartFunction HOUR =
+ new SqlDatePartFunction("HOUR", TimeUnit.HOUR);
+
+ /**
+ * The SQL <code>MINUTE</code> operator. Returns the Minute
+ * from a DATETIME E.g.<br>
+ * <code>MINUTE(timestamp '2008-9-23 01:23:45')</code> returns <code>
+ * 23</code>
+ */
+ public static final SqlDatePartFunction MINUTE =
+ new SqlDatePartFunction("MINUTE", TimeUnit.MINUTE);
+
+ /**
+ * The SQL <code>SECOND</code> operator. Returns the Second
+ * from a DATETIME E.g.<br>
+ * <code>SECOND(timestamp '2008-9-23 01:23:45')</code> returns <code>
+ * 45</code>
+ */
+ public static final SqlDatePartFunction SECOND =
+ new SqlDatePartFunction("SECOND", TimeUnit.SECOND);
/**
* The ELEMENT operator, used to convert a multiset with only one item to a
http://git-wip-us.apache.org/repos/asf/calcite/blob/94cb5778/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index f1c2e1e..40fecf7 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -60,7 +60,6 @@ import org.apache.calcite.sql.fun.SqlMapValueConstructor;
import org.apache.calcite.sql.fun.SqlMultisetQueryConstructor;
import org.apache.calcite.sql.fun.SqlMultisetValueConstructor;
import org.apache.calcite.sql.fun.SqlOverlapsOperator;
-import org.apache.calcite.sql.fun.SqlQuarterFunction;
import org.apache.calcite.sql.fun.SqlRowOperator;
import org.apache.calcite.sql.fun.SqlSequenceValueOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
@@ -744,37 +743,6 @@ public class StandardConvertletTable extends ReflectiveConvertletTable {
return res;
}
- /**
- * Converts a call to the {@code QUARTER} function.
- *
- * <p>Called automatically via reflection.
- */
- public RexNode convertQuarter(
- SqlRexContext cx,
- SqlQuarterFunction op,
- SqlCall call) {
- final List<SqlNode> operands = call.getOperandList();
- assert operands.size() == 1;
- RexNode x = cx.convertExpression(operands.get(0));
- final RexBuilder rexBuilder = cx.getRexBuilder();
- final RelDataTypeFactory typeFactory = cx.getTypeFactory();
- final RelDataType resType =
- typeFactory.createTypeWithNullability(
- typeFactory.createSqlType(SqlTypeName.BIGINT),
- x.getType().isNullable());
- RexNode res =
- rexBuilder.makeCall(
- resType,
- SqlStdOperatorTable.EXTRACT_DATE,
- ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.MONTH), x));
- res = rexBuilder.makeCall(SqlStdOperatorTable.MINUS, res,
- rexBuilder.makeExactLiteral(BigDecimal.ONE));
- res = divide(rexBuilder, res, TimeUnit.QUARTER.multiplier);
- res = rexBuilder.makeCall(SqlStdOperatorTable.PLUS, res,
- rexBuilder.makeExactLiteral(BigDecimal.ONE));
- return res;
- }
-
private static BigDecimal getFactor(TimeUnit unit) {
switch (unit) {
case DAY:
http://git-wip-us.apache.org/repos/asf/calcite/blob/94cb5778/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index 24a4626..b318b7c 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -149,6 +149,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(FLOOR)",
"KEYWORD(FUSION)",
"KEYWORD(GROUPING)",
+ "KEYWORD(HOUR)",
"KEYWORD(INTERVAL)",
"KEYWORD(LAST_VALUE)",
"KEYWORD(LN)",
@@ -157,7 +158,9 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(LOWER)",
"KEYWORD(MAX)",
"KEYWORD(MIN)",
+ "KEYWORD(MINUTE)",
"KEYWORD(MOD)",
+ "KEYWORD(MONTH)",
"KEYWORD(MULTISET)",
"KEYWORD(NEW)",
"KEYWORD(NEXT)",
@@ -174,6 +177,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(REGR_SYY)",
"KEYWORD(ROW)",
"KEYWORD(ROW_NUMBER)",
+ "KEYWORD(SECOND)",
"KEYWORD(SESSION_USER)",
"KEYWORD(SPECIFIC)",
"KEYWORD(SQRT)",
@@ -191,7 +195,8 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(UPPER)",
"KEYWORD(USER)",
"KEYWORD(VAR_POP)",
- "KEYWORD(VAR_SAMP)");
+ "KEYWORD(VAR_SAMP)",
+ "KEYWORD(YEAR)");
protected static final List<String> SELECT_KEYWORDS =
Arrays.asList(
http://git-wip-us.apache.org/repos/asf/calcite/blob/94cb5778/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
index 24c33f5..76aab95 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
@@ -1739,45 +1739,37 @@ public abstract class SqlOperatorBaseTest {
if (false) {
tester.checkScalar("{fn DAYNAME(date)}", null, "");
}
- if (false) {
- tester.checkScalar("{fn DAYOFMONTH(date)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn DAYOFWEEK(date)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn DAYOFYEAR(date)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn HOUR(time)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn MINUTE(time)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn MONTH(date)}", null, "");
- }
+ tester.checkScalar("{fn DAYOFMONTH(DATE '2014-12-10')}", 10,
+ "BIGINT NOT NULL");
+ tester.checkFails("{fn DAYOFWEEK(DATE '2014-12-10')}",
+ "cannot translate call EXTRACT.*",
+ true);
+ tester.checkFails("{fn DAYOFYEAR(DATE '2014-12-10')}",
+ "cannot translate call EXTRACT.*",
+ true);
+ tester.checkScalar("{fn HOUR(TIMESTAMP '2014-12-10 12:34:56')}", 12,
+ "BIGINT NOT NULL");
+ tester.checkScalar("{fn MINUTE(TIMESTAMP '2014-12-10 12:34:56')}", 34,
+ "BIGINT NOT NULL");
+ tester.checkScalar("{fn MONTH(DATE '2014-12-10')}", 12, "BIGINT NOT NULL");
if (false) {
tester.checkScalar("{fn MONTHNAME(date)}", null, "");
}
tester.checkType("{fn NOW()}", "TIMESTAMP(0) NOT NULL");
tester.checkScalar("{fn QUARTER(DATE '2014-12-10')}", "4",
"BIGINT NOT NULL");
- if (false) {
- tester.checkScalar("{fn SECOND(time)}", null, "");
- }
+ tester.checkScalar("{fn SECOND(TIMESTAMP '2014-12-10 12:34:56')}", 56,
+ "BIGINT NOT NULL");
tester.checkScalar("{fn TIMESTAMPADD(HOUR, 5,"
+ " TIMESTAMP '2014-03-29 12:34:56')}",
"2014-03-29 17:34:56", "TIMESTAMP(0) NOT NULL");
tester.checkScalar("{fn TIMESTAMPDIFF(HOUR,"
+ " TIMESTAMP '2014-03-29 12:34:56',"
+ " TIMESTAMP '2014-03-29 12:34:56')}", "0", "INTEGER NOT NULL");
- if (false) {
- tester.checkScalar("{fn WEEK(date)}", null, "");
- }
- if (false) {
- tester.checkScalar("{fn YEAR(date)}", null, "");
- }
+ tester.checkFails("{fn WEEK(DATE '2014-12-10')}",
+ "cannot translate call EXTRACT.*",
+ true);
+ tester.checkScalar("{fn YEAR(DATE '2014-12-10')}", 2014, "BIGINT NOT NULL");
// System Functions
tester.checkType("{fn DATABASE()}", "VARCHAR(2000) NOT NULL");
@@ -4750,6 +4742,19 @@ public abstract class SqlOperatorBaseTest {
tester.setFor(SqlStdOperatorTable.FUSION, VM_FENNEL, VM_JAVA);
}
+ @Test public void testYear() {
+ tester.setFor(
+ SqlStdOperatorTable.YEAR,
+ VM_FENNEL,
+ VM_JAVA);
+
+ tester.checkScalar(
+ "year(date '2008-1-23')",
+ "2008",
+ "BIGINT NOT NULL");
+ tester.checkNull("year(cast(null as date))");
+ }
+
@Test public void testQuarter() {
tester.setFor(
SqlStdOperatorTable.QUARTER,
@@ -4807,6 +4812,117 @@ public abstract class SqlOperatorBaseTest {
tester.checkNull("quarter(cast(null as date))");
}
+ @Test public void testMonth() {
+ tester.setFor(
+ SqlStdOperatorTable.MONTH,
+ VM_FENNEL,
+ VM_JAVA);
+
+ tester.checkScalar(
+ "month(date '2008-1-23')",
+ "1",
+ "BIGINT NOT NULL");
+ tester.checkNull("month(cast(null as date))");
+ }
+
+ @Test public void testWeek() {
+ tester.setFor(
+ SqlStdOperatorTable.WEEK,
+ VM_FENNEL,
+ VM_JAVA);
+ // TODO: Not implemented in operator test execution code
+ tester.checkFails(
+ "week(date '2008-1-23')",
+ "cannot translate call EXTRACT.*",
+ true);
+ tester.checkFails(
+ "week(cast(null as date))",
+ "cannot translate call EXTRACT.*",
+ true);
+ }
+
+ @Test public void testDayOfYear() {
+ tester.setFor(
+ SqlStdOperatorTable.DAYOFYEAR,
+ VM_FENNEL,
+ VM_JAVA);
+ // TODO: Not implemented in operator test execution code
+ tester.checkFails(
+ "dayofyear(date '2008-1-23')",
+ "cannot translate call EXTRACT.*",
+ true);
+ tester.checkFails(
+ "dayofyear(cast(null as date))",
+ "cannot translate call EXTRACT.*",
+ true);
+ }
+
+ @Test public void testDayOfMonth() {
+ tester.setFor(
+ SqlStdOperatorTable.DAYOFMONTH,
+ VM_FENNEL,
+ VM_JAVA);
+ tester.checkScalar(
+ "dayofmonth(date '2008-1-23')",
+ "23",
+ "BIGINT NOT NULL");
+ tester.checkNull("dayofmonth(cast(null as date))");
+ }
+
+ @Test public void testDayOfWeek() {
+ tester.setFor(
+ SqlStdOperatorTable.DAYOFWEEK,
+ VM_FENNEL,
+ VM_JAVA);
+ // TODO: Not implemented in operator test execution code
+ tester.checkFails(
+ "dayofweek(date '2008-1-23')",
+ "cannot translate call EXTRACT.*",
+ true);
+ tester.checkFails("dayofweek(cast(null as date))",
+ "cannot translate call EXTRACT.*",
+ true);
+ }
+
+ @Test public void testHour() {
+ tester.setFor(
+ SqlStdOperatorTable.HOUR,
+ VM_FENNEL,
+ VM_JAVA);
+
+ tester.checkScalar(
+ "hour(timestamp '2008-1-23 12:34:56')",
+ "12",
+ "BIGINT NOT NULL");
+ tester.checkNull("hour(cast(null as timestamp))");
+ }
+
+ @Test public void testMinute() {
+ tester.setFor(
+ SqlStdOperatorTable.MINUTE,
+ VM_FENNEL,
+ VM_JAVA);
+
+ tester.checkScalar(
+ "minute(timestamp '2008-1-23 12:34:56')",
+ "34",
+ "BIGINT NOT NULL");
+ tester.checkNull("minute(cast(null as timestamp))");
+ }
+
+ @Test public void testSecond() {
+ tester.setFor(
+ SqlStdOperatorTable.SECOND,
+ VM_FENNEL,
+ VM_JAVA);
+
+ tester.checkScalar(
+ "second(timestamp '2008-1-23 12:34:56')",
+ "56",
+ "BIGINT NOT NULL");
+ tester.checkNull("second(cast(null as timestamp))");
+ }
+
@Test public void testExtractIntervalYearMonth() {
tester.setFor(
SqlStdOperatorTable.EXTRACT,
http://git-wip-us.apache.org/repos/asf/calcite/blob/94cb5778/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 3b9c3d3..37f33d9 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1026,6 +1026,16 @@ Not implemented:
| EXTRACT(timeUnit FROM datetime) | Extracts and returns the value of a specified datetime field from a datetime value expression
| FLOOR(datetime TO timeUnit) | Rounds *datetime* down to *timeUnit*
| CEIL(datetime TO timeUnit) | Rounds *datetime* up to *timeUnit*
+| YEAR(date) | Equivalent to `EXTRACT(YEAR FROM date)`. Returns an integer.
+| QUARTER(date) | Equivalent to `EXTRACT(QUARTER FROM date)`. Returns an integer between 1 and 4.
+| MONTH(date) | Equivalent to `EXTRACT(MONTH FROM date)`. Returns an integer between 1 and 12.
+| WEEK(date) | Equivalent to `EXTRACT(WEEK FROM date)`. Returns an integer between 1 and 53.
+| DAYOFYEAR(date) | Equivalent to `EXTRACT(DOY FROM date)`. Returns an integer between 1 and 366.
+| DAYOFMONTH(date) | Equivalent to `EXTRACT(DAY FROM date)`. Returns an integer between 1 and 31.
+| DAYOFWEEK(date) | Equivalent to `EXTRACT(DOW FROM date)`. Returns an integer between 1 and 7.
+| HOUR(date) | Equivalent to `EXTRACT(HOUR FROM date)`. Returns an integer between 0 and 23.
+| MINUTE(date) | Equivalent to `EXTRACT(MINUTE FROM date)`. Returns an integer between 0 and 59.
+| SECOND(date) | Equivalent to `EXTRACT(SECOND FROM date)`. Returns an integer between 0 and 59.
Not implemented:
@@ -1152,23 +1162,23 @@ Not implemented:
| {fn CURDATE()} | Equivalent to `CURRENT_DATE`
| {fn CURTIME()} | Equivalent to `LOCALTIME`
| {fn NOW()} | Equivalent to `LOCALTIMESTAMP`
+| {fn YEAR(date)} | Equivalent to `EXTRACT(YEAR FROM date)`. Returns an integer.
| {fn QUARTER(date)} | Equivalent to `EXTRACT(QUARTER FROM date)`. Returns an integer between 1 and 4.
+| {fn MONTH(date)} | Equivalent to `EXTRACT(MONTH FROM date)`. Returns an integer between 1 and 12.
+| {fn WEEK(date)} | Equivalent to `EXTRACT(WEEK FROM date)`. Returns an integer between 1 and 53.
+| {fn DAYOFYEAR(date)} | Equivalent to `EXTRACT(DOY FROM date)`. Returns an integer between 1 and 366.
+| {fn DAYOFMONTH(date)} | Equivalent to `EXTRACT(DAY FROM date)`. Returns an integer between 1 and 31.
+| {fn DAYOFWEEK(date)} | Equivalent to `EXTRACT(DOW FROM date)`. Returns an integer between 1 and 7.
+| {fn HOUR(date)} | Equivalent to `EXTRACT(HOUR FROM date)`. Returns an integer between 0 and 23.
+| {fn MINUTE(date)} | Equivalent to `EXTRACT(MINUTE FROM date)`. Returns an integer between 0 and 59.
+| {fn SECOND(date)} | Equivalent to `EXTRACT(SECOND FROM date)`. Returns an integer between 0 and 59.
| {fn TIMESTAMPADD(timeUnit, count, timestamp)} | Adds an interval of *count* *timeUnit*s to a timestamp
| {fn TIMESTAMPDIFF(timeUnit, timestamp1, timestamp2)} | Subtracts *timestamp1* from *timestamp2* and returns the result in *timeUnit*s
Not implemented:
* {fn DAYNAME(date)}
-* {fn DAYOFMONTH(date)}
-* {fn DAYOFWEEK(date)}
-* {fn DAYOFYEAR(date)}
-* {fn HOUR(time)}
-* {fn MINUTE(time)}
-* {fn MONTH(date)}
* {fn MONTHNAME(date)}
-* {fn SECOND(time)}
-* {fn WEEK(date)}
-* {fn YEAR(date)}
#### System
[4/4] calcite git commit: [CALCITE-1610] RelBuilder sort-combining
optimization treats aliases incorrectly (Jess Balint)
Posted by jh...@apache.org.
[CALCITE-1610] RelBuilder sort-combining optimization treats aliases incorrectly (Jess Balint)
Close apache/calcite#367
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/beb46533
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/beb46533
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/beb46533
Branch: refs/heads/master
Commit: beb465334c1bdc62b8282492ad7580c0683cee01
Parents: 3f955ee
Author: Jess Balint <jb...@gmail.com>
Authored: Fri Jan 27 16:04:40 2017 -0600
Committer: Julian Hyde <jh...@apache.org>
Committed: Fri Jan 27 23:56:55 2017 -0800
----------------------------------------------------------------------
.../org/apache/calcite/tools/RelBuilder.java | 9 ++++---
.../org/apache/calcite/test/RelBuilderTest.java | 26 ++++++++++++++++++++
2 files changed, 31 insertions(+), 4 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/beb46533/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 2b3a545..6993cbe 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -1541,12 +1541,13 @@ public class RelBuilder {
if (project.getInput() instanceof Sort) {
final Sort sort2 = (Sort) project.getInput();
if (sort2.offset == null && sort2.fetch == null) {
- replaceTop(sort2.getInput());
final RelNode sort =
- sortFactory.createSort(peek(), sort2.collation,
+ sortFactory.createSort(sort2.getInput(), sort2.collation,
offsetNode, fetchNode);
- replaceTop(sort);
- project(project.getProjects());
+ replaceTop(
+ projectFactory.createProject(sort,
+ project.getProjects(),
+ Pair.right(project.getNamedProjects())));
return this;
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/beb46533/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index cabbcdc..61d5235 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -1518,6 +1518,32 @@ public class RelBuilderTest {
assertThat(str(root), is(expected));
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1610">[CALCITE-1610]
+ * RelBuilder sort-combining optimization treats aliases incorrectly</a>. */
+ @Test public void testSortOverProjectSort() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ builder.scan("EMP")
+ .sort(0)
+ .project(builder.field(1))
+ // was throwing exception here when attempting to apply to
+ // inner sort node
+ .limit(0, 1)
+ .build();
+ RelNode r = builder.scan("EMP")
+ .sort(0)
+ .project(Lists.newArrayList(builder.field(1)),
+ Lists.newArrayList("F1"))
+ .limit(0, 1)
+ // make sure we can still access the field by alias
+ .project(builder.field("F1"))
+ .build();
+ String expected = "LogicalProject(F1=[$1])\n"
+ + " LogicalSort(sort0=[$0], dir0=[ASC], fetch=[1])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(str(r), is(expected));
+ }
+
/** Tests that a sort on a field followed by a limit gives the same
* effect as calling sortLimit.
*
[3/4] calcite git commit: [CALCITE-1609] In DateTimeUtils,
implement unixDateExtract and unixTimeExtract for more time units
Posted by jh...@apache.org.
[CALCITE-1609] In DateTimeUtils, implement unixDateExtract and unixTimeExtract for more time units
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/3f955ee7
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/3f955ee7
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/3f955ee7
Branch: refs/heads/master
Commit: 3f955ee758c1d7d54e7be258c0fc79e6b66cccec
Parents: d6c5d6e
Author: Julian Hyde <jh...@apache.org>
Authored: Fri Jan 27 12:59:09 2017 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Fri Jan 27 23:56:55 2017 -0800
----------------------------------------------------------------------
.../calcite/avatica/util/DateTimeUtils.java | 55 ++++++
.../calcite/avatica/util/DateTimeUtilsTest.java | 171 +++++++++++++++++++
2 files changed, 226 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/3f955ee7/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
----------------------------------------------------------------------
diff --git a/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java b/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
index 2c16235..c4625d9 100644
--- a/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
+++ b/avatica/core/src/main/java/org/apache/calcite/avatica/util/DateTimeUtils.java
@@ -690,10 +690,65 @@ public class DateTimeUtils {
switch (range) {
case YEAR:
return year;
+ case QUARTER:
+ return (month + 2) / 3;
case MONTH:
return month;
case DAY:
return day;
+ case DOW:
+ return (int) floorMod(julian + 1, 7) + 1; // sun=1, sat=7
+ case WEEK:
+ long fmofw = firstMondayOfFirstWeek(year);
+ if (julian < fmofw) {
+ fmofw = firstMondayOfFirstWeek(year - 1);
+ }
+ return (int) (julian - fmofw) / 7 + 1;
+ case DOY:
+ final long janFirst = ymdToJulian(year, 1, 1);
+ return (int) (julian - janFirst) + 1;
+ case CENTURY:
+ return year > 0
+ ? (year + 99) / 100
+ : (year - 99) / 100;
+ case MILLENNIUM:
+ return year > 0
+ ? (year + 999) / 1000
+ : (year - 999) / 1000;
+ default:
+ throw new AssertionError(range);
+ }
+ }
+
+ /** Returns the first day of the first week of a year.
+ * Per ISO-8601 it is the Monday of the week that contains Jan 4,
+ * or equivalently, it is a Monday between Dec 29 and Jan 4.
+ * Sometimes it is in the year before the given year. */
+ private static long firstMondayOfFirstWeek(int year) {
+ final long janFirst = ymdToJulian(year, 1, 1);
+ final long janFirstDow = floorMod(janFirst + 1, 7); // sun=0, sat=6
+ return janFirst + (11 - janFirstDow) % 7 - 3;
+ }
+
+ /** Extracts a time unit from a UNIX date (milliseconds since epoch). */
+ public static int unixTimestampExtract(TimeUnitRange range,
+ long timestamp) {
+ return unixTimeExtract(range, (int) floorMod(timestamp, MILLIS_PER_DAY));
+ }
+
+ /** Extracts a time unit from a time value (milliseconds since midnight). */
+ public static int unixTimeExtract(TimeUnitRange range, int time) {
+ assert time >= 0;
+ assert time < MILLIS_PER_DAY;
+ switch (range) {
+ case HOUR:
+ return time / (int) MILLIS_PER_HOUR;
+ case MINUTE:
+ final int minutes = time / (int) MILLIS_PER_MINUTE;
+ return minutes % 60;
+ case SECOND:
+ final int seconds = time / (int) MILLIS_PER_SECOND;
+ return seconds % 60;
default:
throw new AssertionError(range);
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/3f955ee7/avatica/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
----------------------------------------------------------------------
diff --git a/avatica/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java b/avatica/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
index f5222de..efc4df3 100644
--- a/avatica/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
+++ b/avatica/core/src/test/java/org/apache/calcite/avatica/util/DateTimeUtilsTest.java
@@ -33,16 +33,20 @@ 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.unixDateExtract;
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.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.unixTimestampToString;
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.junit.Assert.assertEquals;
import static org.junit.Assert.assertThat;
+import static org.junit.Assert.assertTrue;
/**
* Tests for {@link DateTimeUtils}.
@@ -145,6 +149,40 @@ public class DateTimeUtilsTest {
checkTimeString("23:59:59", 86400000 - 1000);
}
+ @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 d) {
assertThat(unixTimeToString(d), is(s));
assertThat(timeStringToUnixDate(s), is(d));
@@ -235,6 +273,70 @@ public class DateTimeUtilsTest {
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));
+
+ 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(2006, 1, 1)),
+ is(52L)); // sun
+ assertThat(unixDateExtract(TimeUnitRange.WEEK, ymdToUnixDate(1970, 1, 1)),
+ is(1L)); // thu
+
+ assertThat(unixDateExtract(TimeUnitRange.WEEK, -1), is(53L)); // wed
+ assertThat(unixDateExtract(TimeUnitRange.WEEK, 0), is(1L)); // thu
+ assertThat(unixDateExtract(TimeUnitRange.WEEK, 1), is(1L)); // fru
+ 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);
@@ -261,6 +363,63 @@ public class DateTimeUtilsTest {
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));
+ // TODO: For a small time range around year 1, due to the Gregorian shift,
+ // we end up in the wrong century. Should be 1.
+ assertThat(
+ unixDateExtract(TimeUnitRange.CENTURY, ymdToUnixDate(1, 1, 1)),
+ is(0L));
+ assertThat(
+ unixDateExtract(TimeUnitRange.CENTURY, ymdToUnixDate(-2, 1, 1)),
+ is(-1L));
+
+ // 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));
+ // TODO: For a small time range around year 1, due to the Gregorian shift,
+ // we end up in the wrong millennium. Should be 1.
+ assertThat(
+ unixDateExtract(TimeUnitRange.MILLENNIUM, ymdToUnixDate(1, 1, 1)),
+ is(0L));
+ assertThat(
+ unixDateExtract(TimeUnitRange.MILLENNIUM, ymdToUnixDate(1, 2, 1)),
+ is(1L));
+ assertThat(
+ unixDateExtract(TimeUnitRange.MILLENNIUM, ymdToUnixDate(-2, 1, 1)),
+ is(-1L));
}
private void thereAndBack(int year, int month, int day) {
@@ -271,6 +430,18 @@ public class DateTimeUtilsTest {
is((long) month));
assertThat(unixDateExtract(TimeUnitRange.DAY, unixDate),
is((long) day));
+ final long w = unixDateExtract(TimeUnitRange.WEEK, unixDate);
+ assertTrue(w >= 1 && w <= 53);
+ final long dow = unixDateExtract(TimeUnitRange.DOW, unixDate);
+ assertTrue(dow >= 1 && dow <= 7);
+ final long doy = unixDateExtract(TimeUnitRange.DOY, unixDate);
+ assertTrue(doy >= 1 && dow <= 366);
+ final long q = unixDateExtract(TimeUnitRange.QUARTER, unixDate);
+ assertTrue(q >= 1 && q <= 4);
+ final long c = unixDateExtract(TimeUnitRange.CENTURY, unixDate);
+ assertTrue(c == (year > 0 ? (year + 99) / 100 : (year - 99) / 100));
+ final long m = unixDateExtract(TimeUnitRange.MILLENNIUM, unixDate);
+ assertTrue(m == (year > 0 ? (year + 999) / 1000 : (year - 999) / 1000));
}
@Test public void testAddMonths() {