You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2015/04/14 02:08:06 UTC
[5/5] phoenix git commit: PHOENIX-1765 Add DAYOFMONTH built-in
function (Alicia Ying Shu)
PHOENIX-1765 Add DAYOFMONTH built-in function (Alicia Ying Shu)
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/0d78e48b
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/0d78e48b
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/0d78e48b
Branch: refs/heads/master
Commit: 0d78e48b579739fb85a64fe7258f1838dc1af2c8
Parents: 8975fc1
Author: James Taylor <jt...@salesforce.com>
Authored: Mon Apr 13 17:05:45 2015 -0700
Committer: James Taylor <jt...@salesforce.com>
Committed: Mon Apr 13 17:05:45 2015 -0700
----------------------------------------------------------------------
.../org/apache/phoenix/end2end/DateTimeIT.java | 281 +++++++++++++++++-
.../end2end/YearMonthSecondFunctionIT.java | 287 -------------------
.../phoenix/expression/ExpressionType.java | 4 +-
.../expression/function/DayOfMonthFunction.java | 83 ++++++
4 files changed, 365 insertions(+), 290 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/0d78e48b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java
index 371d82e..0db36df 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DateTimeIT.java
@@ -42,12 +42,15 @@ import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
+import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.Format;
import java.util.Calendar;
import org.apache.phoenix.util.DateUtil;
+import org.junit.After;
+import org.junit.Before;
import org.junit.Test;
@@ -59,12 +62,21 @@ public class DateTimeIT extends BaseHBaseManagedTimeIT {
public DateTimeIT() throws Exception {
super();
- conn = DriverManager.getConnection(getUrl());
date = new Date(System.currentTimeMillis());
+ }
+
+ @Before
+ public void setUp() throws SQLException {
+ conn = DriverManager.getConnection(getUrl());
initAtable();
}
- protected void initAtable() throws Exception {
+ @After
+ public void tearDown() throws SQLException {
+ conn.close();
+ }
+
+ private void initAtable() throws SQLException {
ensureTableCreated(getUrl(), ATABLE_NAME, (byte[][])null);
PreparedStatement stmt = conn.prepareStatement(
"upsert into " + ATABLE_NAME +
@@ -357,4 +369,269 @@ public class DateTimeIT extends BaseHBaseManagedTimeIT {
assertEquals(ROW1, rs.getString(1));
assertFalse(rs.next());
}
+
+ private static int callYearFunction(Connection conn, String invocation) throws SQLException {
+ Statement stmt = conn.createStatement();
+ ResultSet rs =
+ stmt.executeQuery(String
+ .format("SELECT %s FROM SYSTEM.CATALOG LIMIT 1", invocation));
+ assertTrue(rs.next());
+ int returnValue = rs.getInt(1);
+ assertFalse(rs.next());
+ rs.close();
+ stmt.close();
+ return returnValue;
+ }
+
+ private int callYearFunction(String invocation) throws SQLException {
+ return callYearFunction(conn, invocation);
+ }
+
+ @Test
+ public void testYearFunctionDate() throws SQLException {
+
+ assertEquals(2015, callYearFunction("YEAR(current_date())"));
+
+ assertEquals(2015, callYearFunction("YEAR(now())"));
+
+ assertEquals(2008, callYearFunction("YEAR(TO_DATE('2008-01-01', 'yyyy-MM-dd', 'local'))"));
+
+ assertEquals(2004,
+ callYearFunction("YEAR(TO_DATE('2004-12-13 10:13:18', 'yyyy-MM-dd hh:mm:ss'))"));
+
+ assertEquals(2015, callYearFunction("YEAR(TO_DATE('2015-01-27T16:17:57+00:00'))"));
+
+ assertEquals(2005, callYearFunction("YEAR(TO_DATE('2005-12-13 10:13:18'))"));
+
+ assertEquals(2006, callYearFunction("YEAR(TO_DATE('2006-12-13'))"));
+
+ assertEquals(2015, callYearFunction("YEAR(TO_DATE('2015-W05'))"));
+
+ assertEquals(
+ 2008,
+ callYearFunction("YEAR(TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'))"));
+ }
+
+ @Test
+ public void testYearFunctionTimestamp() throws SQLException {
+
+ assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-01-27T16:17:57+00:00'))"));
+
+ assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-01-27T16:17:57Z'))"));
+
+ assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-W10-3'))"));
+
+ assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-W05'))"));
+
+ assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-063'))"));
+
+ assertEquals(2006, callYearFunction("YEAR(TO_TIMESTAMP('2006-12-13'))"));
+
+ assertEquals(2004,
+ callYearFunction("YEAR(TO_TIMESTAMP('2004-12-13 10:13:18', 'yyyy-MM-dd hh:mm:ss'))"));
+
+ assertEquals(
+ 2008,
+ callYearFunction("YEAR(TO_TIMESTAMP('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'))"));
+ }
+
+ @Test
+ public void testYearFuncAgainstColumns() throws Exception {
+ String ddl =
+ "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " +
+ "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " +
+ "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2005-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " +
+ "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (3, TO_DATE('2006-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " +
+ "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT k1, YEAR(timestamps), YEAR(times), Year(unsignedDates), YEAR(unsignedTimestamps), " +
+ "YEAR(unsignedTimes) FROM T1 where YEAR(dates) = 2004");
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(2006, rs.getInt(2));
+ assertEquals(2008, rs.getInt(3));
+ assertEquals(2010, rs.getInt(4));
+ assertEquals(2012, rs.getInt(5));
+ assertEquals(2015, rs.getInt(6));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testMonthFuncAgainstColumns() throws Exception {
+ String ddl =
+ "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " +
+ "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " +
+ "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2004-04-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " +
+ "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (3, TO_DATE('2004-05-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " +
+ "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT k1, MONTH(timestamps), MONTH(times), MONTH(unsignedDates), MONTH(unsignedTimestamps), " +
+ "MONTH(unsignedTimes) FROM T1 where MONTH(dates) = 3");
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(4, rs.getInt(2));
+ assertEquals(5, rs.getInt(3));
+ assertEquals(6, rs.getInt(4));
+ assertEquals(7, rs.getInt(5));
+ assertEquals(12, rs.getInt(6));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testSecondFuncAgainstColumns() throws Exception {
+ String ddl =
+ "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " +
+ "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:00:20'), TO_TIME('2008-05-16 10:00:30'), " +
+ "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:20:30'), TO_TIME('2008-05-16 10:00:30'), " +
+ "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (3, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:50:30'), TO_TIME('2008-05-16 10:00:30'), " +
+ "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT k1, SECOND(dates), SECOND(times), SECOND(unsignedDates), SECOND(unsignedTimestamps), " +
+ "SECOND(unsignedTimes) FROM T1 where SECOND(timestamps)=20");
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(10, rs.getInt(2));
+ assertEquals(30, rs.getInt(3));
+ assertEquals(40, rs.getInt(4));
+ assertEquals(0, rs.getInt(5));
+ assertEquals(50, rs.getInt(6));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testWeekFuncAgainstColumns() throws Exception {
+ String ddl =
+ "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-01-10 10:00:10'), TO_TIMESTAMP('2006-04-12 08:00:20'), TO_TIME('2008-05-16 10:00:30'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2004-01-10 10:00:10'), TO_TIMESTAMP('2006-05-18 08:00:20'), TO_TIME('2008-05-16 10:00:30'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (3, TO_DATE('2004-01-10 10:00:10'), TO_TIMESTAMP('2006-05-18 08:00:20'), TO_TIME('2008-05-16 10:00:30'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT k1, WEEK(dates), WEEK(times) FROM T1 where WEEK(timestamps)=15");
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(2, rs.getInt(2));
+ assertEquals(20, rs.getInt(3));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testHourFuncAgainstColumns() throws Exception {
+ String ddl =
+ "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'), TO_TIMESTAMP('2006-04-12 15:10:20'), " +
+ "TO_TIME('2008-05-16 20:40:30'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (2, TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'), TO_TIMESTAMP('2006-04-12 10:10:20'), " +
+ "TO_TIME('2008-05-16 20:40:30'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (3, TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'), TO_TIMESTAMP('2006-04-12 08:10:20'), " +
+ "TO_TIME('2008-05-16 20:40:30'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT k1, HOUR(dates), HOUR(times) FROM T1 where HOUR(timestamps)=15");
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(3, rs.getInt(2));
+ assertEquals(20, rs.getInt(3));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testNowFunction() throws Exception {
+ Date date = new Date(System.currentTimeMillis());
+ String ddl =
+ "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, timestamps TIMESTAMP CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO T1 VALUES (?, ?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.setInt(1, 1);
+ stmt.setDate(2, new Date(date.getTime()-500));
+ stmt.execute();
+ stmt.setInt(1, 2);
+ stmt.setDate(2, new Date(date.getTime()+600000));
+ stmt.execute();
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT * from T1 where now() > timestamps");
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(new Date(date.getTime()-500), rs.getDate(2));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testMinuteFuncAgainstColumns() throws Exception {
+ String ddl =
+ "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " +
+ "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-01 00:10:10'), TO_TIMESTAMP('2006-04-12 00:20:20'), TO_TIME('2008-05-16 10:30:30'), " +
+ "TO_DATE('2010-06-20 00:40:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:50:50'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2004-03-01 00:10:10'), TO_TIMESTAMP('2006-04-12 00:50:20'), TO_TIME('2008-05-16 10:30:30'), " +
+ "TO_DATE('2010-06-20 00:40:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:50:50'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT k1, MINUTE(dates), MINUTE(times), MINUTE(unsignedDates), MINUTE(unsignedTimestamps), " +
+ "MINUTE(unsignedTimes) FROM T1 where MINUTE(timestamps)=20");
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(10, rs.getInt(2));
+ assertEquals(30, rs.getInt(3));
+ assertEquals(40, rs.getInt(4));
+ assertEquals(0, rs.getInt(5));
+ assertEquals(50, rs.getInt(6));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testDayOfMonthFuncAgainstColumns() throws Exception {
+ String ddl =
+ "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-01-08 10:00:10'), TO_TIMESTAMP('2006-04-12 08:00:20'), TO_TIME('2008-05-26 11:00:30'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2004-01-18 10:00:10'), TO_TIMESTAMP('2006-05-22 08:00:20'), TO_TIME('2008-12-30 11:00:30'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT k1, DAYOFMONTH(dates), DAYOFMONTH(times) FROM T1 where DAYOFMONTH(timestamps)=12");
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(8, rs.getInt(2));
+ assertEquals(26, rs.getInt(3));
+ assertFalse(rs.next());
+ }
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/0d78e48b/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java
deleted file mode 100644
index 1206ee4..0000000
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java
+++ /dev/null
@@ -1,287 +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.phoenix.end2end;
-
-import static org.junit.Assert.assertEquals;
-import static org.junit.Assert.assertFalse;
-import static org.junit.Assert.assertTrue;
-
-import java.sql.Connection;
-import java.sql.Date;
-import java.sql.DriverManager;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
-
-import org.junit.After;
-import org.junit.Before;
-import org.junit.Test;
-
-public class YearMonthSecondFunctionIT extends BaseHBaseManagedTimeIT {
- private Connection conn;
-
- @Before
- public void setUp() throws SQLException {
- conn = DriverManager.getConnection(getUrl());
- }
-
- @After
- public void tearDown() throws SQLException {
- conn.close();
- }
-
- private static int callYearFunction(Connection conn, String invocation) throws SQLException {
- Statement stmt = conn.createStatement();
- ResultSet rs =
- stmt.executeQuery(String
- .format("SELECT %s FROM SYSTEM.CATALOG LIMIT 1", invocation));
- assertTrue(rs.next());
- int returnValue = rs.getInt(1);
- assertFalse(rs.next());
- rs.close();
- stmt.close();
- return returnValue;
- }
-
- private int callYearFunction(String invocation) throws SQLException {
- return callYearFunction(conn, invocation);
- }
-
- @Test
- public void testYearFunctionDate() throws SQLException {
-
- assertEquals(2015, callYearFunction("YEAR(current_date())"));
-
- assertEquals(2015, callYearFunction("YEAR(now())"));
-
- assertEquals(2008, callYearFunction("YEAR(TO_DATE('2008-01-01', 'yyyy-MM-dd', 'local'))"));
-
- assertEquals(2004,
- callYearFunction("YEAR(TO_DATE('2004-12-13 10:13:18', 'yyyy-MM-dd hh:mm:ss'))"));
-
- assertEquals(2015, callYearFunction("YEAR(TO_DATE('2015-01-27T16:17:57+00:00'))"));
-
- assertEquals(2005, callYearFunction("YEAR(TO_DATE('2005-12-13 10:13:18'))"));
-
- assertEquals(2006, callYearFunction("YEAR(TO_DATE('2006-12-13'))"));
-
- assertEquals(2015, callYearFunction("YEAR(TO_DATE('2015-W05'))"));
-
- assertEquals(
- 2008,
- callYearFunction("YEAR(TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'))"));
- }
-
- @Test
- public void testYearFunctionTimestamp() throws SQLException {
-
- assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-01-27T16:17:57+00:00'))"));
-
- assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-01-27T16:17:57Z'))"));
-
- assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-W10-3'))"));
-
- assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-W05'))"));
-
- assertEquals(2015, callYearFunction("YEAR(TO_TIMESTAMP('2015-063'))"));
-
- assertEquals(2006, callYearFunction("YEAR(TO_TIMESTAMP('2006-12-13'))"));
-
- assertEquals(2004,
- callYearFunction("YEAR(TO_TIMESTAMP('2004-12-13 10:13:18', 'yyyy-MM-dd hh:mm:ss'))"));
-
- assertEquals(
- 2008,
- callYearFunction("YEAR(TO_TIMESTAMP('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'))"));
- }
-
- @Test
- public void testYearFuncAgainstColumns() throws Exception {
- String ddl =
- "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " +
- "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))";
- conn.createStatement().execute(ddl);
- String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " +
- "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2005-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " +
- "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (3, TO_DATE('2006-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " +
- "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))";
- conn.createStatement().execute(dml);
- conn.commit();
-
- ResultSet rs = conn.createStatement().executeQuery("SELECT k1, YEAR(timestamps), YEAR(times), Year(unsignedDates), YEAR(unsignedTimestamps), " +
- "YEAR(unsignedTimes) FROM T1 where YEAR(dates) = 2004");
- assertTrue(rs.next());
- assertEquals(1, rs.getInt(1));
- assertEquals(2006, rs.getInt(2));
- assertEquals(2008, rs.getInt(3));
- assertEquals(2010, rs.getInt(4));
- assertEquals(2012, rs.getInt(5));
- assertEquals(2015, rs.getInt(6));
- assertFalse(rs.next());
- }
-
- @Test
- public void testMonthFuncAgainstColumns() throws Exception {
- String ddl =
- "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " +
- "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))";
- conn.createStatement().execute(ddl);
- String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " +
- "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2004-04-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " +
- "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (3, TO_DATE('2004-05-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " +
- "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))";
- conn.createStatement().execute(dml);
- conn.commit();
-
- ResultSet rs = conn.createStatement().executeQuery("SELECT k1, MONTH(timestamps), MONTH(times), MONTH(unsignedDates), MONTH(unsignedTimestamps), " +
- "MONTH(unsignedTimes) FROM T1 where MONTH(dates) = 3");
- assertTrue(rs.next());
- assertEquals(1, rs.getInt(1));
- assertEquals(4, rs.getInt(2));
- assertEquals(5, rs.getInt(3));
- assertEquals(6, rs.getInt(4));
- assertEquals(7, rs.getInt(5));
- assertEquals(12, rs.getInt(6));
- assertFalse(rs.next());
- }
-
- @Test
- public void testSecondFuncAgainstColumns() throws Exception {
- String ddl =
- "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " +
- "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))";
- conn.createStatement().execute(ddl);
- String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:00:20'), TO_TIME('2008-05-16 10:00:30'), " +
- "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:20:30'), TO_TIME('2008-05-16 10:00:30'), " +
- "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (3, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:50:30'), TO_TIME('2008-05-16 10:00:30'), " +
- "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))";
- conn.createStatement().execute(dml);
- conn.commit();
-
- ResultSet rs = conn.createStatement().executeQuery("SELECT k1, SECOND(dates), SECOND(times), SECOND(unsignedDates), SECOND(unsignedTimestamps), " +
- "SECOND(unsignedTimes) FROM T1 where SECOND(timestamps)=20");
- assertTrue(rs.next());
- assertEquals(1, rs.getInt(1));
- assertEquals(10, rs.getInt(2));
- assertEquals(30, rs.getInt(3));
- assertEquals(40, rs.getInt(4));
- assertEquals(0, rs.getInt(5));
- assertEquals(50, rs.getInt(6));
- assertFalse(rs.next());
- }
-
- @Test
- public void testWeekFuncAgainstColumns() throws Exception {
- String ddl =
- "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))";
- conn.createStatement().execute(ddl);
- String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-01-10 10:00:10'), TO_TIMESTAMP('2006-04-12 08:00:20'), TO_TIME('2008-05-16 10:00:30'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2004-01-10 10:00:10'), TO_TIMESTAMP('2006-05-18 08:00:20'), TO_TIME('2008-05-16 10:00:30'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (3, TO_DATE('2004-01-10 10:00:10'), TO_TIMESTAMP('2006-05-18 08:00:20'), TO_TIME('2008-05-16 10:00:30'))";
- conn.createStatement().execute(dml);
- conn.commit();
-
- ResultSet rs = conn.createStatement().executeQuery("SELECT k1, WEEK(dates), WEEK(times) FROM T1 where WEEK(timestamps)=15");
- assertTrue(rs.next());
- assertEquals(1, rs.getInt(1));
- assertEquals(2, rs.getInt(2));
- assertEquals(20, rs.getInt(3));
- assertFalse(rs.next());
- }
-
- @Test
- public void testHourFuncAgainstColumns() throws Exception {
- String ddl =
- "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))";
- conn.createStatement().execute(ddl);
- String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'), TO_TIMESTAMP('2006-04-12 15:10:20'), " +
- "TO_TIME('2008-05-16 20:40:30'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (2, TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'), TO_TIMESTAMP('2006-04-12 10:10:20'), " +
- "TO_TIME('2008-05-16 20:40:30'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (3, TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'), TO_TIMESTAMP('2006-04-12 08:10:20'), " +
- "TO_TIME('2008-05-16 20:40:30'))";
- conn.createStatement().execute(dml);
- conn.commit();
-
- ResultSet rs = conn.createStatement().executeQuery("SELECT k1, HOUR(dates), HOUR(times) FROM T1 where HOUR(timestamps)=15");
- assertTrue(rs.next());
- assertEquals(1, rs.getInt(1));
- assertEquals(3, rs.getInt(2));
- assertEquals(20, rs.getInt(3));
- assertFalse(rs.next());
- }
-
- @Test
- public void testNowFunction() throws Exception {
- Date date = new Date(System.currentTimeMillis());
- String ddl =
- "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, timestamps TIMESTAMP CONSTRAINT pk PRIMARY KEY (k1))";
- conn.createStatement().execute(ddl);
- String dml = "UPSERT INTO T1 VALUES (?, ?)";
- PreparedStatement stmt = conn.prepareStatement(dml);
- stmt.setInt(1, 1);
- stmt.setDate(2, new Date(date.getTime()-500));
- stmt.execute();
- stmt.setInt(1, 2);
- stmt.setDate(2, new Date(date.getTime()+600000));
- stmt.execute();
- conn.commit();
-
- ResultSet rs = conn.createStatement().executeQuery("SELECT * from T1 where now() > timestamps");
- assertTrue(rs.next());
- assertEquals(1, rs.getInt(1));
- assertEquals(new Date(date.getTime()-500), rs.getDate(2));
- assertFalse(rs.next());
- }
-
- @Test
- public void testMinuteFuncAgainstColumns() throws Exception {
- String ddl =
- "CREATE TABLE IF NOT EXISTS T1 (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " +
- "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))";
- conn.createStatement().execute(ddl);
- String dml = "UPSERT INTO T1 VALUES (1, TO_DATE('2004-03-01 00:10:10'), TO_TIMESTAMP('2006-04-12 00:20:20'), TO_TIME('2008-05-16 10:30:30'), " +
- "TO_DATE('2010-06-20 00:40:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:50:50'))";
- conn.createStatement().execute(dml);
- dml = "UPSERT INTO T1 VALUES (2, TO_DATE('2004-03-01 00:10:10'), TO_TIMESTAMP('2006-04-12 00:50:20'), TO_TIME('2008-05-16 10:30:30'), " +
- "TO_DATE('2010-06-20 00:40:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:50:50'))";
- conn.createStatement().execute(dml);
- conn.commit();
-
- ResultSet rs = conn.createStatement().executeQuery("SELECT k1, MINUTE(dates), MINUTE(times), MINUTE(unsignedDates), MINUTE(unsignedTimestamps), " +
- "MINUTE(unsignedTimes) FROM T1 where MINUTE(timestamps)=20");
- assertTrue(rs.next());
- assertEquals(1, rs.getInt(1));
- assertEquals(10, rs.getInt(2));
- assertEquals(30, rs.getInt(3));
- assertEquals(40, rs.getInt(4));
- assertEquals(0, rs.getInt(5));
- assertEquals(50, rs.getInt(6));
- assertFalse(rs.next());
- }
-}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/phoenix/blob/0d78e48b/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java
index d42c5f2..d562d6a 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java
@@ -31,6 +31,7 @@ import org.apache.phoenix.expression.function.CeilTimestampExpression;
import org.apache.phoenix.expression.function.CoalesceFunction;
import org.apache.phoenix.expression.function.ConvertTimezoneFunction;
import org.apache.phoenix.expression.function.CountAggregateFunction;
+import org.apache.phoenix.expression.function.DayOfMonthFunction;
import org.apache.phoenix.expression.function.DecodeFunction;
import org.apache.phoenix.expression.function.DistinctCountAggregateFunction;
import org.apache.phoenix.expression.function.DistinctValueAggregateFunction;
@@ -209,7 +210,8 @@ public enum ExpressionType {
HourFunction(HourFunction.class),
NowFunction(NowFunction.class),
InstrFunction(InstrFunction.class),
- MinuteFunction(MinuteFunction.class)
+ MinuteFunction(MinuteFunction.class),
+ DayOfMonthFunction(DayOfMonthFunction.class)
;
ExpressionType(Class<? extends Expression> clazz) {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/0d78e48b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfMonthFunction.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfMonthFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfMonthFunction.java
new file mode 100644
index 0000000..0c328cf
--- /dev/null
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfMonthFunction.java
@@ -0,0 +1,83 @@
+/*
+ * 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.phoenix.expression.function;
+
+import java.sql.SQLException;
+import java.util.List;
+
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.parse.FunctionParseNode.Argument;
+import org.apache.phoenix.parse.FunctionParseNode.BuiltInFunction;
+import org.apache.phoenix.schema.tuple.Tuple;
+import org.apache.phoenix.schema.types.PDataType;
+import org.apache.phoenix.schema.types.PInteger;
+import org.apache.phoenix.schema.types.PTimestamp;
+import org.joda.time.DateTime;
+
+/**
+ *
+ * Implementation of the DayOfMonth() buildin. Input Date/Timestamp.
+ * An integer from 1 to 31 representing the day of the month in date
+ *
+ */
+@BuiltInFunction(name=DayOfMonthFunction.NAME,
+args={@Argument(allowedTypes={PTimestamp.class})})
+public class DayOfMonthFunction extends ScalarFunction {
+ public static final String NAME = "DAYOFMONTH";
+
+ public DayOfMonthFunction() {
+ }
+
+ public DayOfMonthFunction(List<Expression> children) throws SQLException {
+ super(children);
+ }
+
+ @Override
+ public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
+ Expression expression = getChildExpression();
+ if (!expression.evaluate(tuple, ptr)) {
+ return false;
+ }
+ if ( ptr.getLength() == 0) {
+ return true; //means null
+ }
+ long dateTime = expression.getDataType().getCodec().decodeLong(ptr, expression.getSortOrder());
+ DateTime dt = new DateTime(dateTime);
+ int day = dt.getDayOfMonth();
+ PDataType returnType = getDataType();
+ byte[] byteValue = new byte[returnType.getByteSize()];
+ returnType.getCodec().encodeInt(day, byteValue, 0);
+ ptr.set(byteValue);
+ return true;
+ }
+
+ @Override
+ public PDataType getDataType() {
+ return PInteger.INSTANCE;
+ }
+
+ @Override
+ public String getName() {
+ return NAME;
+ }
+
+ private Expression getChildExpression() {
+ return children.get(0);
+ }
+}