You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by gr...@apache.org on 2014/05/16 15:33:24 UTC
git commit: PHOENIX-983 Add timezone offset function
Repository: incubator-phoenix
Updated Branches:
refs/heads/3.0 4ce243c1c -> 9236a1dcf
PHOENIX-983 Add timezone offset function
Add a function to retrieve the GMT offset (in minutes) for a given
timezone and point in time.
Signed-off-by: Gabriel Reid <ga...@ngdata.com>
Project: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/commit/9236a1dc
Tree: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/tree/9236a1dc
Diff: http://git-wip-us.apache.org/repos/asf/incubator-phoenix/diff/9236a1dc
Branch: refs/heads/3.0
Commit: 9236a1dcfcd737ac38437979d5b8917f84a84f12
Parents: 4ce243c
Author: tzolkincz <va...@socialbakers.com>
Authored: Wed Apr 2 18:19:35 2014 +0200
Committer: Gabriel Reid <ga...@ngdata.com>
Committed: Fri May 16 15:25:57 2014 +0200
----------------------------------------------------------------------
.../end2end/TimezoneOffsetFunctionIT.java | 157 +++++++++++++++++++
.../phoenix/expression/ExpressionType.java | 6 +-
.../function/TimezoneOffsetFunction.java | 97 ++++++++++++
3 files changed, 258 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/9236a1dc/phoenix-core/src/it/java/org/apache/phoenix/end2end/TimezoneOffsetFunctionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TimezoneOffsetFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TimezoneOffsetFunctionIT.java
new file mode 100644
index 0000000..0264574
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TimezoneOffsetFunctionIT.java
@@ -0,0 +1,157 @@
+/*
+ * 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 java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import org.apache.phoenix.schema.IllegalDataException;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+import org.junit.Test;
+
+public class TimezoneOffsetFunctionIT extends BaseHBaseManagedTimeIT {
+
+ @Test
+ public void testTimezoneOffset() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST"
+ + " (k1 INTEGER NOT NULL, dates DATE CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (1, TO_DATE('2014-02-02 00:00:00'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (2, TO_DATE('2014-06-02 00:00:00'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT k1, dates, TIMEZONE_OFFSET('Indian/Cocos', dates) FROM TIMEZONE_OFFSET_TEST");
+
+ assertTrue(rs.next());
+ assertEquals(390, rs.getInt(3));
+ assertTrue(rs.next());
+ assertEquals(390, rs.getInt(3));
+
+ }
+
+ @Test
+ public void testUnknownTimezone() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST"
+ + " (k1 INTEGER NOT NULL, dates DATE CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (1, TO_DATE('2014-02-02 00:00:00'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ try {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT k1, dates, TIMEZONE_OFFSET('Unknown_Timezone', dates) FROM TIMEZONE_OFFSET_TEST");
+
+ rs.next();
+ assertEquals(0, rs.getInt(3));
+ fail();
+ } catch (IllegalDataException e) {
+ assertTrue(true);
+ return;
+ }
+ fail();
+
+ }
+
+ @Test
+ public void testInRowKeyDSTTimezoneDesc() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST "
+ + "(k1 INTEGER NOT NULL, dates DATE NOT NULL CONSTRAINT pk PRIMARY KEY (k1, dates DESC))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (1, TO_DATE('2014-02-02 00:00:00'))";
+ conn.createStatement().execute(dml);
+ dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (2, TO_DATE('2014-06-02 00:00:00'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT k1, dates, TIMEZONE_OFFSET('Europe/Prague', dates)"
+ + "FROM TIMEZONE_OFFSET_TEST ORDER BY k1 ASC");
+
+ assertTrue(rs.next());
+ assertEquals(60, rs.getInt(3));
+ assertTrue(rs.next());
+ assertEquals(120, rs.getInt(3));
+ }
+
+ @Test
+ public void testBothParametersNull() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST "
+ + "(k1 INTEGER NOT NULL, dates DATE, v1 VARCHAR CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates, v1) VALUES (2, null, null)";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT k1, dates, TIMEZONE_OFFSET(v1, dates)"
+ + "FROM TIMEZONE_OFFSET_TEST ORDER BY k1 ASC");
+
+ assertTrue(rs.next());
+ rs.getInt(3);
+ assertTrue(rs.wasNull());
+ }
+
+ @Test
+ public void timezoneParameterNull() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST "
+ + "(k1 INTEGER NOT NULL, dates DATE, v1 VARCHAR CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates, v1) VALUES (2, TO_DATE('2014-06-02 00:00:00'), null)";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT k1, dates, TIMEZONE_OFFSET(v1, dates)"
+ + "FROM TIMEZONE_OFFSET_TEST ORDER BY k1 ASC");
+
+ assertTrue(rs.next());
+ rs.getInt(3);
+ assertTrue(rs.wasNull());
+ }
+
+ @Test
+ public void dateParameterNull() throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST "
+ + "(k1 INTEGER NOT NULL, dates DATE, v1 VARCHAR CONSTRAINT pk PRIMARY KEY (k1))";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates, v1) VALUES (2, null, 'Asia/Aden')";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT k1, dates, TIMEZONE_OFFSET(v1, dates)"
+ + "FROM TIMEZONE_OFFSET_TEST ORDER BY k1 ASC");
+
+ assertTrue(rs.next());
+ rs.getInt(3);
+ assertTrue(rs.wasNull());
+ }
+
+}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/9236a1dc/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 657f8fe..892e698 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
@@ -64,6 +64,7 @@ import org.apache.phoenix.expression.function.ToNumberFunction;
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.TimezoneOffsetFunction;
import com.google.common.collect.Maps;
@@ -72,8 +73,8 @@ import com.google.common.collect.Maps;
* Enumeration of all Expression types that may be evaluated on the server-side.
* Used during serialization and deserialization to pass Expression between client
* and server.
+ *
*
- *
* @since 0.1
*/
public enum ExpressionType {
@@ -153,7 +154,8 @@ public enum ExpressionType {
ArrayLengthFunction(ArrayLengthFunction.class),
ArrayConstructorExpression(ArrayConstructorExpression.class),
SQLViewTypeFunction(SQLViewTypeFunction.class),
- ExternalSqlTypeIdFunction(ExternalSqlTypeIdFunction.class);
+ ExternalSqlTypeIdFunction(ExternalSqlTypeIdFunction.class),
+ TimezoneOffsetFunction(TimezoneOffsetFunction.class);
ExpressionType(Class<? extends Expression> clazz) {
this.clazz = clazz;
}
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/9236a1dc/phoenix-core/src/main/java/org/apache/phoenix/expression/function/TimezoneOffsetFunction.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/TimezoneOffsetFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/TimezoneOffsetFunction.java
new file mode 100644
index 0000000..870e0fa
--- /dev/null
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/TimezoneOffsetFunction.java
@@ -0,0 +1,97 @@
+/*
+ * 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.Date;
+import java.sql.SQLException;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.TimeZone;
+import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.expression.Expression;
+import org.apache.phoenix.parse.FunctionParseNode;
+import org.apache.phoenix.schema.IllegalDataException;
+import org.apache.phoenix.schema.PDataType;
+import org.apache.phoenix.schema.tuple.Tuple;
+
+
+/**
+ * Returns offset (shift in minutes) of timezone at particular datetime in minutes.
+ */
+@FunctionParseNode.BuiltInFunction(name = TimezoneOffsetFunction.NAME, args = {
+ @FunctionParseNode.Argument(allowedTypes = {PDataType.VARCHAR}),
+ @FunctionParseNode.Argument(allowedTypes = {PDataType.DATE})})
+public class TimezoneOffsetFunction extends ScalarFunction {
+
+ public static final String NAME = "TIMEZONE_OFFSET";
+ private static final int MILLIS_TO_MINUTES = 60 * 1000;
+ private final Map<String, TimeZone> cachedTimeZones = new HashMap<String, TimeZone>();
+
+ public TimezoneOffsetFunction() {
+ }
+
+ public TimezoneOffsetFunction(List<Expression> children) throws SQLException {
+ super(children);
+ }
+
+ @Override
+ public String getName() {
+ return NAME;
+ }
+
+ @Override
+ public boolean evaluate(Tuple tuple, ImmutableBytesWritable ptr) {
+ if (!children.get(0).evaluate(tuple, ptr)) {
+ return false;
+ }
+
+ String timezone = Bytes.toString(ptr.get(), ptr.getOffset(), ptr.getLength());
+
+ if (!children.get(1).evaluate(tuple, ptr)) {
+ return false;
+ }
+
+ if (!cachedTimeZones.containsKey(timezone)) {
+ TimeZone tz = TimeZone.getTimeZone(timezone);
+ if (!tz.getID().equals(timezone)) {
+ throw new IllegalDataException("Invalid timezone " + timezone);
+ }
+ cachedTimeZones.put(timezone, tz);
+ }
+
+ Date date = (Date)PDataType.DATE.toObject(ptr, children.get(1).getSortOrder());
+ int offset = cachedTimeZones.get(timezone).getOffset(date.getTime());
+
+ ptr.set(PDataType.INTEGER.toBytes(offset / MILLIS_TO_MINUTES));
+ return true;
+ }
+
+ @Override
+ public PDataType getDataType() {
+ return PDataType.INTEGER;
+ }
+
+ @Override
+ public boolean isNullable() {
+ return children.get(0).isNullable() || children.get(1).isNullable();
+ }
+
+}