You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jc...@apache.org on 2018/06/22 18:49:14 UTC

[3/3] calcite git commit: [CALCITE-2286] Support timestamp type for Druid adapter

[CALCITE-2286] Support timestamp type for Druid adapter

Close apache/calcite#681


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

Branch: refs/heads/master
Commit: b29397d92e16f8aa77d5e84cf7a21744cd1b19a9
Parents: c12cb4b
Author: Jesus Camacho Rodriguez <jc...@apache.org>
Authored: Thu Apr 26 16:29:15 2018 -0700
Committer: Jesus Camacho Rodriguez <jc...@apache.org>
Committed: Fri Jun 22 11:49:06 2018 -0700

----------------------------------------------------------------------
 .../adapter/druid/CeilOperatorConversion.java   |   13 +-
 .../adapter/druid/DruidDateTimeUtils.java       |  118 +-
 .../calcite/adapter/druid/DruidExpressions.java |    8 +-
 .../calcite/adapter/druid/DruidJsonFilter.java  |   34 +-
 .../calcite/adapter/druid/DruidQuery.java       |   60 +-
 .../calcite/adapter/druid/DruidRules.java       |    4 +-
 .../adapter/druid/DruidSqlCastConverter.java    |   37 +-
 .../adapter/druid/DruidTableFactory.java        |   27 +-
 .../druid/ExtractOperatorConversion.java        |    9 +-
 .../adapter/druid/FloorOperatorConversion.java  |   12 +-
 .../adapter/druid/TimeExtractionFunction.java   |   40 +-
 .../org/apache/calcite/test/DruidAdapterIT.java |  107 +-
 .../apache/calcite/test/DruidAdapterIT2.java    | 3979 ++++++++++++++++++
 .../calcite/test/DruidDateRangeRulesTest.java   |    4 +-
 .../druid-foodmart-model-timestamp.json         |  153 +
 .../test/resources/druid-foodmart-model.json    |    5 +-
 site/_docs/druid_adapter.md                     |    5 +-
 17 files changed, 4471 insertions(+), 144 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/CeilOperatorConversion.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/CeilOperatorConversion.java b/druid/src/main/java/org/apache/calcite/adapter/druid/CeilOperatorConversion.java
index 7f15307..30c01e5 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/CeilOperatorConversion.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/CeilOperatorConversion.java
@@ -16,6 +16,7 @@
  */
 package org.apache.calcite.adapter.druid;
 
+import org.apache.calcite.avatica.util.DateTimeUtils;
 import org.apache.calcite.avatica.util.TimeUnitRange;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rex.RexCall;
@@ -23,6 +24,7 @@ import org.apache.calcite.rex.RexLiteral;
 import org.apache.calcite.rex.RexNode;
 import org.apache.calcite.sql.SqlOperator;
 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.type.SqlTypeName;
 
 import java.util.TimeZone;
 
@@ -63,11 +65,14 @@ public class CeilOperatorConversion implements DruidSqlOperatorConverter {
       if (isoPeriodFormat == null) {
         return null;
       }
+      final TimeZone tz;
+      if (arg.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
+        tz = TimeZone.getTimeZone(query.getConnectionConfig().timeZone());
+      } else {
+        tz = DateTimeUtils.UTC_ZONE;
+      }
       return DruidExpressions.applyTimestampCeil(
-          druidExpression,
-          isoPeriodFormat,
-          "",
-          TimeZone.getTimeZone(query.getConnectionConfig().timeZone()));
+          druidExpression, isoPeriodFormat, "", tz);
     } else {
       return null;
     }

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/DruidDateTimeUtils.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidDateTimeUtils.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidDateTimeUtils.java
index 91f5fa4..20da334 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidDateTimeUtils.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidDateTimeUtils.java
@@ -16,7 +16,6 @@
  */
 package org.apache.calcite.adapter.druid;
 
-import org.apache.calcite.avatica.util.DateTimeUtils;
 import org.apache.calcite.avatica.util.TimeUnitRange;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rex.RexCall;
@@ -27,7 +26,6 @@ import org.apache.calcite.sql.SqlKind;
 import org.apache.calcite.sql.type.SqlTypeName;
 import org.apache.calcite.util.DateString;
 import org.apache.calcite.util.TimestampString;
-import org.apache.calcite.util.TimestampWithTimeZoneString;
 import org.apache.calcite.util.Util;
 import org.apache.calcite.util.trace.CalciteTrace;
 
@@ -45,7 +43,6 @@ import org.slf4j.Logger;
 
 import java.util.ArrayList;
 import java.util.List;
-import java.util.TimeZone;
 
 import javax.annotation.Nullable;
 
@@ -66,9 +63,9 @@ public class DruidDateTimeUtils {
    * reference a single column: the timestamp column.
    */
   @Nullable
-  public static List<Interval> createInterval(RexNode e, String timeZone) {
-    final List<Range<TimestampString>> ranges =
-        extractRanges(e, TimeZone.getTimeZone(timeZone), false);
+  public static List<Interval> createInterval(RexNode e) {
+    final List<Range<Long>> ranges =
+        extractRanges(e, false);
     if (ranges == null) {
       // We did not succeed, bail out
       return null;
@@ -85,18 +82,18 @@ public class DruidDateTimeUtils {
   }
 
   protected static List<Interval> toInterval(
-      List<Range<TimestampString>> ranges) {
+      List<Range<Long>> ranges) {
     List<Interval> intervals = Lists.transform(ranges,
-        new Function<Range<TimestampString>, Interval>() {
-          public Interval apply(Range<TimestampString> range) {
+        new Function<Range<Long>, Interval>() {
+          public Interval apply(Range<Long> range) {
             if (!range.hasLowerBound() && !range.hasUpperBound()) {
               return DruidTable.DEFAULT_INTERVAL;
             }
             long start = range.hasLowerBound()
-                ? range.lowerEndpoint().getMillisSinceEpoch()
+                ? range.lowerEndpoint().longValue()
                 : DruidTable.DEFAULT_INTERVAL.getStartMillis();
             long end = range.hasUpperBound()
-                ? range.upperEndpoint().getMillisSinceEpoch()
+                ? range.upperEndpoint().longValue()
                 : DruidTable.DEFAULT_INTERVAL.getEndMillis();
             if (range.hasLowerBound()
                 && range.lowerBoundType() == BoundType.OPEN) {
@@ -116,8 +113,7 @@ public class DruidDateTimeUtils {
   }
 
   @Nullable
-  protected static List<Range<TimestampString>> extractRanges(RexNode node,
-      TimeZone timeZone, boolean withNot) {
+  protected static List<Range<Long>> extractRanges(RexNode node, boolean withNot) {
     switch (node.getKind()) {
     case EQUALS:
     case LESS_THAN:
@@ -126,17 +122,17 @@ public class DruidDateTimeUtils {
     case GREATER_THAN_OR_EQUAL:
     case BETWEEN:
     case IN:
-      return leafToRanges((RexCall) node, timeZone, withNot);
+      return leafToRanges((RexCall) node, withNot);
 
     case NOT:
-      return extractRanges(((RexCall) node).getOperands().get(0), timeZone, !withNot);
+      return extractRanges(((RexCall) node).getOperands().get(0), !withNot);
 
     case OR: {
       RexCall call = (RexCall) node;
-      List<Range<TimestampString>> intervals = Lists.newArrayList();
+      List<Range<Long>> intervals = Lists.newArrayList();
       for (RexNode child : call.getOperands()) {
-        List<Range<TimestampString>> extracted =
-            extractRanges(child, timeZone, withNot);
+        List<Range<Long>> extracted =
+            extractRanges(child, withNot);
         if (extracted != null) {
           intervals.addAll(extracted);
         }
@@ -146,10 +142,10 @@ public class DruidDateTimeUtils {
 
     case AND: {
       RexCall call = (RexCall) node;
-      List<Range<TimestampString>> ranges = new ArrayList<>();
+      List<Range<Long>> ranges = new ArrayList<>();
       for (RexNode child : call.getOperands()) {
-        List<Range<TimestampString>> extractedRanges =
-            extractRanges(child, timeZone, false);
+        List<Range<Long>> extractedRanges =
+            extractRanges(child, false);
         if (extractedRanges == null || extractedRanges.isEmpty()) {
           // We could not extract, we bail out
           return null;
@@ -158,7 +154,7 @@ public class DruidDateTimeUtils {
           ranges.addAll(extractedRanges);
           continue;
         }
-        List<Range<TimestampString>> overlapped = new ArrayList<>();
+        List<Range<Long>> overlapped = new ArrayList<>();
         for (Range current : ranges) {
           for (Range interval : extractedRanges) {
             if (current.isConnected(interval)) {
@@ -177,8 +173,7 @@ public class DruidDateTimeUtils {
   }
 
   @Nullable
-  protected static List<Range<TimestampString>> leafToRanges(RexCall call,
-      TimeZone timeZone, boolean withNot) {
+  protected static List<Range<Long>> leafToRanges(RexCall call, boolean withNot) {
     switch (call.getKind()) {
     case EQUALS:
     case LESS_THAN:
@@ -186,13 +181,13 @@ public class DruidDateTimeUtils {
     case GREATER_THAN:
     case GREATER_THAN_OR_EQUAL:
     {
-      final TimestampString value;
+      final Long value;
       if (call.getOperands().get(0) instanceof RexInputRef
-          && literalValue(call.getOperands().get(1), timeZone) != null) {
-        value = literalValue(call.getOperands().get(1), timeZone);
+          && literalValue(call.getOperands().get(1)) != null) {
+        value = literalValue(call.getOperands().get(1));
       } else if (call.getOperands().get(1) instanceof RexInputRef
-          && literalValue(call.getOperands().get(0), timeZone) != null) {
-        value = literalValue(call.getOperands().get(0), timeZone);
+          && literalValue(call.getOperands().get(0)) != null) {
+        value = literalValue(call.getOperands().get(0));
       } else {
         return null;
       }
@@ -214,12 +209,12 @@ public class DruidDateTimeUtils {
     }
     case BETWEEN:
     {
-      final TimestampString value1;
-      final TimestampString value2;
-      if (literalValue(call.getOperands().get(2), timeZone) != null
-          && literalValue(call.getOperands().get(3), timeZone) != null) {
-        value1 = literalValue(call.getOperands().get(2), timeZone);
-        value2 = literalValue(call.getOperands().get(3), timeZone);
+      final Long value1;
+      final Long value2;
+      if (literalValue(call.getOperands().get(2)) != null
+          && literalValue(call.getOperands().get(3)) != null) {
+        value1 = literalValue(call.getOperands().get(2));
+        value2 = literalValue(call.getOperands().get(3));
       } else {
         return null;
       }
@@ -234,10 +229,10 @@ public class DruidDateTimeUtils {
     }
     case IN:
     {
-      ImmutableList.Builder<Range<TimestampString>> ranges =
+      ImmutableList.Builder<Range<Long>> ranges =
           ImmutableList.builder();
       for (RexNode operand : Util.skip(call.operands)) {
-        final TimestampString element = literalValue(operand, timeZone);
+        final Long element = literalValue(operand);
         if (element == null) {
           return null;
         }
@@ -255,25 +250,29 @@ public class DruidDateTimeUtils {
     }
   }
 
+  /**
+   * Returns the literal value for the given node, assuming it is a literal with
+   * datetime type, or a cast that only alters nullability on top of a literal with
+   * datetime type.
+   */
   @Nullable
-  protected static TimestampString literalValue(RexNode node, TimeZone timeZone) {
+  protected static Long literalValue(RexNode node) {
     switch (node.getKind()) {
     case LITERAL:
       switch (((RexLiteral) node).getTypeName()) {
-      case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
-        return ((RexLiteral) node).getValueAs(TimestampString.class);
       case TIMESTAMP:
-        // Cast timestamp to timestamp with local time zone
-        final TimestampString t = ((RexLiteral) node).getValueAs(TimestampString.class);
-        return new TimestampWithTimeZoneString(t.toString() + " " + timeZone.getID())
-            .withTimeZone(DateTimeUtils.UTC_ZONE).getLocalTimestampString();
+      case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+        TimestampString tsVal = ((RexLiteral) node).getValueAs(TimestampString.class);
+        if (tsVal == null) {
+          return null;
+        }
+        return tsVal.getMillisSinceEpoch();
       case DATE:
-        // Cast date to timestamp with local time zone
-        final DateString d = ((RexLiteral) node).getValueAs(DateString.class);
-        return new TimestampWithTimeZoneString(
-            TimestampString.fromMillisSinceEpoch(
-                d.getMillisSinceEpoch()).toString() + " " + timeZone.getID())
-            .withTimeZone(DateTimeUtils.UTC_ZONE).getLocalTimestampString();
+        DateString dateVal = ((RexLiteral) node).getValueAs(DateString.class);
+        if (dateVal == null) {
+          return null;
+        }
+        return dateVal.getMillisSinceEpoch();
       }
       break;
     case CAST:
@@ -293,7 +292,7 @@ public class DruidDateTimeUtils {
               || callType.getSqlTypeName() == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)
           && callType.isNullable()
           && !operandType.isNullable()) {
-        return literalValue(operand, timeZone);
+        return literalValue(operand);
       }
     }
     return null;
@@ -308,21 +307,36 @@ public class DruidDateTimeUtils {
    * @param node the Rex node
    * @return the granularity, or null if it cannot be inferred
    */
+  @Nullable
   public static Granularity extractGranularity(RexNode node, String timeZone) {
+    final int valueIndex;
     final int flagIndex;
 
     if (TimeExtractionFunction.isValidTimeExtract(node)) {
       flagIndex = 0;
+      valueIndex = 1;
     } else if (TimeExtractionFunction.isValidTimeFloor(node)) {
+      valueIndex = 0;
       flagIndex = 1;
     } else {
       // We can only infer granularity from floor and extract.
       return null;
     }
     final RexCall call = (RexCall) node;
+    final RexNode value = call.operands.get(valueIndex);
     final RexLiteral flag = (RexLiteral) call.operands.get(flagIndex);
     final TimeUnitRange timeUnit = (TimeUnitRange) flag.getValue();
-    return Granularities.createGranularity(timeUnit, timeZone);
+
+    final RelDataType valueType = value.getType();
+    if (valueType.getSqlTypeName() == SqlTypeName.DATE
+        || valueType.getSqlTypeName() == SqlTypeName.TIMESTAMP) {
+      // We use 'UTC' for date/timestamp type as Druid needs timezone information
+      return Granularities.createGranularity(timeUnit, "UTC");
+    } else if (valueType.getSqlTypeName() == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
+      return Granularities.createGranularity(timeUnit, timeZone);
+    }
+    // Type not recognized
+    return null;
   }
 
   /**

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/DruidExpressions.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidExpressions.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidExpressions.java
index 0e1965c..bae6a4d 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidExpressions.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidExpressions.java
@@ -146,11 +146,11 @@ public class DruidExpressions {
       } else if (SqlTypeName.STRING_TYPES.contains(sqlTypeName)) {
         return
             DruidExpressions.stringLiteral(RexLiteral.stringValue(rexNode));
-      } else if (SqlTypeName.TIMESTAMP == sqlTypeName || SqlTypeName.DATE == sqlTypeName
+      } else if (SqlTypeName.DATE == sqlTypeName
+          || SqlTypeName.TIMESTAMP == sqlTypeName
           || SqlTypeName.TIME_WITH_LOCAL_TIME_ZONE == sqlTypeName) {
-        return DruidExpressions.numberLiteral(DruidDateTimeUtils
-            .literalValue(rexNode, TimeZone.getTimeZone(druidRel.getConnectionConfig().timeZone()))
-            .getMillisSinceEpoch());
+        return DruidExpressions.numberLiteral(
+            DruidDateTimeUtils.literalValue(rexNode));
       } else if (SqlTypeName.BOOLEAN == sqlTypeName) {
         return DruidExpressions.numberLiteral(RexLiteral.booleanValue(rexNode) ? 1 : 0);
       }

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/DruidJsonFilter.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidJsonFilter.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidJsonFilter.java
index 11ec2be..cca9d6b 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidJsonFilter.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidJsonFilter.java
@@ -16,6 +16,7 @@
  */
 package org.apache.calcite.adapter.druid;
 
+import org.apache.calcite.avatica.util.DateTimeUtils;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rex.RexCall;
 import org.apache.calcite.rex.RexLiteral;
@@ -24,7 +25,6 @@ import org.apache.calcite.sql.SqlKind;
 import org.apache.calcite.sql.type.SqlTypeFamily;
 import org.apache.calcite.sql.type.SqlTypeName;
 import org.apache.calcite.util.Pair;
-import org.apache.calcite.util.TimestampString;
 
 import com.fasterxml.jackson.core.JsonGenerator;
 import com.google.common.annotations.VisibleForTesting;
@@ -37,7 +37,6 @@ import java.io.IOException;
 import java.text.SimpleDateFormat;
 import java.util.List;
 import java.util.Locale;
-import java.util.TimeZone;
 
 import javax.annotation.Nullable;
 
@@ -47,6 +46,9 @@ import javax.annotation.Nullable;
  */
 abstract class DruidJsonFilter implements DruidJson {
 
+  private static final SimpleDateFormat DATE_FORMATTER = getDateFormatter();
+
+
   /**
    * @param rexNode    rexNode to translate to Druid Json Filter
    * @param rowType    rowType associated to rexNode
@@ -206,30 +208,22 @@ abstract class DruidJsonFilter implements DruidJson {
   @Nullable
   private static String toDruidLiteral(RexNode rexNode, RelDataType rowType,
       DruidQuery druidQuery) {
-    final SimpleDateFormat dateFormatter = new SimpleDateFormat(
-        TimeExtractionFunction.ISO_TIME_FORMAT,
-        Locale.ROOT);
-    final String timeZone = druidQuery.getConnectionConfig().timeZone();
-    if (timeZone != null) {
-      dateFormatter.setTimeZone(TimeZone.getTimeZone(timeZone));
-    }
     final String val;
     final RexLiteral rhsLiteral = (RexLiteral) rexNode;
     if (SqlTypeName.NUMERIC_TYPES.contains(rhsLiteral.getTypeName())) {
       val = String.valueOf(RexLiteral.value(rhsLiteral));
     } else if (SqlTypeName.CHAR_TYPES.contains(rhsLiteral.getTypeName())) {
       val = String.valueOf(RexLiteral.stringValue(rhsLiteral));
-    } else if (SqlTypeName.TIMESTAMP == rhsLiteral.getTypeName() || SqlTypeName.DATE == rhsLiteral
-        .getTypeName() || SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE == rhsLiteral.getTypeName()) {
-      TimestampString timestampString = DruidDateTimeUtils
-          .literalValue(rexNode, TimeZone.getTimeZone(timeZone));
-      if (timestampString == null) {
+    } else if (SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE == rhsLiteral.getTypeName()
+        || SqlTypeName.TIMESTAMP == rhsLiteral.getTypeName()
+        || SqlTypeName.DATE == rhsLiteral.getTypeName()) {
+      Long millisSinceEpoch = DruidDateTimeUtils.literalValue(rexNode);
+      if (millisSinceEpoch == null) {
         throw new AssertionError(
             "Cannot translate Literal" + rexNode + " of type "
                 + rhsLiteral.getTypeName() + " to TimestampString");
       }
-      //@TODO this is unnecessary we can send time as Long (eg millis since epoch) to druid
-      val = dateFormatter.format(timestampString.getMillisSinceEpoch());
+      val = DATE_FORMATTER.format(millisSinceEpoch);
     } else {
       // Don't know how to filter on this kind of literal.
       val = null;
@@ -637,6 +631,14 @@ abstract class DruidJsonFilter implements DruidJson {
       generator.writeEndObject();
     }
   }
+
+  private static SimpleDateFormat getDateFormatter() {
+    final SimpleDateFormat dateFormatter = new SimpleDateFormat(
+        TimeExtractionFunction.ISO_TIME_FORMAT,
+        Locale.ROOT);
+    dateFormatter.setTimeZone(DateTimeUtils.UTC_ZONE);
+    return dateFormatter;
+  }
 }
 
 // End DruidJsonFilter.java

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java
index d926060..611c722 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java
@@ -18,6 +18,7 @@ package org.apache.calcite.adapter.druid;
 
 import org.apache.calcite.DataContext;
 import org.apache.calcite.avatica.ColumnMetaData;
+import org.apache.calcite.avatica.util.DateTimeUtils;
 import org.apache.calcite.config.CalciteConnectionConfig;
 import org.apache.calcite.interpreter.BindableRel;
 import org.apache.calcite.interpreter.Bindables;
@@ -259,11 +260,13 @@ public class DruidQuery extends AbstractRelNode implements BindableRel {
     switch (rexNode.getKind()) {
     case INPUT_REF:
       columnName = extractColumnName(rexNode, rowType, druidQuery);
-      //@TODO we can remove this ugly check by treating druid time columns as LONG
-      if (rexNode.getType().getFamily() == SqlTypeFamily.DATE
-          || rexNode.getType().getFamily() == SqlTypeFamily.TIMESTAMP) {
-        extractionFunction = TimeExtractionFunction
-            .createDefault(druidQuery.getConnectionConfig().timeZone());
+      if (rexNode.getType().getSqlTypeName() == SqlTypeName.DATE
+          || rexNode.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP
+          || rexNode.getType().getSqlTypeName()
+          == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
+        // Use UTC for DATE and TIMESTAMP types
+        extractionFunction = TimeExtractionFunction.createDefault(
+            DateTimeUtils.UTC_ZONE.getID());
       } else {
         extractionFunction = null;
       }
@@ -278,12 +281,24 @@ public class DruidQuery extends AbstractRelNode implements BindableRel {
       if (!TimeExtractionFunction.isValidTimeExtract((RexCall) rexNode)) {
         return Pair.of(null, null);
       }
-      extractionFunction =
-          TimeExtractionFunction.createExtractFromGranularity(granularity,
-              druidQuery.getConnectionConfig().timeZone());
-      columnName =
-          extractColumnName(((RexCall) rexNode).getOperands().get(1), rowType, druidQuery);
-
+      RexNode extractValueNode = ((RexCall) rexNode).getOperands().get(1);
+      if (extractValueNode.getType().getSqlTypeName() == SqlTypeName.DATE
+          || extractValueNode.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP) {
+        // Use 'UTC' at the extraction level
+        extractionFunction =
+            TimeExtractionFunction.createExtractFromGranularity(
+                granularity, DateTimeUtils.UTC_ZONE.getID());
+        columnName = extractColumnName(extractValueNode, rowType, druidQuery);
+      } else if (extractValueNode.getType().getSqlTypeName()
+          == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
+        // Use local time zone at the extraction level
+        extractionFunction =
+          TimeExtractionFunction.createExtractFromGranularity(
+              granularity, druidQuery.getConnectionConfig().timeZone());
+        columnName = extractColumnName(extractValueNode, rowType, druidQuery);
+      } else {
+        return Pair.of(null, null);
+      }
       break;
     case FLOOR:
       granularity = DruidDateTimeUtils
@@ -295,11 +310,20 @@ public class DruidQuery extends AbstractRelNode implements BindableRel {
       if (!TimeExtractionFunction.isValidTimeFloor((RexCall) rexNode)) {
         return Pair.of(null, null);
       }
-      extractionFunction =
-          TimeExtractionFunction
-              .createFloorFromGranularity(granularity, druidQuery.getConnectionConfig().timeZone());
-      columnName =
-          extractColumnName(((RexCall) rexNode).getOperands().get(0), rowType, druidQuery);
+      RexNode floorValueNode = ((RexCall) rexNode).getOperands().get(0);
+      if (floorValueNode.getType().getSqlTypeName() == SqlTypeName.DATE
+          || floorValueNode.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP
+          || floorValueNode.getType().getSqlTypeName()
+          == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
+        // Use 'UTC' at the extraction level, since all datetime types
+        // are represented in 'UTC'
+        extractionFunction =
+            TimeExtractionFunction.createFloorFromGranularity(
+                granularity, DateTimeUtils.UTC_ZONE.getID());
+        columnName = extractColumnName(floorValueNode, rowType, druidQuery);
+      } else {
+        return Pair.of(null, null);
+      }
       break;
     case CAST:
       // CASE we have a cast over InputRef. Check that cast is valid
@@ -310,8 +334,8 @@ public class DruidQuery extends AbstractRelNode implements BindableRel {
           extractColumnName(((RexCall) rexNode).getOperands().get(0), rowType, druidQuery);
       // CASE CAST to TIME/DATE need to make sure that we have valid extraction fn
       final SqlTypeName toTypeName = rexNode.getType().getSqlTypeName();
-      if (toTypeName.getFamily() == SqlTypeFamily.TIMESTAMP
-          || toTypeName.getFamily() == SqlTypeFamily.DATETIME) {
+      if (toTypeName == SqlTypeName.DATE || toTypeName == SqlTypeName.TIMESTAMP
+          || toTypeName == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
         extractionFunction = TimeExtractionFunction.translateCastToTimeExtract(rexNode,
             TimeZone.getTimeZone(druidQuery.getConnectionConfig().timeZone()));
         if (extractionFunction == null) {

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/DruidRules.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidRules.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidRules.java
index 7b6bc78..83d4fce 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidRules.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidRules.java
@@ -196,9 +196,7 @@ public class DruidRules {
             .unwrap(CalciteConnectionConfig.class).timeZone();
         assert timeZone != null;
         intervals = DruidDateTimeUtils.createInterval(
-            RexUtil.composeConjunction(rexBuilder, triple.getLeft(), false),
-
-            query.getConnectionConfig().timeZone());
+            RexUtil.composeConjunction(rexBuilder, triple.getLeft(), false));
         if (intervals == null || intervals.isEmpty()) {
           // Case we have a filter with extract that can not be written as interval push down
           triple.getMiddle().addAll(triple.getLeft());

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/DruidSqlCastConverter.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidSqlCastConverter.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidSqlCastConverter.java
index 0ca6cc7..def28c6 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidSqlCastConverter.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidSqlCastConverter.java
@@ -16,6 +16,7 @@
  */
 package org.apache.calcite.adapter.druid;
 
+import org.apache.calcite.avatica.util.DateTimeUtils;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rex.RexCall;
 import org.apache.calcite.rex.RexNode;
@@ -53,16 +54,42 @@ public class DruidSqlCastConverter implements DruidSqlOperatorConverter {
     final SqlTypeName toType = rexNode.getType().getSqlTypeName();
     final String timeZoneConf = druidQuery.getConnectionConfig().timeZone();
     final TimeZone timeZone = TimeZone.getTimeZone(timeZoneConf == null ? "UTC" : timeZoneConf);
+    final boolean nullEqualToEmpty = druidQuery.getConnectionConfig().nullEqualToEmpty();
 
     if (SqlTypeName.CHAR_TYPES.contains(fromType) && SqlTypeName.DATETIME_TYPES.contains(toType)) {
       //case chars to dates
       return castCharToDateTime(timeZone, operandExpression, toType,
-          druidQuery.getConnectionConfig().nullEqualToEmpty() ? "" : null);
+          nullEqualToEmpty ? "" : null);
     } else if (SqlTypeName.DATETIME_TYPES.contains(fromType) && SqlTypeName.CHAR_TYPES.contains
         (toType)) {
       //case dates to chars
-      return castDateTimeToChar(timeZone, operandExpression,
-          fromType);
+      return castDateTimeToChar(timeZone, operandExpression, fromType);
+    } else if (toType == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE
+        && SqlTypeName.DATETIME_TYPES.contains(fromType)) {
+      if (timeZone.equals(DateTimeUtils.UTC_ZONE)) {
+        // bail out, internal representation is the same,
+        // we do not need to do anything
+        return operandExpression;
+      }
+      // to timestamp with local time zone
+      return castCharToDateTime(
+          timeZone,
+          castDateTimeToChar(DateTimeUtils.UTC_ZONE, operandExpression, fromType),
+          toType,
+          nullEqualToEmpty ? "" : null);
+    } else if (fromType == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE
+        && SqlTypeName.DATETIME_TYPES.contains(toType)) {
+      if (toType != SqlTypeName.DATE && timeZone.equals(DateTimeUtils.UTC_ZONE)) {
+        // bail out, internal representation is the same,
+        // we do not need to do anything
+        return operandExpression;
+      }
+      // timestamp with local time zone to other types
+      return castCharToDateTime(
+          DateTimeUtils.UTC_ZONE,
+          castDateTimeToChar(timeZone, operandExpression, fromType),
+          toType,
+          nullEqualToEmpty ? "" : null);
     } else {
       // Handle other casts.
       final DruidType fromExprType = DruidExpressions.EXPRESSION_TYPES.get(fromType);
@@ -115,8 +142,8 @@ public class DruidSqlCastConverter implements DruidSqlOperatorConverter {
           Period.days(1).toString(),
           "",
           timeZone);
-    } else if (toType == SqlTypeName.TIMESTAMP || toType == SqlTypeName
-        .TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
+    } else if (toType == SqlTypeName.TIMESTAMP
+        || toType == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
       return timestampExpression;
     } else {
       throw new IllegalStateException(

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java
index add0136..e714609 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java
@@ -57,12 +57,33 @@ public class DruidTableFactory implements TableFactory {
     final Map<String, SqlTypeName> fieldBuilder = new LinkedHashMap<>();
     final Map<String, List<ComplexMetric>> complexMetrics = new HashMap<>();
     final String timestampColumnName;
-    if (operand.get("timestampColumn") != null) {
-      timestampColumnName = (String) operand.get("timestampColumn");
+    final SqlTypeName timestampColumnType;
+    final Object timestampInfo = operand.get("timestampColumn");
+    if (timestampInfo != null) {
+      if (timestampInfo instanceof Map) {
+        Map map = (Map) timestampInfo;
+        if (!(map.get("name") instanceof String)) {
+          throw new IllegalArgumentException("timestampColumn array must have name");
+        }
+        timestampColumnName = (String) map.get("name");
+        if (!(map.get("type") instanceof String)
+            || map.get("type").equals("timestamp with local time zone")) {
+          timestampColumnType = SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE;
+        } else if (map.get("type").equals("timestamp")) {
+          timestampColumnType = SqlTypeName.TIMESTAMP;
+        } else {
+          throw new IllegalArgumentException("unexpected type for timestampColumn array");
+        }
+      } else {
+        // String (for backwards compatibility)
+        timestampColumnName = (String) timestampInfo;
+        timestampColumnType = SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE;
+      }
     } else {
       timestampColumnName = DruidTable.DEFAULT_TIMESTAMP_COLUMN;
+      timestampColumnType = SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE;
     }
-    fieldBuilder.put(timestampColumnName, SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE);
+    fieldBuilder.put(timestampColumnName, timestampColumnType);
     final Object dimensionsRaw = operand.get("dimensions");
     if (dimensionsRaw instanceof List) {
       // noinspection unchecked

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/ExtractOperatorConversion.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/ExtractOperatorConversion.java b/druid/src/main/java/org/apache/calcite/adapter/druid/ExtractOperatorConversion.java
index 6e35540..f31b0dc 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/ExtractOperatorConversion.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/ExtractOperatorConversion.java
@@ -16,6 +16,7 @@
  */
 package org.apache.calcite.adapter.druid;
 
+import org.apache.calcite.avatica.util.DateTimeUtils;
 import org.apache.calcite.avatica.util.TimeUnitRange;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rex.RexCall;
@@ -23,6 +24,7 @@ import org.apache.calcite.rex.RexLiteral;
 import org.apache.calcite.rex.RexNode;
 import org.apache.calcite.sql.SqlOperator;
 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.type.SqlTypeName;
 
 import com.google.common.collect.ImmutableMap;
 
@@ -72,8 +74,11 @@ public class ExtractOperatorConversion implements DruidSqlOperatorConverter {
       return null;
     }
 
-    return DruidExpressions.applyTimeExtract(
-        input, druidUnit, TimeZone.getTimeZone(query.getConnectionConfig().timeZone()));
+    final TimeZone tz =
+        arg.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE
+            ? TimeZone.getTimeZone(query.getConnectionConfig().timeZone())
+            : DateTimeUtils.UTC_ZONE;
+    return DruidExpressions.applyTimeExtract(input, druidUnit, tz);
   }
 }
 

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/FloorOperatorConversion.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/FloorOperatorConversion.java b/druid/src/main/java/org/apache/calcite/adapter/druid/FloorOperatorConversion.java
index 0d8ecc1..187fa66 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/FloorOperatorConversion.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/FloorOperatorConversion.java
@@ -16,11 +16,13 @@
  */
 package org.apache.calcite.adapter.druid;
 
+import org.apache.calcite.avatica.util.DateTimeUtils;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rex.RexCall;
 import org.apache.calcite.rex.RexNode;
 import org.apache.calcite.sql.SqlOperator;
 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.type.SqlTypeName;
 
 import java.util.TimeZone;
 
@@ -51,8 +53,14 @@ public class FloorOperatorConversion implements DruidSqlOperatorConverter {
       return  DruidQuery.format("floor(%s)", druidExpression);
     } else if (call.getOperands().size() == 2) {
       // FLOOR(expr TO timeUnit)
+      final TimeZone tz;
+      if (arg.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
+        tz = TimeZone.getTimeZone(druidQuery.getConnectionConfig().timeZone());
+      } else {
+        tz = DateTimeUtils.UTC_ZONE;
+      }
       final Granularity granularity = DruidDateTimeUtils
-          .extractGranularity(call, druidQuery.getConnectionConfig().timeZone());
+          .extractGranularity(call, tz.getID());
       if (granularity == null) {
         return null;
       }
@@ -64,7 +72,7 @@ public class FloorOperatorConversion implements DruidSqlOperatorConverter {
           druidExpression,
           isoPeriodFormat,
           "",
-          TimeZone.getTimeZone(druidQuery.getConnectionConfig().timeZone()));
+          tz);
     } else {
       return null;
     }

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/main/java/org/apache/calcite/adapter/druid/TimeExtractionFunction.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/TimeExtractionFunction.java b/druid/src/main/java/org/apache/calcite/adapter/druid/TimeExtractionFunction.java
index 552d221..d0c1596 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/TimeExtractionFunction.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/TimeExtractionFunction.java
@@ -16,13 +16,13 @@
  */
 package org.apache.calcite.adapter.druid;
 
+import org.apache.calcite.avatica.util.DateTimeUtils;
 import org.apache.calcite.avatica.util.TimeUnitRange;
 import org.apache.calcite.rex.RexCall;
 import org.apache.calcite.rex.RexLiteral;
 import org.apache.calcite.rex.RexNode;
 import org.apache.calcite.sql.SqlKind;
 
-import org.apache.calcite.sql.type.SqlTypeFamily;
 import org.apache.calcite.sql.type.SqlTypeName;
 
 import com.fasterxml.jackson.core.JsonGenerator;
@@ -209,17 +209,37 @@ public class TimeExtractionFunction implements ExtractionFunction {
       // unknown format
       return null;
     }
-    if (rexCall.getType().getFamily() == SqlTypeFamily.DATE) {
-      return new TimeExtractionFunction(castFormat,
-          Granularities.createGranularity(TimeUnitRange.DAY, timeZoneId), timeZoneId,
-          Locale.ENGLISH.toString());
+    SqlTypeName fromType = rexCall.getOperands().get(0).getType().getSqlTypeName();
+    SqlTypeName toType = rexCall.getType().getSqlTypeName();
+    String granularityTZId;
+    switch (fromType) {
+    case DATE:
+    case TIMESTAMP:
+      granularityTZId = DateTimeUtils.UTC_ZONE.getID();
+      break;
+    case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+      granularityTZId = timeZoneId;
+      break;
+    default:
+      return null;
     }
-    if (rexCall.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP
-        || rexCall.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE) {
-      return new TimeExtractionFunction(castFormat, null, timeZoneId, Locale.ENGLISH.toString());
+    switch (toType) {
+    case DATE:
+      return new TimeExtractionFunction(castFormat,
+          Granularities.createGranularity(TimeUnitRange.DAY, granularityTZId),
+          DateTimeUtils.UTC_ZONE.getID(), Locale.ENGLISH.toString());
+    case TIMESTAMP:
+      // date -> timestamp: UTC
+      // timestamp -> timestamp: UTC
+      // timestamp with local time zone -> granularityTZId
+      return new TimeExtractionFunction(
+          castFormat, null, granularityTZId, Locale.ENGLISH.toString());
+    case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+      return new TimeExtractionFunction(
+          castFormat, null, DateTimeUtils.UTC_ZONE.getID(), Locale.ENGLISH.toString());
+    default:
+      return null;
     }
-
-    return null;
   }
 
 }

http://git-wip-us.apache.org/repos/asf/calcite/blob/b29397d9/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
----------------------------------------------------------------------
diff --git a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
index 24616b0..2a57303 100644
--- a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
+++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
@@ -70,6 +70,10 @@ import static org.junit.Assert.assertTrue;
  *   <li>push SORT and/or LIMIT into "groupBy" query</li>
  *   <li>push HAVING into "groupBy" query</li>
  * </ul>
+ *
+ * These tests use "timestamp with local time zone" type for the
+ * Druid timestamp column, instead of "timestamp" type  as
+ * {@link DruidAdapterIT2}.
  */
 public class DruidAdapterIT {
   /** URL of the "druid-foodmart" model. */
@@ -347,8 +351,7 @@ public class DruidAdapterIT {
     final String druidQuery = "{'queryType':'scan',"
         + "'dataSource':'wikiticker',"
         + "'intervals':['1900-01-01T00:00:00.000Z/2015-10-12T00:00:00.000Z'],"
-        + "'virtualColumns':[{'type':'expression','name':'vc','expression':'\\'__time\\'','outputType':'LONG'}],'columns':['vc'],"
-        + "'resultFormat':'compactedList'";
+        + "'virtualColumns':[{'type':'expression','name':'vc','expression':";
     sql(sql, WIKI_AUTO2)
         .limit(2)
         .returnsUnordered("__time=2015-09-12 00:46:58",
@@ -3242,7 +3245,7 @@ public class DruidAdapterIT {
 
     final String druidQuery = "{'queryType':'scan','dataSource':'foodmart','intervals':"
         + "['1997-01-01T00:00:00.000Z/1997-04-01T00:00:00.000Z'],'virtualColumns':"
-        + "[{'type':'expression','name':'vc','expression':'timestamp_floor(\\'__time\\'";
+        + "[{'type':'expression','name':'vc','expression':'timestamp_floor(";
     sql(sql, FOODMART)
         .returnsOrdered("T=1997-01-01 00:00:00", "T=1997-01-01 00:00:00")
         .queryContains(
@@ -3285,36 +3288,61 @@ public class DruidAdapterIT {
         + "\"ordering\":\"lexicographic\",\"extractionFn\":{\"type\":\"timeFormat\","
         + "\"format\":\"yyyy-MM-dd";
     final String druidQueryPart2 = "\"granularity\":{\"type\":\"period\",\"period\":\"PT1H\","
-        + "\"timeZone\":\"IST\"},\"timeZone\":\"IST\","
+        + "\"timeZone\":\"Asia/Kolkata\"},\"timeZone\":\"UTC\","
         + "\"locale\":\"und\"}}";
 
     CalciteAssert.that()
         .enable(enabled())
         .with(ImmutableMap.of("model", WIKI_AUTO2.getPath()))
-        .with(CalciteConnectionProperty.TIME_ZONE.camelName(), "IST")
+        .with(CalciteConnectionProperty.TIME_ZONE.camelName(), "Asia/Kolkata")
         .query(sql)
         .runs()
         .queryContains(druidChecker(druidQueryPart1, druidQueryPart2))
-        .returnsOrdered("T=2015-09-12 02:30:02");
+        .returnsOrdered("T=2015-09-12 14:00:01");
   }
 
   @Test public void testTimeWithFilterOnFloorOnTimeWithTimezoneConversion() {
     final String sql = "Select cast(\"__time\" as timestamp) as t, \"countryName\" as s, "
         + "count(*) as c from \"wikiticker\" where floor(\"__time\" to HOUR)"
-        + " >= '2015-09-12 08:00:00 IST' group by cast(\"__time\" as timestamp), \"countryName\""
+        + " >= '2015-09-12 08:00:00 Asia/Kolkata' group by cast(\"__time\" as timestamp), \"countryName\""
         + " order by t limit 4";
     final String druidQueryPart1 = "filter\":{\"type\":\"bound\",\"dimension\":\"__time\","
-        + "\"lower\":\"2015-09-12T08:00:00.000Z\",\"lowerStrict\":false,"
+        + "\"lower\":\"2015-09-12T02:30:00.000Z\",\"lowerStrict\":false,"
+        + "\"ordering\":\"lexicographic\",\"extractionFn\":{\"type\":\"timeFormat\","
+        + "\"format\":\"yyyy-MM-dd";
+    final String druidQueryPart2 = "\"granularity\":{\"type\":\"period\",\"period\":\"PT1H\","
+        + "\"timeZone\":\"Asia/Kolkata\"},\"timeZone\":\"UTC\","
+        + "\"locale\":\"und\"}}";
+    CalciteAssert.that()
+        .enable(enabled())
+        .with(ImmutableMap.of("model", WIKI_AUTO2.getPath()))
+        .with(CalciteConnectionProperty.TIME_ZONE.camelName(), "Asia/Kolkata")
+        .query(sql)
+        .runs()
+        .queryContains(druidChecker(druidQueryPart1, druidQueryPart2))
+        .returnsOrdered("T=2015-09-12 08:00:02; S=null; C=1",
+            "T=2015-09-12 08:00:04; S=null; C=1",
+            "T=2015-09-12 08:00:05; S=null; C=1",
+            "T=2015-09-12 08:00:07; S=null; C=1");
+  }
+
+  @Test public void testTimeWithFilterOnFloorOnTimeWithTimezoneConversionCast() {
+    final String sql = "Select cast(\"__time\" as timestamp) as t, \"countryName\" as s, "
+        + "count(*) as c from \"wikiticker\" where floor(\"__time\" to HOUR)"
+        + " >= '2015-09-12 08:00:00 Asia/Kolkata' group by cast(\"__time\" as timestamp), \"countryName\""
+        + " order by t limit 4";
+    final String druidQueryPart1 = "filter\":{\"type\":\"bound\",\"dimension\":\"__time\","
+        + "\"lower\":\"2015-09-12T02:30:00.000Z\",\"lowerStrict\":false,"
         + "\"ordering\":\"lexicographic\",\"extractionFn\":{\"type\":\"timeFormat\","
         + "\"format\":\"yyyy-MM-dd";
     final String druidQueryPart2 = "\"granularity\":{\"type\":\"period\",\"period\":\"PT1H\","
-        + "\"timeZone\":\"IST\"},\"timeZone\":\"IST\","
+        + "\"timeZone\":\"Asia/Kolkata\"},\"timeZone\":\"UTC\","
         + "\"locale\":\"und\"}}";
 
     CalciteAssert.that()
         .enable(enabled())
         .with(ImmutableMap.of("model", WIKI_AUTO2.getPath()))
-        .with(CalciteConnectionProperty.TIME_ZONE.camelName(), "IST")
+        .with(CalciteConnectionProperty.TIME_ZONE.camelName(), "Asia/Kolkata")
         .query(sql)
         .runs()
         .queryContains(druidChecker(druidQueryPart1, druidQueryPart2))
@@ -3343,7 +3371,7 @@ public class DruidAdapterIT {
         + " limit 1";
     final String druidQuery = "{\"queryType\":\"scan\",\"dataSource\":\"foodmart\",\"intervals\":"
         + "[\"1997-04-30T18:30:00.000Z/1997-05-31T18:30:00.000Z\"],\"virtualColumns\":[{\"type\":"
-        + "\"expression\",\"name\":\"vc\",\"expression\":\"timestamp_floor(\\\"__time\\\"";
+        + "\"expression\",\"name\":\"vc\",\"expression\":\"timestamp_parse";
     CalciteAssert.that()
         .enable(enabled())
         .with(ImmutableMap.of("model", FOODMART.getPath()))
@@ -3351,7 +3379,7 @@ public class DruidAdapterIT {
         .query(sql)
         .runs()
         .queryContains(druidChecker(druidQuery))
-        .returnsOrdered("T=1997-04-30 18:30:00");
+        .returnsOrdered("T=1997-05-01 00:00:00");
   }
 
   @Test
@@ -3592,7 +3620,7 @@ public class DruidAdapterIT {
             + "groups=[{}], aggs=[[COUNT()]])")
         .queryContains(
             druidChecker("{\"type\":\"expression\","
-                + "\"expression\":\"(timestamp_format(timestamp_floor(\\\"__time\\\""))
+                + "\"expression\":\"(timestamp_format(timestamp_floor("))
         .returnsUnordered("EXPR$0=117");
   }
 
@@ -3643,9 +3671,9 @@ public class DruidAdapterIT {
 
   @Test
   public void testExtractHourFilterExpression() {
-    final String sql = "SELECT EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) "
+    final String sql = "SELECT EXTRACT(HOUR from \"timestamp\") "
             + "from \"foodmart\" WHERE EXTRACT(HOUR from \"timestamp\") = 17 "
-            + "group by EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) ";
+            + "group by EXTRACT(HOUR from \"timestamp\") ";
     CalciteAssert.that()
         .enable(enabled())
         .with(ImmutableMap.of("model", FOODMART.getPath()))
@@ -3654,10 +3682,47 @@ public class DruidAdapterIT {
         .runs()
         .returnsOrdered("EXPR$0=17");
 
-    final String sql2 = "SELECT EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) "
+    final String sql2 = "SELECT EXTRACT(HOUR from \"timestamp\") "
             + "from \"foodmart\" WHERE"
-            + " EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) = 19 "
-            + "group by EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) ";
+            + " EXTRACT(HOUR from \"timestamp\") = 19 "
+            + "group by EXTRACT(HOUR from \"timestamp\") ";
+    CalciteAssert.that()
+        .enable(enabled())
+        .with(ImmutableMap.of("model", FOODMART.getPath()))
+        .with(CalciteConnectionProperty.TIME_ZONE.camelName(), "EST")
+        .query(sql2)
+        .runs()
+        .returnsOrdered("EXPR$0=19");
+
+    final String sql3 = "SELECT EXTRACT(HOUR from \"timestamp\") "
+            + "from \"foodmart\" WHERE EXTRACT(HOUR from \"timestamp\") = 0 "
+            + "group by EXTRACT(HOUR from \"timestamp\") ";
+    CalciteAssert.that()
+        .enable(enabled())
+        .with(ImmutableMap.of("model", FOODMART.getPath()))
+        .with(CalciteConnectionProperty.TIME_ZONE.camelName(), "UTC")
+        .query(sql3)
+        .runs()
+        .returnsOrdered("EXPR$0=0");
+  }
+
+  @Test
+  public void testExtractHourFilterExpressionWithCast() {
+    final String sql = "SELECT EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) "
+        + "from \"foodmart\" WHERE EXTRACT(HOUR from \"timestamp\") = 17 "
+        + "group by EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) ";
+    CalciteAssert.that()
+        .enable(enabled())
+        .with(ImmutableMap.of("model", FOODMART.getPath()))
+        .with(CalciteConnectionProperty.TIME_ZONE.camelName(), "America/Los_Angeles")
+        .query(sql)
+        .runs()
+        .returnsOrdered("EXPR$0=17");
+
+    final String sql2 = "SELECT EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) "
+        + "from \"foodmart\" WHERE"
+        + " EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) = 19 "
+        + "group by EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) ";
     CalciteAssert.that()
         .enable(enabled())
         .with(ImmutableMap.of("model", FOODMART.getPath()))
@@ -3667,8 +3732,8 @@ public class DruidAdapterIT {
         .returnsOrdered("EXPR$0=19");
 
     final String sql3 = "SELECT EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) "
-            + "from \"foodmart\" WHERE EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) = 0 "
-            + "group by EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) ";
+        + "from \"foodmart\" WHERE EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) = 0 "
+        + "group by EXTRACT(HOUR from CAST(\"timestamp\" AS TIMESTAMP)) ";
     CalciteAssert.that()
         .enable(enabled())
         .with(ImmutableMap.of("model", FOODMART.getPath()))
@@ -3707,7 +3772,7 @@ public class DruidAdapterIT {
         .queryContains(
             druidChecker("\"filter\":{\"type\":\"expression\",\"expression\":\""
                     + "(timestamp_floor(timestamp_parse(concat(concat(",
-                "== timestamp_floor(\\\"__time\\\""));
+                "== timestamp_floor("));
   }
 
   @Test