Repository: phoenix
Updated Branches:
  refs/heads/3.0 d29254079 -> e4218a99c


PHOENIX-1043 Add timezone conversion function

Contributed by Vaclav Loffelmann


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/e4218a99
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/e4218a99
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/e4218a99

Branch: refs/heads/3.0
Commit: e4218a99c870f50772be51fe94d35f53d414cf63
Parents: d292540
Author: Gabriel Reid <gabri...@ngdata.com>
Authored: Sat Jun 14 22:37:34 2014 +0200
Committer: Gabriel Reid <gabri...@ngdata.com>
Committed: Sat Jun 14 22:52:20 2014 +0200

----------------------------------------------------------------------
 .../end2end/ConvertTimezoneFunctionIT.java      | 132 +++++++++++++++++++
 .../phoenix/expression/ExpressionType.java      |   2 +
 .../function/ConvertTimezoneFunction.java       | 101 ++++++++++++++
 3 files changed, 235 insertions(+)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/e4218a99/phoenix-core/src/it/java/org/apache/phoenix/end2end/ConvertTimezoneFunctionIT.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ConvertTimezoneFunctionIT.java
 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ConvertTimezoneFunctionIT.java
new file mode 100644
index 0000000..4cdf6ac
--- /dev/null
+++ 
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ConvertTimezoneFunctionIT.java
@@ -0,0 +1,132 @@
+/*
+ * Copyright 2014 Apache Software Foundation.
+ *
+ * Licensed 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 java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import org.apache.phoenix.schema.IllegalDataException;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+import org.junit.Test;
+
+/**
+ * CONVERT_TZ(date, 'from_timezone', 'to_timezone') tests
+ *
+ */
+public class ConvertTimezoneFunctionIT extends BaseHBaseManagedTimeIT {
+
+    @Test
+    public void testConvertTimezoneEurope() 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-03-01 00:00:00'))";
+        conn.createStatement().execute(dml);
+        conn.commit();
+
+        ResultSet rs = conn.createStatement().executeQuery(
+                "SELECT k1, dates, CONVERT_TZ(dates, 'UTC', 'Europe/Prague') 
FROM TIMEZONE_OFFSET_TEST");
+
+        assertTrue(rs.next());
+        assertEquals(1393635600000L, rs.getDate(3).getTime()); //Sat, 01 Mar 
2014 01:00:00
+    }
+
+    @Test
+    public void testConvertTimezoneAmerica() 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-03-01 00:00:00'))";
+        conn.createStatement().execute(dml);
+        conn.commit();
+
+        ResultSet rs = conn.createStatement().executeQuery(
+                "SELECT k1, dates, CONVERT_TZ(dates, 'UTC', 'America/Adak') 
FROM TIMEZONE_OFFSET_TEST");
+
+        assertTrue(rs.next());
+        assertEquals(1393596000000L, rs.getDate(3).getTime()); //Fri, 28 Feb 
2014 14:00:00
+    }
+
+    @Test
+    public void nullInDateParameter() 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) VALUES (1)";
+        conn.createStatement().execute(dml);
+        conn.commit();
+
+        ResultSet rs = conn.createStatement().executeQuery(
+                "SELECT k1, dates, CONVERT_TZ(dates, 'UTC', 'America/Adak') 
FROM TIMEZONE_OFFSET_TEST");
+
+        assertTrue(rs.next());
+        rs.getDate(3);
+        assertTrue(rs.wasNull());
+    }
+
+    @Test
+    public void nullInFirstTimezoneParameter() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST (k1 
INTEGER NOT NULL, dates DATE, tz VARCHAR, CONSTRAINT pk PRIMARY KEY (k1))";
+        conn.createStatement().execute(ddl);
+        String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (1, 
TO_DATE('2014-03-01 00:00:00'))";
+        conn.createStatement().execute(dml);
+        conn.commit();
+
+        ResultSet rs = conn.createStatement().executeQuery(
+                "SELECT k1, dates, CONVERT_TZ(dates, tz, 'America/Adak') FROM 
TIMEZONE_OFFSET_TEST");
+
+        assertTrue(rs.next());
+        rs.getDate(3);
+        assertTrue(rs.wasNull());
+    }
+
+    @Test
+    public void nullInSecondTimezoneParameter() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        String ddl = "CREATE TABLE IF NOT EXISTS TIMEZONE_OFFSET_TEST (k1 
INTEGER NOT NULL, dates DATE, tz VARCHAR, CONSTRAINT pk PRIMARY KEY (k1))";
+        conn.createStatement().execute(ddl);
+        String dml = "UPSERT INTO TIMEZONE_OFFSET_TEST (k1, dates) VALUES (1, 
TO_DATE('2014-03-01 00:00:00'))";
+        conn.createStatement().execute(dml);
+        conn.commit();
+
+        ResultSet rs = conn.createStatement().executeQuery(
+                "SELECT k1, dates, CONVERT_TZ(dates, 'America/Adak', tz) FROM 
TIMEZONE_OFFSET_TEST");
+
+        assertTrue(rs.next());
+        rs.getDate(3);
+        assertTrue(rs.wasNull());
+    }
+
+    @Test(expected=IllegalDataException.class)
+    public void unknownTimezone() 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-03-01 00:00:00'))";
+        conn.createStatement().execute(dml);
+        conn.commit();
+
+        ResultSet rs = conn.createStatement().executeQuery(
+                "SELECT k1, dates, CONVERT_TZ(dates, 'UNKNOWN_TIMEZONE', 
'America/Adak') FROM TIMEZONE_OFFSET_TEST");
+
+        rs.next();
+
+        rs.getDate(3).getTime();
+    }
+}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/e4218a99/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 1168b0b..9e55610 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
@@ -68,6 +68,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.ConvertTimezoneFunction;
 
 import com.google.common.collect.Maps;
 
@@ -158,6 +159,7 @@ public enum ExpressionType {
     ArrayConstructorExpression(ArrayConstructorExpression.class),
     SQLViewTypeFunction(SQLViewTypeFunction.class),
     ExternalSqlTypeIdFunction(ExternalSqlTypeIdFunction.class),
+    ConvertTimezoneFunction(ConvertTimezoneFunction.class),
     DecodeFunction(DecodeFunction.class),
     TimezoneOffsetFunction(TimezoneOffsetFunction.class),
     EncodeFunction(EncodeFunction.class),

http://git-wip-us.apache.org/repos/asf/phoenix/blob/e4218a99/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ConvertTimezoneFunction.java
----------------------------------------------------------------------
diff --git 
a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ConvertTimezoneFunction.java
 
b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ConvertTimezoneFunction.java
new file mode 100644
index 0000000..b8e5edb
--- /dev/null
+++ 
b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/ConvertTimezoneFunction.java
@@ -0,0 +1,101 @@
+/*
+ * Copyright 2014 Apache Software Foundation.
+ *
+ * Licensed 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;
+
+/**
+ * Build in function CONVERT_TZ(date, 'timezone_from', 'timezone_to). Convert 
date from one timezone to
+ * another
+ *
+ */
+@FunctionParseNode.BuiltInFunction(name = ConvertTimezoneFunction.NAME, args = 
{
+    @FunctionParseNode.Argument(allowedTypes = {PDataType.DATE}),
+    @FunctionParseNode.Argument(allowedTypes = {PDataType.VARCHAR}),
+    @FunctionParseNode.Argument(allowedTypes = {PDataType.VARCHAR})})
+public class ConvertTimezoneFunction extends ScalarFunction {
+
+    public static final String NAME = "CONVERT_TZ";
+    private final Map<String, TimeZone> cachedTimeZones = new HashMap<String, 
TimeZone>();
+
+    public ConvertTimezoneFunction() {
+    }
+
+    public ConvertTimezoneFunction(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;
+        }
+
+        Date dateo = (Date) PDataType.DATE.toObject(ptr, 
children.get(0).getSortOrder());
+        Long date = dateo.getTime();
+
+        if (!children.get(1).evaluate(tuple, ptr)) {
+            return false;
+        }
+        TimeZone timezoneFrom = getTimezoneFromCache(Bytes.toString(ptr.get(), 
ptr.getOffset(), ptr.getLength()));
+
+        if (!children.get(2).evaluate(tuple, ptr)) {
+            return false;
+        }
+        TimeZone timezoneTo = TimeZone.getTimeZone(Bytes.toString(ptr.get(), 
ptr.getOffset(), ptr.getLength()));
+
+        long dateInUtc = date - timezoneFrom.getOffset(date);
+        long dateInTo = dateInUtc + timezoneTo.getOffset(dateInUtc);
+
+        ptr.set(PDataType.DATE.toBytes(new Date(dateInTo)));
+
+        return true;
+    }
+
+    private TimeZone getTimezoneFromCache(String timezone) throws 
IllegalDataException {
+        if (!cachedTimeZones.containsKey(timezone)) {
+            TimeZone tz = TimeZone.getTimeZone(timezone);
+            if (!tz.getID().equals(timezone)) {
+                throw new IllegalDataException("Invalid timezone " + timezone);
+            }
+            cachedTimeZones.put(timezone, tz);
+            return tz;
+        }
+        return cachedTimeZones.get(timezone);
+    }
+
+    @Override
+    public PDataType getDataType() {
+        return PDataType.DATE;
+    }
+}

Reply via email to