Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id A36C6200B84 for ; Tue, 20 Sep 2016 20:05:26 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id A2239160AF6; Tue, 20 Sep 2016 18:05:26 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 803FE160AC9 for ; Tue, 20 Sep 2016 20:05:25 +0200 (CEST) Received: (qmail 70598 invoked by uid 500); 20 Sep 2016 18:05:24 -0000 Mailing-List: contact commits-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list commits@phoenix.apache.org Received: (qmail 70513 invoked by uid 99); 20 Sep 2016 18:05:24 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 20 Sep 2016 18:05:24 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 75A0BE0BDD; Tue, 20 Sep 2016 18:05:24 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: maryannxue@apache.org To: commits@phoenix.apache.org Date: Tue, 20 Sep 2016 18:05:26 -0000 Message-Id: In-Reply-To: <2c7077c5d0824268ad367450fda7d7aa@git.apache.org> References: <2c7077c5d0824268ad367450fda7d7aa@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [03/47] phoenix git commit: Implement DAYOFWEEK and DAYOFYEAR built-in functions (Prakul Agarwal) archived-at: Tue, 20 Sep 2016 18:05:26 -0000 Implement DAYOFWEEK and DAYOFYEAR built-in functions (Prakul Agarwal) Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/fce0f103 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/fce0f103 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/fce0f103 Branch: refs/heads/calcite Commit: fce0f1033e252ecb55d168f63284be330a8fbda7 Parents: 06d37e5 Author: Samarth Authored: Thu Sep 1 15:32:42 2016 -0700 Committer: Samarth Committed: Thu Sep 1 15:32:42 2016 -0700 ---------------------------------------------------------------------- .../org/apache/phoenix/end2end/DateTimeIT.java | 45 ++++++++++ .../phoenix/expression/ExpressionType.java | 9 +- .../expression/function/DayOfWeekFunction.java | 86 ++++++++++++++++++++ .../expression/function/DayOfYearFunction.java | 77 ++++++++++++++++++ 4 files changed, 215 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/fce0f103/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 227374a..05a8ec8 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 @@ -688,6 +688,51 @@ public class DateTimeIT extends BaseHBaseManagedTimeTableReuseIT { assertFalse(rs.next()); } + /* + Reference for dates used in the test + 2013-04-09 - Tuesday (2) + 2014-05-18 - Sunday (7) + 2015-06-27 - Saturday (6) + */ + @Test + public void testDayOfWeekFuncAgainstColumns() throws Exception { + String tableName = generateRandomString(); + String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))"; + conn.createStatement().execute(ddl); + String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2012-03-08 11:01:10'), TO_TIMESTAMP('2013-06-16 12:02:20'), TO_TIME('2014-09-23 13:03:30'))"; + conn.createStatement().execute(dml); + dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2013-04-09 11:02:10'), TO_TIMESTAMP('2014-05-18 12:03:20'), TO_TIME('2015-06-27 13:04:30'))"; + conn.createStatement().execute(dml); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery("SELECT k1, DAYOFWEEK(dates), DAYOFWEEK(timestamps) FROM " + tableName + " where DAYOFWEEK(times)=6"); + assertTrue(rs.next()); + assertEquals(2, rs.getInt(1)); + assertEquals(2, rs.getInt(2)); + assertEquals(7, rs.getInt(3)); + assertFalse(rs.next()); + } + + @Test + public void testDayOfYearFuncAgainstColumns() throws Exception { + String tableName = generateRandomString(); + String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))"; + conn.createStatement().execute(ddl); + String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2012-03-01 11:01:10'), TO_TIMESTAMP('2013-02-01 12:02:20'), TO_TIME('2014-01-15 13:03:30'))"; + conn.createStatement().execute(dml); + dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2013-04-09 11:02:10'), TO_TIMESTAMP('2014-05-18 12:03:20'), TO_TIME('2015-06-27 13:04:30'))"; + conn.createStatement().execute(dml); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery("SELECT k1, DAYOFYEAR(dates), DAYOFYEAR(timestamps) FROM " + tableName + " where DAYOFYEAR(times)=15"); + + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + assertEquals(61, rs.getInt(2)); + assertEquals(32, rs.getInt(3)); + assertFalse(rs.next()); + } + @Test public void testNullDate() throws Exception { http://git-wip-us.apache.org/repos/asf/phoenix/blob/fce0f103/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 f13e265..94120e9 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 @@ -125,6 +125,8 @@ import org.apache.phoenix.expression.function.UDFExpression; import org.apache.phoenix.expression.function.UpperFunction; import org.apache.phoenix.expression.function.WeekFunction; import org.apache.phoenix.expression.function.YearFunction; +import org.apache.phoenix.expression.function.DayOfWeekFunction; +import org.apache.phoenix.expression.function.DayOfYearFunction; import com.google.common.collect.Maps; @@ -281,8 +283,11 @@ public enum ExpressionType { FloorYearExpression(FloorYearExpression.class), CeilWeekExpression(CeilWeekExpression.class), CeilMonthExpression(CeilMonthExpression.class), - CeilYearExpression(CeilYearExpression.class); - ; + CeilYearExpression(CeilYearExpression.class), + DayOfWeekFunction(DayOfWeekFunction.class), + DayOfYearFunction(DayOfYearFunction.class); + + ExpressionType(Class clazz) { this.clazz = clazz; http://git-wip-us.apache.org/repos/asf/phoenix/blob/fce0f103/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfWeekFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfWeekFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfWeekFunction.java new file mode 100644 index 0000000..0330b83 --- /dev/null +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfWeekFunction.java @@ -0,0 +1,86 @@ +/* + * 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 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; + +import java.util.List; + +/** + * Implementation of DayOfWeekFunction(Date/Timestamp) + * + * Returns an integer from 1 to 7. Each represents a day of the week as follows : + * MONDAY = 1; + * TUESDAY = 2; + * WEDNESDAY = 3; + * THURSDAY = 4; + * FRIDAY = 5; + * SATURDAY = 6; + * SUNDAY = 7; + * + */ +@BuiltInFunction(name=DayOfWeekFunction.NAME, + args={@Argument(allowedTypes={PTimestamp.class})}) +public class DayOfWeekFunction extends ScalarFunction { + public static final String NAME = "DAYOFWEEK"; + + public DayOfWeekFunction(){ + + } + + public DayOfWeekFunction(List children){ + super(children); + } + @Override + public String getName() { + return NAME; + } + + @Override + public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) { + Expression arg = getChildren().get(0); + if (!arg.evaluate(tuple,ptr)) { + return false; + } + if (ptr.getLength() == 0) { + return true; + } + long dateTime = arg.getDataType().getCodec().decodeLong(ptr, arg.getSortOrder()); + DateTime jodaDT = new DateTime(dateTime); + int day = jodaDT.getDayOfWeek(); + PDataType returnDataType = getDataType(); + byte[] byteValue = new byte[returnDataType.getByteSize()]; + returnDataType.getCodec().encodeInt(day, byteValue, 0); + ptr.set(byteValue); + return true; + } + + + @Override + public PDataType getDataType() { + return PInteger.INSTANCE; + } +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/fce0f103/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfYearFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfYearFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfYearFunction.java new file mode 100644 index 0000000..d4e92d6 --- /dev/null +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/DayOfYearFunction.java @@ -0,0 +1,77 @@ +/* + * 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 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; +import java.util.List; + +/** + * Implementation of DayOfYearFunction(Date/Timestamp) + * + * Returns an integer from 1 to 365 (for each day of the week). Returns 366 in a leap year. + * + */ +@BuiltInFunction(name=DayOfYearFunction.NAME, + args={@Argument(allowedTypes={PTimestamp.class})}) +public class DayOfYearFunction extends ScalarFunction { + public static final String NAME = "DAYOFYEAR"; + + public DayOfYearFunction() { + } + + public DayOfYearFunction(List children) { + super(children); + } + + @Override + public String getName() { + return NAME; + } + + @Override + public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) { + Expression arg = getChildren().get(0); + if (!arg.evaluate(tuple,ptr)) { + return false; + } + if (ptr.getLength() == 0) { + return true; + } + long dateTime = arg.getDataType().getCodec().decodeLong(ptr, arg.getSortOrder()); + DateTime jodaDT = new DateTime(dateTime); + int day = jodaDT.getDayOfYear(); + PDataType returnDataType = getDataType(); + byte[] byteValue = new byte[returnDataType.getByteSize()]; + returnDataType.getCodec().encodeInt(day, byteValue, 0); + ptr.set(byteValue); + return true; + } + + @Override + public PDataType getDataType() { + return PInteger.INSTANCE; + } +}