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; + } +}
