PHOENIX-1792 Add Week() and Hour() built-ins(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/d05d7c86 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/d05d7c86 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/d05d7c86 Branch: refs/heads/calcite Commit: d05d7c86c1de64a88cf8fb7ff119b1e1208f9929 Parents: 2bf8c67 Author: Rajeshbabu Chintaguntla <rajeshb...@apache.org> Authored: Tue Mar 31 14:15:09 2015 +0530 Committer: Rajeshbabu Chintaguntla <rajeshb...@apache.org> Committed: Tue Mar 31 14:15:09 2015 +0530 ---------------------------------------------------------------------- .../end2end/YearMonthSecondFunctionIT.java | 36 +++++++++ .../phoenix/expression/ExpressionType.java | 6 +- .../expression/function/HourFunction.java | 81 +++++++++++++++++++ .../expression/function/WeekFunction.java | 83 ++++++++++++++++++++ 4 files changed, 205 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/d05d7c86/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 index da745fe..3742a17 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/YearMonthSecondFunctionIT.java @@ -168,4 +168,40 @@ public class YearMonthSecondFunctionIT extends BaseHBaseManagedTimeIT { 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-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00: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(9, 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); + conn.commit(); + + ResultSet rs = conn.createStatement().executeQuery("SELECT k1, HOUR(dates), HOUR(timestamps), HOUR(times) FROM T1"); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + assertEquals(3, rs.getInt(2)); + assertEquals(15, rs.getInt(3)); + assertEquals(20, rs.getInt(4)); + assertFalse(rs.next()); + } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/phoenix/blob/d05d7c86/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 92dd1d4..8a2f127 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 @@ -40,6 +40,7 @@ import org.apache.phoenix.expression.function.FirstValueFunction; import org.apache.phoenix.expression.function.FloorDateExpression; import org.apache.phoenix.expression.function.FloorDecimalExpression; import org.apache.phoenix.expression.function.FloorFunction; +import org.apache.phoenix.expression.function.HourFunction; import org.apache.phoenix.expression.function.IndexStateNameFunction; import org.apache.phoenix.expression.function.InvertFunction; import org.apache.phoenix.expression.function.LTrimFunction; @@ -84,6 +85,7 @@ import org.apache.phoenix.expression.function.ToTimestampFunction; import org.apache.phoenix.expression.function.TrimFunction; import org.apache.phoenix.expression.function.TruncFunction; import org.apache.phoenix.expression.function.UpperFunction; +import org.apache.phoenix.expression.function.WeekFunction; import org.apache.phoenix.expression.function.YearFunction; import com.google.common.collect.Maps; @@ -199,7 +201,9 @@ public enum ExpressionType { SignFunction(SignFunction.class), YearFunction(YearFunction.class), MonthFunction(MonthFunction.class), - SecondFunction(SecondFunction.class) + SecondFunction(SecondFunction.class), + WeekFunction(WeekFunction.class), + HourFunction(HourFunction.class) ; ExpressionType(Class<? extends Expression> clazz) { http://git-wip-us.apache.org/repos/asf/phoenix/blob/d05d7c86/phoenix-core/src/main/java/org/apache/phoenix/expression/function/HourFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/HourFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/HourFunction.java new file mode 100644 index 0000000..0e9efd8 --- /dev/null +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/HourFunction.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.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; + +/** + * + * Implementation of the HOUR() buildin. Input Date/Timestamp/Time. + * Returns an integer from 0 to 23 representing the hour component of time + * + */ +@BuiltInFunction(name=HourFunction.NAME, +args={@Argument(allowedTypes={PTimestamp.class})}) +public class HourFunction extends ScalarFunction { + public static final String NAME = "HOUR"; + + public HourFunction() { + } + + public HourFunction(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()); + int hour = (int)(((dateTime/1000) % (24*3600))/3600); + PDataType returnType = getDataType(); + byte[] byteValue = new byte[returnType.getByteSize()]; + returnType.getCodec().encodeInt(hour, 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); + } +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/d05d7c86/phoenix-core/src/main/java/org/apache/phoenix/expression/function/WeekFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/WeekFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/WeekFunction.java new file mode 100644 index 0000000..126aba8 --- /dev/null +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/WeekFunction.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 WEEK() buildin. Input Date/Timestamp. + * Returns an integer from 1 to 53 representing the week of the year in date + * + */ +@BuiltInFunction(name=WeekFunction.NAME, +args={@Argument(allowedTypes={PTimestamp.class})}) +public class WeekFunction extends ScalarFunction { + public static final String NAME = "WEEK"; + + public WeekFunction() { + } + + public WeekFunction(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 week = dt.getWeekOfWeekyear(); + PDataType returnType = getDataType(); + byte[] byteValue = new byte[returnType.getByteSize()]; + returnType.getCodec().encodeInt(week, 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); + } +}