Apache Drill
  1. Apache Drill
  2. DRILL-549

Implement functions for date and interval data types

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.4.0
    • Component/s: None
    • Labels:
      None

      Description

      Below is the list of functions to be supported as part of this task:

      Date & Interval Arithmetic Functions:

      date +/- integer

      date + interval
      time + interval
      timestamp + interval
      timestamptz + interval
      date + intervalday
      time + intervalday
      timestamp + intervalday
      timestamptz + intervalday
      date + intervalyear
      time + intervalyear
      timestamp + intervalyear
      timestamptz + intervalyear

      date + time

      date - date
      time - time
      timestamp - timestamp
      timestamptz - timestamptz

      interval +/- interval
      intervalday +/- intervalday
      intervalyear +/- intervalyear

      interval *//(div) integer or float or double
      intervalday *//(div) integer or float or double
      intervalyear *//(div) integer or float or double

      -interval
      -intervalday
      -intervalyear

      Date Utility Functions:

      CURRENT_DATE
      CURRENT_TIME
      CURRENT_TIMESTAMP
      LOCALTIME
      LOCALTIMESTAMP
      now()
      timeofday()
      clock_timestamp()

      // For each of the below functions, the 'text' parameter can be one of the following

      {year, month, day, hour, minute, second}

      date_part(text, date)
      date_part(text, time)
      date_part(text, timestamp)
      date_part(text, timestamptz)
      date_part(text, interval)
      date_part(text, intervalday)
      date_part(text, intervalyear)

      // Extract functions similar to date_part
      extract(field from date)
      extract(field from time)
      extract(field from timestamp)
      extract(field from timestamptz)
      extract(field from interval)
      extract(field from intervalday)
      extract(field from intervalyear)

      Date Formatting Functions:
      // 'text' parameter represents the desired output format
      to_char(date, text)
      to_char(time, text)
      to_char(timestamp, text)
      to_char(timestamptz, text)

      // In the below functions first 'text' param represents the string to be converted to date type
      // Second 'text' param represents the format its in
      to_date(text, text)
      to_time(text, text)
      to_timestamp(text, text)
      to_timestamptz(text, text)

      // Input is long milliseconds from epoch
      to_date(long)
      to_time(long)
      to_timestamp(long)
      to_timestamptz(long)

      1. DRILL-549.patch
        104 kB
        Mehant Baid

        Activity

        Hide
        ASF GitHub Bot added a comment -

        GitHub user mehant opened a pull request:

        https://github.com/apache/incubator-drill/pull/52

        DRILL-549: Implement functions for date and interval data types

        You can merge this pull request into a Git repository by running:

        $ git pull https://github.com/mehant/incubator-drill date_functions

        Alternatively you can review and apply these changes as the patch at:

        https://github.com/apache/incubator-drill/pull/52.patch

        To close this pull request, make a commit to your master/trunk branch
        with (at least) the following in the commit message:

        This closes #52


        commit b5424486dfa101b3481474b073292e492468533a
        Author: Mehant Baid <mehantr@gmail.com>
        Date: 2014-04-18T02:02:25Z

        DRILL-549: Implement functions for date and interval data types

        Jira, DRILL-549 has the complete list of functions implemented.


        Show
        ASF GitHub Bot added a comment - GitHub user mehant opened a pull request: https://github.com/apache/incubator-drill/pull/52 DRILL-549 : Implement functions for date and interval data types You can merge this pull request into a Git repository by running: $ git pull https://github.com/mehant/incubator-drill date_functions Alternatively you can review and apply these changes as the patch at: https://github.com/apache/incubator-drill/pull/52.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #52 commit b5424486dfa101b3481474b073292e492468533a Author: Mehant Baid <mehantr@gmail.com> Date: 2014-04-18T02:02:25Z DRILL-549 : Implement functions for date and interval data types Jira, DRILL-549 has the complete list of functions implemented.
        Hide
        ASF GitHub Bot added a comment -

        Github user jinfengni commented on a diff in the pull request:

        https://github.com/apache/incubator-drill/pull/52#discussion_r12062732

        — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java —
        @@ -201,125 +210,96 @@ public void eval() {
        }
        }

        • @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
          - public static class DateIntervalAdd implements DrillSimpleFunc{
          -
          - @Param DateHolder dateType;
          - @Param IntervalHolder intervalType;
          + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentDate implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          @Output DateHolder out;

          - public void setup(RecordBatch b){}
          -
          - public void eval(){
          -
          - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
          - temp.addMonths(intervalType.months);
          - temp.addDays(intervalType.days);
          - temp.add(intervalType.milliSeconds);
          + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); }
          - }
          -
          - @FunctionTemplate(names = {"date_add", "add"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class IntervalDateAdd implements DrillSimpleFunc{
        • @Param IntervalHolder intervalType;
        • @Param DateHolder dateType;
        • @Output DateHolder out;
          -
        • public void setup(RecordBatch b){}
          -
        • public void eval(){
          -
        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months);
        • temp.addDays(intervalType.days);
        • temp.add(intervalType.milliSeconds);
          -
        • out.value = temp.getMillis();
          + public void eval() { + out.value = queryStartDate; }
        • }
          -
          -
        • @FunctionTemplate(names = {"date_sub", "subtract"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class DateIntervalSub implements DrillSimpleFunc{
          -
        • @Param DateHolder dateType;
        • @Param IntervalHolder intervalType;
        • @Output DateHolder out;
        • public void setup(RecordBatch b){}
          + }
        • public void eval(){
          + @FunctionTemplate(names = {"current_timestamp", "now"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentTimeStamp implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          + @Workspace int timezoneIndex;
          + @Output TimeStampTZHolder out;

        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months * -1);
        • temp.addDays(intervalType.days * -1);
        • temp.add(intervalType.milliSeconds * -1);
          + public void setup(RecordBatch incoming) {
        • out.value = temp.getMillis();
          + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime());
            • End diff –

        If two different drill bits have different time zone, will this line get different value of DateTime?

        Let's say getQueryStartTime() return 10000 milliseconds.

        Bit 1 : UTC-8:00
        BIT2 : UTC.
        Then, Bit1 will have 10000 miliseconds in UTC-8:00, and Bit2 will have 10000 millseconds in UTC.

        Is this the expected result?

        Similiar issue for current_date.

        Show
        ASF GitHub Bot added a comment - Github user jinfengni commented on a diff in the pull request: https://github.com/apache/incubator-drill/pull/52#discussion_r12062732 — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java — @@ -201,125 +210,96 @@ public void eval() { } } @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) - public static class DateIntervalAdd implements DrillSimpleFunc{ - - @Param DateHolder dateType; - @Param IntervalHolder intervalType; + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentDate implements DrillSimpleFunc { + @Workspace long queryStartDate; @Output DateHolder out; - public void setup(RecordBatch b){} - - public void eval(){ - - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); - temp.addMonths(intervalType.months); - temp.addDays(intervalType.days); - temp.add(intervalType.milliSeconds); + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); } - } - - @FunctionTemplate(names = {"date_add", "add"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class IntervalDateAdd implements DrillSimpleFunc{ @Param IntervalHolder intervalType; @Param DateHolder dateType; @Output DateHolder out; - public void setup(RecordBatch b){} - public void eval(){ - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months); temp.addDays(intervalType.days); temp.add(intervalType.milliSeconds); - out.value = temp.getMillis(); + public void eval() { + out.value = queryStartDate; } } - - @FunctionTemplate(names = {"date_sub", "subtract"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class DateIntervalSub implements DrillSimpleFunc{ - @Param DateHolder dateType; @Param IntervalHolder intervalType; @Output DateHolder out; public void setup(RecordBatch b){} + } public void eval(){ + @FunctionTemplate(names = {"current_timestamp", "now"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentTimeStamp implements DrillSimpleFunc { + @Workspace long queryStartDate; + @Workspace int timezoneIndex; + @Output TimeStampTZHolder out; org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months * -1); temp.addDays(intervalType.days * -1); temp.add(intervalType.milliSeconds * -1); + public void setup(RecordBatch incoming) { out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); End diff – If two different drill bits have different time zone, will this line get different value of DateTime? Let's say getQueryStartTime() return 10000 milliseconds. Bit 1 : UTC-8:00 BIT2 : UTC. Then, Bit1 will have 10000 miliseconds in UTC-8:00, and Bit2 will have 10000 millseconds in UTC. Is this the expected result? Similiar issue for current_date.
        Hide
        ASF GitHub Bot added a comment -

        Github user jinfengni commented on a diff in the pull request:

        https://github.com/apache/incubator-drill/pull/52#discussion_r12063108

        — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java —
        @@ -201,125 +210,96 @@ public void eval() {
        }
        }

        • @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
          - public static class DateIntervalAdd implements DrillSimpleFunc{
          -
          - @Param DateHolder dateType;
          - @Param IntervalHolder intervalType;
          + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentDate implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          @Output DateHolder out;

          - public void setup(RecordBatch b){}
          -
          - public void eval(){
          -
          - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
          - temp.addMonths(intervalType.months);
          - temp.addDays(intervalType.days);
          - temp.add(intervalType.milliSeconds);
          + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); }
          - }
          -
          - @FunctionTemplate(names = {"date_add", "add"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class IntervalDateAdd implements DrillSimpleFunc{
        • @Param IntervalHolder intervalType;
        • @Param DateHolder dateType;
        • @Output DateHolder out;
          -
        • public void setup(RecordBatch b){}
          -
        • public void eval(){
          -
        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months);
        • temp.addDays(intervalType.days);
        • temp.add(intervalType.milliSeconds);
          -
        • out.value = temp.getMillis();
          + public void eval() { + out.value = queryStartDate; }
        • }
          -
          -
        • @FunctionTemplate(names = {"date_sub", "subtract"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class DateIntervalSub implements DrillSimpleFunc{
          -
        • @Param DateHolder dateType;
        • @Param IntervalHolder intervalType;
        • @Output DateHolder out;
        • public void setup(RecordBatch b){}
          + }
        • public void eval(){
          + @FunctionTemplate(names = {"current_timestamp", "now"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentTimeStamp implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          + @Workspace int timezoneIndex;
          + @Output TimeStampTZHolder out;

        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months * -1);
        • temp.addDays(intervalType.days * -1);
        • temp.add(intervalType.milliSeconds * -1);
          + public void setup(RecordBatch incoming) {
        • out.value = temp.getMillis();
          + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime());
            • End diff –

        Seems to me getQueryStartTime() should return a TimestampTZ value, which is set up by the root Drill fragment. Then, all other drill bits will get the identical TimestampTZ value, and convert into each individual's timezone. That will ensure different drill bits will get the same value, even though they may have different time zones.

        Show
        ASF GitHub Bot added a comment - Github user jinfengni commented on a diff in the pull request: https://github.com/apache/incubator-drill/pull/52#discussion_r12063108 — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java — @@ -201,125 +210,96 @@ public void eval() { } } @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) - public static class DateIntervalAdd implements DrillSimpleFunc{ - - @Param DateHolder dateType; - @Param IntervalHolder intervalType; + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentDate implements DrillSimpleFunc { + @Workspace long queryStartDate; @Output DateHolder out; - public void setup(RecordBatch b){} - - public void eval(){ - - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); - temp.addMonths(intervalType.months); - temp.addDays(intervalType.days); - temp.add(intervalType.milliSeconds); + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); } - } - - @FunctionTemplate(names = {"date_add", "add"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class IntervalDateAdd implements DrillSimpleFunc{ @Param IntervalHolder intervalType; @Param DateHolder dateType; @Output DateHolder out; - public void setup(RecordBatch b){} - public void eval(){ - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months); temp.addDays(intervalType.days); temp.add(intervalType.milliSeconds); - out.value = temp.getMillis(); + public void eval() { + out.value = queryStartDate; } } - - @FunctionTemplate(names = {"date_sub", "subtract"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class DateIntervalSub implements DrillSimpleFunc{ - @Param DateHolder dateType; @Param IntervalHolder intervalType; @Output DateHolder out; public void setup(RecordBatch b){} + } public void eval(){ + @FunctionTemplate(names = {"current_timestamp", "now"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentTimeStamp implements DrillSimpleFunc { + @Workspace long queryStartDate; + @Workspace int timezoneIndex; + @Output TimeStampTZHolder out; org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months * -1); temp.addDays(intervalType.days * -1); temp.add(intervalType.milliSeconds * -1); + public void setup(RecordBatch incoming) { out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); End diff – Seems to me getQueryStartTime() should return a TimestampTZ value, which is set up by the root Drill fragment. Then, all other drill bits will get the identical TimestampTZ value, and convert into each individual's timezone. That will ensure different drill bits will get the same value, even though they may have different time zones.
        Hide
        ASF GitHub Bot added a comment -

        Github user jinfengni commented on a diff in the pull request:

        https://github.com/apache/incubator-drill/pull/52#discussion_r12065917

        — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java —
        @@ -201,125 +210,96 @@ public void eval() {
        }
        }

        • @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
          - public static class DateIntervalAdd implements DrillSimpleFunc{
          -
          - @Param DateHolder dateType;
          - @Param IntervalHolder intervalType;
          + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentDate implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          @Output DateHolder out;

          - public void setup(RecordBatch b){}
          -
          - public void eval(){
          -
          - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
          - temp.addMonths(intervalType.months);
          - temp.addDays(intervalType.days);
          - temp.add(intervalType.milliSeconds);
          + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); }
          - }
          -
          - @FunctionTemplate(names = {"date_add", "add"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class IntervalDateAdd implements DrillSimpleFunc{
        • @Param IntervalHolder intervalType;
        • @Param DateHolder dateType;
        • @Output DateHolder out;
          -
        • public void setup(RecordBatch b){}
          -
        • public void eval(){
          -
        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months);
        • temp.addDays(intervalType.days);
        • temp.add(intervalType.milliSeconds);
          -
        • out.value = temp.getMillis();
          + public void eval() { + out.value = queryStartDate; }
        • }
          -
          -
        • @FunctionTemplate(names = {"date_sub", "subtract"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
          - public static class DateIntervalSub implements DrillSimpleFunc{
          -
          - @Param DateHolder dateType;
          - @Param IntervalHolder intervalType;
          - @Output DateHolder out;

          - public void setup(RecordBatch b){}
          + }

          - public void eval(){
          + @FunctionTemplate(names = {"current_timestamp", "now"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentTimeStamp implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          + @Workspace int timezoneIndex;
          + @Output TimeStampTZHolder out;

          - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
          - temp.addMonths(intervalType.months * -1);
          - temp.addDays(intervalType.days * -1);
          - temp.add(intervalType.milliSeconds * -1);
          + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = now.getMillis(); + timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); }
          - }
          -
          - @FunctionTemplate(names = {"date_sub", "subtract"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class IntervalDateSub implements DrillSimpleFunc{
        • @Param IntervalHolder intervalType;
        • @Param DateHolder dateType;
        • @Output DateHolder out;
          + public void eval() { + out.value = queryStartDate; + out.index = timezoneIndex; + }
        • public void setup(RecordBatch b){}
          + }
        • public void eval(){
          + @FunctionTemplate(name = "clock_timestamp", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL, isRandom = true)
          + public static class ClockTimeStamp implements DrillSimpleFunc {
          + @Workspace int timezoneIndex;
          + @Output TimeStampTZHolder out;
        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months * -1);
        • temp.addDays(intervalType.days * -1);
        • temp.add(intervalType.milliSeconds * -1);
          + public void setup(RecordBatch incoming) { + org.joda.time.DateTime now = new org.joda.time.DateTime(); + timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); + }
        • out.value = temp.getMillis();
          + public void eval() { + org.joda.time.DateTime now = new org.joda.time.DateTime(); + out.value = now.getMillis(); + out.index = timezoneIndex; }

          }

        • @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
        • public static class CurrentDate implements DrillSimpleFunc {
        • @Workspace long queryStartDate;
        • @Output DateHolder out;
          + @FunctionTemplate(name = "timeofday", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL, isRandom = true)
          + public static class TimeOfDay implements DrillSimpleFunc {
          + @Workspace ByteBuf buffer;
          + @Output VarCharHolder out;

        public void setup(RecordBatch incoming)

        { - - org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); - queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); + buffer = io.netty.buffer.Unpooled.wrappedBuffer(new byte[100]); }

        public void eval()

        { - out.value = queryStartDate; + org.joda.time.DateTime temp = new org.joda.time.DateTime(); + String str = org.apache.drill.exec.expr.fn.impl.DateUtility.formatTimeStampTZ.print(temp); + out.buffer = buffer; + out.start = 0; + out.end = Math.min(100, str.length()); // truncate if target type has length smaller than that of input's string + out.buffer.setBytes(0, str.substring(0,out.end).getBytes()); }

        -
        }

        • @FunctionTemplate(name = "current_timestamptz", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
        • public static class CurrentTimeStampTZ implements DrillSimpleFunc {
          + @FunctionTemplate(name = "localtimestamp", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class LocalTimeStamp implements DrillSimpleFunc {
          @Workspace long queryStartDate;
        • @Workspace int timezoneIndex;
        • @Output TimeStampTZHolder out;
          + @Output TimeStampHolder out;

        public void setup(RecordBatch incoming)

        { - org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + org.joda.time.DateTime now = (new org.joda.time.DateTime(incoming.getContext().getQueryStartTime())).withZoneRetainFields(org.joda.time.DateTimeZone.UTC); queryStartDate = now.getMillis(); - timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); }

        public void eval()

        { out.value = queryStartDate; - out.index = timezoneIndex; }

        -
        }

        • @FunctionTemplate(name = "current_time", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + @FunctionTemplate(names = {"current_time", "localtime"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)

            • End diff –

        Here, current_time and localtime are processed as an identical function. However, in Postgre, they are different:
        "
        CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.
        "

        Show
        ASF GitHub Bot added a comment - Github user jinfengni commented on a diff in the pull request: https://github.com/apache/incubator-drill/pull/52#discussion_r12065917 — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java — @@ -201,125 +210,96 @@ public void eval() { } } @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) - public static class DateIntervalAdd implements DrillSimpleFunc{ - - @Param DateHolder dateType; - @Param IntervalHolder intervalType; + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentDate implements DrillSimpleFunc { + @Workspace long queryStartDate; @Output DateHolder out; - public void setup(RecordBatch b){} - - public void eval(){ - - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); - temp.addMonths(intervalType.months); - temp.addDays(intervalType.days); - temp.add(intervalType.milliSeconds); + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); } - } - - @FunctionTemplate(names = {"date_add", "add"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class IntervalDateAdd implements DrillSimpleFunc{ @Param IntervalHolder intervalType; @Param DateHolder dateType; @Output DateHolder out; - public void setup(RecordBatch b){} - public void eval(){ - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months); temp.addDays(intervalType.days); temp.add(intervalType.milliSeconds); - out.value = temp.getMillis(); + public void eval() { + out.value = queryStartDate; } } - - @FunctionTemplate(names = {"date_sub", "subtract"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) - public static class DateIntervalSub implements DrillSimpleFunc{ - - @Param DateHolder dateType; - @Param IntervalHolder intervalType; - @Output DateHolder out; - public void setup(RecordBatch b){} + } - public void eval(){ + @FunctionTemplate(names = {"current_timestamp", "now"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentTimeStamp implements DrillSimpleFunc { + @Workspace long queryStartDate; + @Workspace int timezoneIndex; + @Output TimeStampTZHolder out; - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); - temp.addMonths(intervalType.months * -1); - temp.addDays(intervalType.days * -1); - temp.add(intervalType.milliSeconds * -1); + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = now.getMillis(); + timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); } - } - - @FunctionTemplate(names = {"date_sub", "subtract"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class IntervalDateSub implements DrillSimpleFunc{ @Param IntervalHolder intervalType; @Param DateHolder dateType; @Output DateHolder out; + public void eval() { + out.value = queryStartDate; + out.index = timezoneIndex; + } public void setup(RecordBatch b){} + } public void eval(){ + @FunctionTemplate(name = "clock_timestamp", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL, isRandom = true) + public static class ClockTimeStamp implements DrillSimpleFunc { + @Workspace int timezoneIndex; + @Output TimeStampTZHolder out; org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months * -1); temp.addDays(intervalType.days * -1); temp.add(intervalType.milliSeconds * -1); + public void setup(RecordBatch incoming) { + org.joda.time.DateTime now = new org.joda.time.DateTime(); + timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); + } out.value = temp.getMillis(); + public void eval() { + org.joda.time.DateTime now = new org.joda.time.DateTime(); + out.value = now.getMillis(); + out.index = timezoneIndex; } } @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) public static class CurrentDate implements DrillSimpleFunc { @Workspace long queryStartDate; @Output DateHolder out; + @FunctionTemplate(name = "timeofday", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL, isRandom = true) + public static class TimeOfDay implements DrillSimpleFunc { + @Workspace ByteBuf buffer; + @Output VarCharHolder out; public void setup(RecordBatch incoming) { - - org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); - queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); + buffer = io.netty.buffer.Unpooled.wrappedBuffer(new byte[100]); } public void eval() { - out.value = queryStartDate; + org.joda.time.DateTime temp = new org.joda.time.DateTime(); + String str = org.apache.drill.exec.expr.fn.impl.DateUtility.formatTimeStampTZ.print(temp); + out.buffer = buffer; + out.start = 0; + out.end = Math.min(100, str.length()); // truncate if target type has length smaller than that of input's string + out.buffer.setBytes(0, str.substring(0,out.end).getBytes()); } - } @FunctionTemplate(name = "current_timestamptz", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) public static class CurrentTimeStampTZ implements DrillSimpleFunc { + @FunctionTemplate(name = "localtimestamp", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class LocalTimeStamp implements DrillSimpleFunc { @Workspace long queryStartDate; @Workspace int timezoneIndex; @Output TimeStampTZHolder out; + @Output TimeStampHolder out; public void setup(RecordBatch incoming) { - org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + org.joda.time.DateTime now = (new org.joda.time.DateTime(incoming.getContext().getQueryStartTime())).withZoneRetainFields(org.joda.time.DateTimeZone.UTC); queryStartDate = now.getMillis(); - timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); } public void eval() { out.value = queryStartDate; - out.index = timezoneIndex; } - } @FunctionTemplate(name = "current_time", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + @FunctionTemplate(names = {"current_time", "localtime"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) End diff – Here, current_time and localtime are processed as an identical function. However, in Postgre, they are different: " CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone. "
        Hide
        Mehant Baid added a comment -

        Updated patch to reflect review comments.
        Rebased on latest master.

        Show
        Mehant Baid added a comment - Updated patch to reflect review comments. Rebased on latest master.
        Hide
        ASF GitHub Bot added a comment -

        Github user mehant commented on a diff in the pull request:

        https://github.com/apache/incubator-drill/pull/52#discussion_r12215926

        — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java —
        @@ -201,125 +210,96 @@ public void eval() {
        }
        }

        • @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
          - public static class DateIntervalAdd implements DrillSimpleFunc{
          -
          - @Param DateHolder dateType;
          - @Param IntervalHolder intervalType;
          + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentDate implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          @Output DateHolder out;

          - public void setup(RecordBatch b){}
          -
          - public void eval(){
          -
          - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
          - temp.addMonths(intervalType.months);
          - temp.addDays(intervalType.days);
          - temp.add(intervalType.milliSeconds);
          + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); }
          - }
          -
          - @FunctionTemplate(names = {"date_add", "add"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class IntervalDateAdd implements DrillSimpleFunc{
        • @Param IntervalHolder intervalType;
        • @Param DateHolder dateType;
        • @Output DateHolder out;
          -
        • public void setup(RecordBatch b){}
          -
        • public void eval(){
          -
        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months);
        • temp.addDays(intervalType.days);
        • temp.add(intervalType.milliSeconds);
          -
        • out.value = temp.getMillis();
          + public void eval() { + out.value = queryStartDate; }
        • }
          -
          -
        • @FunctionTemplate(names = {"date_sub", "subtract"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
          - public static class DateIntervalSub implements DrillSimpleFunc{
          -
          - @Param DateHolder dateType;
          - @Param IntervalHolder intervalType;
          - @Output DateHolder out;

          - public void setup(RecordBatch b){}
          + }

          - public void eval(){
          + @FunctionTemplate(names = {"current_timestamp", "now"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentTimeStamp implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          + @Workspace int timezoneIndex;
          + @Output TimeStampTZHolder out;

          - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
          - temp.addMonths(intervalType.months * -1);
          - temp.addDays(intervalType.days * -1);
          - temp.add(intervalType.milliSeconds * -1);
          + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = now.getMillis(); + timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); }
          - }
          -
          - @FunctionTemplate(names = {"date_sub", "subtract"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class IntervalDateSub implements DrillSimpleFunc{
        • @Param IntervalHolder intervalType;
        • @Param DateHolder dateType;
        • @Output DateHolder out;
          + public void eval() { + out.value = queryStartDate; + out.index = timezoneIndex; + }
        • public void setup(RecordBatch b){}
          + }
        • public void eval(){
          + @FunctionTemplate(name = "clock_timestamp", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL, isRandom = true)
          + public static class ClockTimeStamp implements DrillSimpleFunc {
          + @Workspace int timezoneIndex;
          + @Output TimeStampTZHolder out;
        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months * -1);
        • temp.addDays(intervalType.days * -1);
        • temp.add(intervalType.milliSeconds * -1);
          + public void setup(RecordBatch incoming) { + org.joda.time.DateTime now = new org.joda.time.DateTime(); + timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); + }
        • out.value = temp.getMillis();
          + public void eval() { + org.joda.time.DateTime now = new org.joda.time.DateTime(); + out.value = now.getMillis(); + out.index = timezoneIndex; }

          }

        • @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
        • public static class CurrentDate implements DrillSimpleFunc {
        • @Workspace long queryStartDate;
        • @Output DateHolder out;
          + @FunctionTemplate(name = "timeofday", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL, isRandom = true)
          + public static class TimeOfDay implements DrillSimpleFunc {
          + @Workspace ByteBuf buffer;
          + @Output VarCharHolder out;

        public void setup(RecordBatch incoming)

        { - - org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); - queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); + buffer = io.netty.buffer.Unpooled.wrappedBuffer(new byte[100]); }

        public void eval()

        { - out.value = queryStartDate; + org.joda.time.DateTime temp = new org.joda.time.DateTime(); + String str = org.apache.drill.exec.expr.fn.impl.DateUtility.formatTimeStampTZ.print(temp); + out.buffer = buffer; + out.start = 0; + out.end = Math.min(100, str.length()); // truncate if target type has length smaller than that of input's string + out.buffer.setBytes(0, str.substring(0,out.end).getBytes()); }

        -
        }

        • @FunctionTemplate(name = "current_timestamptz", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
        • public static class CurrentTimeStampTZ implements DrillSimpleFunc {
          + @FunctionTemplate(name = "localtimestamp", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class LocalTimeStamp implements DrillSimpleFunc {
          @Workspace long queryStartDate;
        • @Workspace int timezoneIndex;
        • @Output TimeStampTZHolder out;
          + @Output TimeStampHolder out;

        public void setup(RecordBatch incoming)

        { - org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + org.joda.time.DateTime now = (new org.joda.time.DateTime(incoming.getContext().getQueryStartTime())).withZoneRetainFields(org.joda.time.DateTimeZone.UTC); queryStartDate = now.getMillis(); - timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); }

        public void eval()

        { out.value = queryStartDate; - out.index = timezoneIndex; }

        -
        }

        • @FunctionTemplate(name = "current_time", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + @FunctionTemplate(names = {"current_time", "localtime"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)

            • End diff –

        CURRENT_TIMESTAMP and LOCALTIMESTAMP follow the above convention where the former returns TIMESTAMPTZ and latter returns TIMESTAMP.

        However since we don't support TIME with time zone we cannot make that distinction in the case of CURRENT_TIME vs LOCALTIME

        Show
        ASF GitHub Bot added a comment - Github user mehant commented on a diff in the pull request: https://github.com/apache/incubator-drill/pull/52#discussion_r12215926 — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java — @@ -201,125 +210,96 @@ public void eval() { } } @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) - public static class DateIntervalAdd implements DrillSimpleFunc{ - - @Param DateHolder dateType; - @Param IntervalHolder intervalType; + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentDate implements DrillSimpleFunc { + @Workspace long queryStartDate; @Output DateHolder out; - public void setup(RecordBatch b){} - - public void eval(){ - - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); - temp.addMonths(intervalType.months); - temp.addDays(intervalType.days); - temp.add(intervalType.milliSeconds); + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); } - } - - @FunctionTemplate(names = {"date_add", "add"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class IntervalDateAdd implements DrillSimpleFunc{ @Param IntervalHolder intervalType; @Param DateHolder dateType; @Output DateHolder out; - public void setup(RecordBatch b){} - public void eval(){ - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months); temp.addDays(intervalType.days); temp.add(intervalType.milliSeconds); - out.value = temp.getMillis(); + public void eval() { + out.value = queryStartDate; } } - - @FunctionTemplate(names = {"date_sub", "subtract"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) - public static class DateIntervalSub implements DrillSimpleFunc{ - - @Param DateHolder dateType; - @Param IntervalHolder intervalType; - @Output DateHolder out; - public void setup(RecordBatch b){} + } - public void eval(){ + @FunctionTemplate(names = {"current_timestamp", "now"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentTimeStamp implements DrillSimpleFunc { + @Workspace long queryStartDate; + @Workspace int timezoneIndex; + @Output TimeStampTZHolder out; - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); - temp.addMonths(intervalType.months * -1); - temp.addDays(intervalType.days * -1); - temp.add(intervalType.milliSeconds * -1); + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = now.getMillis(); + timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); } - } - - @FunctionTemplate(names = {"date_sub", "subtract"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class IntervalDateSub implements DrillSimpleFunc{ @Param IntervalHolder intervalType; @Param DateHolder dateType; @Output DateHolder out; + public void eval() { + out.value = queryStartDate; + out.index = timezoneIndex; + } public void setup(RecordBatch b){} + } public void eval(){ + @FunctionTemplate(name = "clock_timestamp", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL, isRandom = true) + public static class ClockTimeStamp implements DrillSimpleFunc { + @Workspace int timezoneIndex; + @Output TimeStampTZHolder out; org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months * -1); temp.addDays(intervalType.days * -1); temp.add(intervalType.milliSeconds * -1); + public void setup(RecordBatch incoming) { + org.joda.time.DateTime now = new org.joda.time.DateTime(); + timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); + } out.value = temp.getMillis(); + public void eval() { + org.joda.time.DateTime now = new org.joda.time.DateTime(); + out.value = now.getMillis(); + out.index = timezoneIndex; } } @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) public static class CurrentDate implements DrillSimpleFunc { @Workspace long queryStartDate; @Output DateHolder out; + @FunctionTemplate(name = "timeofday", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL, isRandom = true) + public static class TimeOfDay implements DrillSimpleFunc { + @Workspace ByteBuf buffer; + @Output VarCharHolder out; public void setup(RecordBatch incoming) { - - org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); - queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); + buffer = io.netty.buffer.Unpooled.wrappedBuffer(new byte[100]); } public void eval() { - out.value = queryStartDate; + org.joda.time.DateTime temp = new org.joda.time.DateTime(); + String str = org.apache.drill.exec.expr.fn.impl.DateUtility.formatTimeStampTZ.print(temp); + out.buffer = buffer; + out.start = 0; + out.end = Math.min(100, str.length()); // truncate if target type has length smaller than that of input's string + out.buffer.setBytes(0, str.substring(0,out.end).getBytes()); } - } @FunctionTemplate(name = "current_timestamptz", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) public static class CurrentTimeStampTZ implements DrillSimpleFunc { + @FunctionTemplate(name = "localtimestamp", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class LocalTimeStamp implements DrillSimpleFunc { @Workspace long queryStartDate; @Workspace int timezoneIndex; @Output TimeStampTZHolder out; + @Output TimeStampHolder out; public void setup(RecordBatch incoming) { - org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + org.joda.time.DateTime now = (new org.joda.time.DateTime(incoming.getContext().getQueryStartTime())).withZoneRetainFields(org.joda.time.DateTimeZone.UTC); queryStartDate = now.getMillis(); - timezoneIndex = org.apache.drill.exec.expr.fn.impl.DateUtility.getIndex(now.getZone().toString()); } public void eval() { out.value = queryStartDate; - out.index = timezoneIndex; } - } @FunctionTemplate(name = "current_time", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + @FunctionTemplate(names = {"current_time", "localtime"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) End diff – CURRENT_TIMESTAMP and LOCALTIMESTAMP follow the above convention where the former returns TIMESTAMPTZ and latter returns TIMESTAMP. However since we don't support TIME with time zone we cannot make that distinction in the case of CURRENT_TIME vs LOCALTIME
        Hide
        ASF GitHub Bot added a comment -

        Github user mehant commented on a diff in the pull request:

        https://github.com/apache/incubator-drill/pull/52#discussion_r12215956

        — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java —
        @@ -201,125 +210,96 @@ public void eval() {
        }
        }

        • @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
          - public static class DateIntervalAdd implements DrillSimpleFunc{
          -
          - @Param DateHolder dateType;
          - @Param IntervalHolder intervalType;
          + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentDate implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          @Output DateHolder out;

          - public void setup(RecordBatch b){}
          -
          - public void eval(){
          -
          - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
          - temp.addMonths(intervalType.months);
          - temp.addDays(intervalType.days);
          - temp.add(intervalType.milliSeconds);
          + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); }
          - }
          -
          - @FunctionTemplate(names = {"date_add", "add"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class IntervalDateAdd implements DrillSimpleFunc{
        • @Param IntervalHolder intervalType;
        • @Param DateHolder dateType;
        • @Output DateHolder out;
          -
        • public void setup(RecordBatch b){}
          -
        • public void eval(){
          -
        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months);
        • temp.addDays(intervalType.days);
        • temp.add(intervalType.milliSeconds);
          -
        • out.value = temp.getMillis();
          + public void eval() { + out.value = queryStartDate; }
        • }
          -
          -
        • @FunctionTemplate(names = {"date_sub", "subtract"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)

        • public static class DateIntervalSub implements DrillSimpleFunc{
          -
        • @Param DateHolder dateType;
        • @Param IntervalHolder intervalType;
        • @Output DateHolder out;
        • public void setup(RecordBatch b){}
          + }
        • public void eval(){
          + @FunctionTemplate(names = {"current_timestamp", "now"}

          , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL)
          + public static class CurrentTimeStamp implements DrillSimpleFunc {
          + @Workspace long queryStartDate;
          + @Workspace int timezoneIndex;
          + @Output TimeStampTZHolder out;

        • org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC);
        • temp.addMonths(intervalType.months * -1);
        • temp.addDays(intervalType.days * -1);
        • temp.add(intervalType.milliSeconds * -1);
          + public void setup(RecordBatch incoming) {
        • out.value = temp.getMillis();
          + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime());
            • End diff –

        Good point, Added root time zone to the fragment context which gets passed around so we return consistent results across Drill bits if they happen to be in different time zones.

        Show
        ASF GitHub Bot added a comment - Github user mehant commented on a diff in the pull request: https://github.com/apache/incubator-drill/pull/52#discussion_r12215956 — Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/DateTypeFunctions.java — @@ -201,125 +210,96 @@ public void eval() { } } @FunctionTemplate(names = {"date_add", "add"}, scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) - public static class DateIntervalAdd implements DrillSimpleFunc{ - - @Param DateHolder dateType; - @Param IntervalHolder intervalType; + @FunctionTemplate(name = "current_date", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentDate implements DrillSimpleFunc { + @Workspace long queryStartDate; @Output DateHolder out; - public void setup(RecordBatch b){} - - public void eval(){ - - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); - temp.addMonths(intervalType.months); - temp.addDays(intervalType.days); - temp.add(intervalType.milliSeconds); + public void setup(RecordBatch incoming) { - out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); + queryStartDate = (new org.joda.time.DateMidnight(now.getYear(), now.getMonthOfYear(), now.getDayOfMonth(), org.joda.time.DateTimeZone.UTC)).getMillis(); } - } - - @FunctionTemplate(names = {"date_add", "add"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class IntervalDateAdd implements DrillSimpleFunc{ @Param IntervalHolder intervalType; @Param DateHolder dateType; @Output DateHolder out; - public void setup(RecordBatch b){} - public void eval(){ - org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months); temp.addDays(intervalType.days); temp.add(intervalType.milliSeconds); - out.value = temp.getMillis(); + public void eval() { + out.value = queryStartDate; } } - - @FunctionTemplate(names = {"date_sub", "subtract"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL) public static class DateIntervalSub implements DrillSimpleFunc{ - @Param DateHolder dateType; @Param IntervalHolder intervalType; @Output DateHolder out; public void setup(RecordBatch b){} + } public void eval(){ + @FunctionTemplate(names = {"current_timestamp", "now"} , scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + public static class CurrentTimeStamp implements DrillSimpleFunc { + @Workspace long queryStartDate; + @Workspace int timezoneIndex; + @Output TimeStampTZHolder out; org.joda.time.MutableDateTime temp = new org.joda.time.MutableDateTime(dateType.value, org.joda.time.DateTimeZone.UTC); temp.addMonths(intervalType.months * -1); temp.addDays(intervalType.days * -1); temp.add(intervalType.milliSeconds * -1); + public void setup(RecordBatch incoming) { out.value = temp.getMillis(); + org.joda.time.DateTime now = new org.joda.time.DateTime(incoming.getContext().getQueryStartTime()); End diff – Good point, Added root time zone to the fragment context which gets passed around so we return consistent results across Drill bits if they happen to be in different time zones.
        Hide
        Jacques Nadeau added a comment -

        merged in 22eab58

        Show
        Jacques Nadeau added a comment - merged in 22eab58
        Hide
        ASF GitHub Bot added a comment -

        Github user mehant commented on the pull request:

        https://github.com/apache/incubator-drill/pull/52#issuecomment-42269992

        merged as 22eab58756ffe2e99aaeffd8535fe7be1e187d27

        Show
        ASF GitHub Bot added a comment - Github user mehant commented on the pull request: https://github.com/apache/incubator-drill/pull/52#issuecomment-42269992 merged as 22eab58756ffe2e99aaeffd8535fe7be1e187d27
        Hide
        ASF GitHub Bot added a comment -

        Github user mehant closed the pull request at:

        https://github.com/apache/incubator-drill/pull/52

        Show
        ASF GitHub Bot added a comment - Github user mehant closed the pull request at: https://github.com/apache/incubator-drill/pull/52

          People

          • Assignee:
            Mehant Baid
            Reporter:
            Mehant Baid
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development